[Qgis-user] Performance of MSSQL Server with QGIS 3.4

[Nyall Dawson]

On Tue, 30 Oct 2018 at 05:18, Baker, Matthew <MATTHEW_BAKER at dpsk12.org> wrote:

> Everything was working fine even with version 3.2, and additionally, I tested with version 2.18 (which used to work fine), and the same issue is happening.

Just to clarify -- you mean that 2.18 shows the same performance issue?

My suspicion here is that you're running into SQL Server's (very
annoying) invalid geometry handling. We've been fighting with this for
a couple of releases, and it breaks down to two choices:

1. Don't have any code in place on QGIS' side to overcome invalid
geometries on SQL server databases. Benefit: fastest performance.
Downside: if ANY features in your table have invalid geometries, SQL
Server silently aborts the request and returns a truncated table. You
may be missing features and never even know about them.

2. Handle invalid geometries by repairing all invalid geometries when
fetching from SQL server. Benefit: no issues with randomly truncated
tables. Downside: much slower retrieval of features due to all the
extra processing (done on the SQL server itself)

Since later 2.18 releases and QGIS 3.4 we play it safe and take
approach 2 by default. Because it's better to have a slower provider
instead of silent data loss. BUT if you're 100% confident that your
database has no invalid geometries, and never will have them, then you
can take off the safeties and run at full performance by changing a
setting in your SQL Server connection. Look for the "Skip invalid
geometry handling" checkbox under the connection properties and turn
it on. But you've been warned, turning this setting on pushes all
responsibility back TO YOU to ensure that your database is safe.
You'll get 0 warnings if it isn't, and you'll have randomly missing
features from your layers.

I wish there was another approach here, but as of current SQL Server
versions there isn't*. Frankly, it's just a very poor decision made by
SQL Server's engineers which makes SQL Server an inferior choice for
an enterprise spatial database.

Nyall

* if there is something we've missed -- please let us know!

>
>
>
> This issue doesn’t seem to lie in the server itself, as our DB GUI’s (MSSQL Server Management Studio, DBeaver, DataGrip) all seem to be behaving normally – tables displaying with no issue, queries performing fine, etc.
>
>
>
> Any thoughts appreciated!
>
>
>
> Thank you!
>
>
>
> -Matthew Baker
>
> Denver Public Schools
>
> Denver, CO, USA
>
>
>
> _______________________________________________
> Qgis-user mailing list
> Qgis-user at lists.osgeo.org
> List info: https://lists.osgeo.org/mailman/listinfo/qgis-user
> Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user