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

Nyall Dawson nyall.dawson at gmail.com
Thu Dec 6 02:16:58 PST 2018


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
>


More information about the QGIS-Developer mailing list