[QGIS-Developer] Whatever is wrong with QGIS 3.x SQL server driver

Thu Dec 6 23:19:27 PST 2018

Thanks Nyall -

"I'll be back !" - in about a week or so

Den fre. 7. dec. 2018 kl. 00.09 skrev Nyall Dawson <nyall.dawson at gmail.com>:

> On Fri, 7 Dec 2018 at 00:13, Bo Victor Thomsen
> <bo.victor.thomsen at gmail.com> 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 <
> nyall.dawson at gmail.com>:
> >>
> >> On Thu, 6 Dec 2018 at 20:34, Bo Victor Thomsen
> >> <bo.victor.thomsen at gmail.com> 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 <
> nyall.dawson at gmail.com>:
> >> >>
> >> >> On Thu, 6 Dec 2018 at 20:05, Bo Victor Thomsen
> >> >> <bo.victor.thomsen at gmail.com> 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 <
> nyall.dawson at gmail.com>:
> >> >> >>
> >> >> >> On Thu, 6 Dec 2018 at 19:05, Bo Victor Thomsen
> >> >> >> <bo.victor.thomsen at gmail.com> 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
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/qgis-developer/attachments/20181207/19b6d813/attachment-0001.html>