On Thu, 6 Dec 2018 at 20:34, Bo Victor Thomsen <[email protected]> wrote: > > Hi Nyall - > > I'm running QGIS 3.4.2 on Windows ver.10. > > > > Have there been any other changes to the SQLServer driver besides the > validity check? (I remember vaguely something about the internal > representation of spatial objects in the driver)
No, nothing that would explain this. Just minor bug fixing and the port to Qt 5. I wonder if you could log the queries coming from QGIS and see if you can identify any changes from 2.18? Nyall > > I'm asking, because I've done this type of testing QGIS 2.x before where the > time difference between Postgres and SQL Server was relatively small when > doing simple MBR based searches - somewhere in the vicinity of 20% > > > > I would happily ditch MS SQLServer forever for spatial work and mainly use > Postgres. However, my customers have a different opinion :-( > > Den tor. 6. dec. 2018 kl. 11.17 skrev Nyall Dawson <[email protected]>: >> >> On Thu, 6 Dec 2018 at 20:05, Bo Victor Thomsen >> <[email protected]> wrote: >> >> > I've tried switching the validity check off as described. As far as I can >> > measure, there is no time difference with or without the validity check. >> > When does the validity check kick in? Writing or reading the features? Or >> > both? >> > >> >> It's on read. Writing always uses a make valid call for SQL Server to >> try to avoid triggering the issue. >> >> > And the validity check doesn't explain the obvious time difference between >> > the OGR driver and the native QGIS driver for SQL Server >> >> Well, it would if OGR wasn't doing this check by default. What >> platform are you connecting from? Windows or Linux? >> >> > However, I will use your explanation about SQL Server's behavior regarding >> > invalid geometries as an argument for my customers to switch to Postgres >> > instead of using SQLServer :-) >> >> There's also these points: https://www.pg-versus-ms.com/ (I think I >> could write as much again on the spatial side of things alone.) If you >> have a choice, Postgres is far superior in so many ways. >> >> Nyall >> >> > >> > >> > >> > >> > Den tor. 6. dec. 2018 kl. 10.17 skrev Nyall Dawson >> > <[email protected]>: >> >> >> >> On Thu, 6 Dec 2018 at 19:05, Bo Victor Thomsen >> >> <[email protected]> wrote: >> >> > >> >> > Hi list - >> >> > >> >> > >> >> > >> >> > I've done some experiments with a dataset consisting of 440000 rows and >> >> > uploaded this to two database servers: Postgres and SQLServer. Both >> >> > tables has indexes on Primary key and the spatial column. >> >> > >> >> > >> >> > >> >> > And then connected to both tables in QGIS. The SQL server is 3 times >> >> > slower in retrieving the dataset than Postgres in QGIS! >> >> > >> >> >> >> It's probably the extra validity checks which were added. SQL Server >> >> itself is broken by design when it comes to spatial data handling and >> >> if it encounters an invalid geometry it will silently abort the >> >> request and you'll be missing features from the layer. But there's *no >> >> way* for QGIS to detect when this occurs! Accordingly QGIS takes the >> >> "safer is better" approach and forces a validity check and make valid >> >> step as part of the queries sent to SQL Server. This avoids the >> >> potentially missing features, but comes at a large cost. >> >> >> >> If you're 100% sure that your tables have no invalid geometries (and >> >> never will have any!), you *can* switch this check off. But be >> >> warned... if you ever introduce invalid geometries into your tables, >> >> you'll get data loss. The setting is under the SQL Server connection's >> >> properties -- "skip invalid geometry handling". >> >> >> >> Let me know if this helps at all >> >> >> >> Nyall >> > >> > >> > >> > -- >> > Med venlig hilsen >> > >> > Bo Victor Thomsen >> > > > > > -- > Med venlig hilsen > > Bo Victor Thomsen > _______________________________________________ QGIS-Developer mailing list [email protected] List info: https://lists.osgeo.org/mailman/listinfo/qgis-developer Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-developer
