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

[Bo Victor Thomsen]

Ok, I'll take a look and monitor the queries from QGIS 2 and 3 and report
back. It will take a couple of days

And thanks 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
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/qgis-developer/attachments/20181206/a6d7f677/attachment.html>