On Fri, 7 Dec 2018 at 00:13, Bo Victor Thomsen <[email protected]> wrote:
> There is not much difference in the SQL Server execution times for the 3 > queries (around 200 - 300 ms) , so that can't explain the time difference in > QGIS > > QGIS 2 with native driver and QGIS 3 with ogr driver uses appr. the same time > showing the map. > QGIS 3 with native driver uses appr. twice the time to show the results on > the map. Ok, thanks. In that case it appears as though this regression was probably caused by the move to Qt5 or somewhere in the QGIS 3.0 refactoring (as opposed to a deliberate change) Next things to try: 1. Download debugview (https://docs.microsoft.com/en-us/sysinternals/downloads/debugview), start it, and then open QGIS. Test your layer and see if there's any relevant console output which may explain things 2. Run some little PyQGIS scripts to help narrow down where the issue is. I.e. run these scripts on a large layer with the different versions and time their execution: # fetch attributes only, no geometry req = QgsFeatureRequest().setFlags(QgsFeatureRequest.NoGeometry) for f in iface.activeLayer().getFeatures(req): pass # fetch geometry only, no attributes req = QgsFeatureRequest().setSubsetOfAttributes([]) for f in iface.activeLayer().getFeatures(req): pass This will help determine whether the regression is in the geometry parsing, attribute parsing, or somewhere else... Nyall > > Den tor. 6. dec. 2018 kl. 11.54 skrev Nyall Dawson <[email protected]>: >> >> 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 >> > > > > > -- > 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
