Thanks Nyall - "I'll be back !" - in about a week or so
Den fre. 7. dec. 2018 kl. 00.09 skrev Nyall Dawson <[email protected]>: > 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 > > > -- 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
