Re: [Qgis-user] Performance of MSSQL Server with QGIS 3.4
On Thu, 1 Nov 2018 at 01:55, Baker, Matthew wrote: > > Thanks again, Nyall. > > Re. 'only look in geometry columns' - no, we're not using this setting, nor > have we ever. Is that true that pre-3.4 was using that setting? I would have > imagined this is the other way around? I will see if this fix addresses this > issue when it gets implemented. Pre 3.4 browser was ALWAYS using that logic, regardless of the connection's setting. So it would have been nice and fast, but missing tables which weren't present in the geometry_columns tables. (Note that QGIS automatically creates this table and populates it automatically during certain layer creation operations) Nyall > > Re. the schema display for dragging and dropping - still not a fan of this > feature - would prefer the QGIS browser window not be a DBA-type of GUI > (https://issues.qgis.org/issues/17719) but that's another issue altogether! > > Thanks again! > > -m > > > > > > -Original Message- > From: Nyall Dawson > Sent: Tuesday, October 30, 2018 6:48 PM > To: Baker, Matthew > Cc: Andreas Neumann ; qgis-user > Subject: Re: [Qgis-user] Performance of MSSQL Server with QGIS 3.4 > > On Wed, 31 Oct 2018 at 01:28, Baker, Matthew wrote: > > > > 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. > > Thanks for the clarification. Can you confirm whether you have the setting > "only look in the geometry_columns metadata table" switched on or off for > your connection? > > If you're not using this setting, then it's possibly related to this fix > https://github.com/qgis/QGIS/commit/e813fe880. Pre 3.4 QGIS was only looking > in the geometry columns table for the browser, and not respecting this > setting. > > > 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! > > That's intended -- for db connections we should be showing empty schemas in > the browser, as this allows users to drag and drop tables into these schemas > to initially populate them. > > Nyall ___ Qgis-user mailing list Qgis-user@lists.osgeo.org List info: https://lists.osgeo.org/mailman/listinfo/qgis-user Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user
Re: [Qgis-user] Performance of MSSQL Server with QGIS 3.4
Thanks again, Nyall. Re. 'only look in geometry columns' - no, we're not using this setting, nor have we ever. Is that true that pre-3.4 was using that setting? I would have imagined this is the other way around? I will see if this fix addresses this issue when it gets implemented. Re. the schema display for dragging and dropping - still not a fan of this feature - would prefer the QGIS browser window not be a DBA-type of GUI (https://issues.qgis.org/issues/17719) but that's another issue altogether! Thanks again! -m -Original Message- From: Nyall Dawson Sent: Tuesday, October 30, 2018 6:48 PM To: Baker, Matthew Cc: Andreas Neumann ; qgis-user Subject: Re: [Qgis-user] Performance of MSSQL Server with QGIS 3.4 On Wed, 31 Oct 2018 at 01:28, Baker, Matthew wrote: > > 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. Thanks for the clarification. Can you confirm whether you have the setting "only look in the geometry_columns metadata table" switched on or off for your connection? If you're not using this setting, then it's possibly related to this fix https://github.com/qgis/QGIS/commit/e813fe880. Pre 3.4 QGIS was only looking in the geometry columns table for the browser, and not respecting this setting. > 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! That's intended -- for db connections we should be showing empty schemas in the browser, as this allows users to drag and drop tables into these schemas to initially populate them. Nyall ___ Qgis-user mailing list Qgis-user@lists.osgeo.org List info: https://lists.osgeo.org/mailman/listinfo/qgis-user Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user
Re: [Qgis-user] Performance of MSSQL Server with QGIS 3.4
On Wed, 31 Oct 2018 at 01:28, Baker, Matthew wrote: > > 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. Thanks for the clarification. Can you confirm whether you have the setting "only look in the geometry_columns metadata table" switched on or off for your connection? If you're not using this setting, then it's possibly related to this fix https://github.com/qgis/QGIS/commit/e813fe880. Pre 3.4 QGIS was only looking in the geometry columns table for the browser, and not respecting this setting. > 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! That's intended -- for db connections we should be showing empty schemas in the browser, as this allows users to drag and drop tables into these schemas to initially populate them. Nyall ___ Qgis-user mailing list Qgis-user@lists.osgeo.org List info: https://lists.osgeo.org/mailman/listinfo/qgis-user Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user
Re: [Qgis-user] Performance of MSSQL Server with QGIS 3.4
Hi All, Just confirming I am seeing the same issue, I can see all schemas, more than I could with 3.2 and 2.18. Kind Regards Chris From: Qgis-user On Behalf Of Baker, Matthew Sent: Tuesday, 30 October 2018 3:29 PM To: Nyall Dawson ; Andreas Neumann Cc: qgis-user Subject: Re: [Qgis-user] Performance of MSSQL Server with QGIS 3.4 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 mailto:nyall.daw...@gmail.com>> Sent: Monday, October 29, 2018 5:04 PM To: Baker, Matthew mailto:matthew_ba...@dpsk12.org>>; Andreas Neumann mailto:andr...@qgis.org>> Cc: qgis-user mailto:qgis-user@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 mailto:matthew_ba...@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@lists.osgeo.org<mailto:Qgis-user@lists.osgeo.org> > List info: > https://lists.osgeo.org/mailman/listinfo/qgis-user<https://lists.osgeo.org/mailman/listinfo/qgis-user> > Unsubscribe: > https://lists.osgeo.org/mailman/listinfo/qgis-user<https://lists.osgeo.org/mailman/listinfo/qgis-user> ___ Qgis-user mailing list Qgis-user@lists.osgeo.org<mailto:Qgis-user@lists.osgeo.org> List info: https://lists.osgeo.org/mailman/listinfo/qgis-user<https://lists.osgeo.org/mailman/listinfo/qgis-user> Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user<https://lists.osgeo.org/mailman/listinfo/qgis-user> ___ Qgis-user mailing list Qgis-user@lists.osgeo.org List info: https://lists.osgeo.org/mailman/listinfo/qgis-user Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user
Re: [Qgis-user] Performance of MSSQL Server with QGIS 3.4
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 Sent: Monday, October 29, 2018 5:04 PM To: Baker, Matthew ; Andreas Neumann Cc: qgis-user Subject: Re: [Qgis-user] Performance of MSSQL Server with QGIS 3.4 On Tue, 30 Oct 2018 at 05:18, Baker, Matthew 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@lists.osgeo.org > List info: https://lists.osgeo.org/mailman/listinfo/qgis-user > Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user ___ Qgis-user mailing list Qgis-user@lists.osgeo.org List info: https://lists.osgeo.org/mailman/listinfo/qgis-user Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user
Re: [Qgis-user] Performance of MSSQL Server with QGIS 3.4
On Tue, 30 Oct 2018 at 05:18, Baker, Matthew 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@lists.osgeo.org > List info: https://lists.osgeo.org/mailman/listinfo/qgis-user > Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user ___ Qgis-user mailing list Qgis-user@lists.osgeo.org List info: https://lists.osgeo.org/mailman/listinfo/qgis-user Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user
[Qgis-user] Performance of MSSQL Server with QGIS 3.4
Hi all, For some reason, our MSSQL Server experience has changed drastically with QGIS 3.4. The issue resides in the display / refreshing of tables in the database. For some reason, the refresh hangs for many minutes (5-10), and freezes up QGIS. Previous to our upgrade, we have had great success using MSSQL Server, and no connection issues at all. Are there any changes to 3.4 and MSSQL that automatically implements a geometry_columns table? We haven't used one in the past, with no negative effect. Is there a process new to QGIS 3.4 that gets kicks off in the database that might be spinning causing QGIS to hang? 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. 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@lists.osgeo.org List info: https://lists.osgeo.org/mailman/listinfo/qgis-user Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user
