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

[Baker, Matthew]

Thanks for the reply, Nyall.

To clarify - this issue is just refreshing the database tree in the browser window - once refreshed (eventually), all tables behave normally (see below re. invalid geometries). 

2.18 and 3.2 used to refresh the tables just fine. Upon installing 3.4, things have slowed down for all versions.

Another thing to note - 2.18 and 3.2 show only a handful of schemas in the database (not sure how it decides which one) - but now 3.4 is showing ALL schemas, including those without any tables and that weren't shown in 2.18/3.2 - that's weird!

Re. skipping invalid geometries - this setting doesn't seem to have any effect on the behavior we're experiencing. (our data is in pretty good shape and we haven't had issues with invalid geometries prior to 3.4 when the option to 'skip' was not available.) 

Thanks again!

-m



-----Original Message-----
From: Nyall Dawson <nyall.dawson at gmail.com> 
Sent: Monday, October 29, 2018 5:04 PM
To: Baker, Matthew <MATTHEW_BAKER at dpsk12.org>; Andreas Neumann <andreas at qgis.org>
Cc: qgis-user <qgis-user at lists.osgeo.org>
Subject: Re: [Qgis-user] Performance of MSSQL Server with QGIS 3.4

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