<div dir="ltr">Thanks Nyall -<div><br></div><div>"I'll be back !" - in about a week or so </div></div><br><div class="gmail_quote"><div dir="ltr">Den fre. 7. dec. 2018 kl. 00.09 skrev Nyall Dawson <<a href="mailto:nyall.dawson@gmail.com">nyall.dawson@gmail.com</a>>:<br></div><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">On Fri, 7 Dec 2018 at 00:13, Bo Victor Thomsen<br>
<<a href="mailto:bo.victor.thomsen@gmail.com" target="_blank">bo.victor.thomsen@gmail.com</a>> wrote:<br>
<br>
> 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<br>
><br>
> QGIS 2 with native driver and QGIS 3 with ogr driver uses appr. the same time showing the map.<br>
> QGIS 3 with native driver uses appr. twice the time to show the results on the map.<br>
<br>
Ok, thanks. In that case it appears as though this regression was<br>
probably caused by the move to Qt5 or somewhere in the QGIS 3.0<br>
refactoring (as opposed to a deliberate change)<br>
<br>
Next things to try:<br>
<br>
1. Download debugview<br>
(<a href="https://docs.microsoft.com/en-us/sysinternals/downloads/debugview" rel="noreferrer" target="_blank">https://docs.microsoft.com/en-us/sysinternals/downloads/debugview</a>),<br>
start it, and then open QGIS. Test your layer and see if there's any<br>
relevant console output which may explain things<br>
<br>
2. Run some little PyQGIS scripts to help narrow down where the issue<br>
is. I.e. run these scripts on a large layer with the different<br>
versions and time their execution:<br>
<br>
# fetch attributes only, no geometry<br>
req = QgsFeatureRequest().setFlags(QgsFeatureRequest.NoGeometry)<br>
for f in iface.activeLayer().getFeatures(req):<br>
pass<br>
<br>
# fetch geometry only, no attributes<br>
req = QgsFeatureRequest().setSubsetOfAttributes([])<br>
for f in iface.activeLayer().getFeatures(req):<br>
pass<br>
<br>
This will help determine whether the regression is in the geometry<br>
parsing, attribute parsing, or somewhere else...<br>
<br>
Nyall<br>
<br>
<br>
<br>
<br>
><br>
> Den tor. 6. dec. 2018 kl. 11.54 skrev Nyall Dawson <<a href="mailto:nyall.dawson@gmail.com" target="_blank">nyall.dawson@gmail.com</a>>:<br>
>><br>
>> On Thu, 6 Dec 2018 at 20:34, Bo Victor Thomsen<br>
>> <<a href="mailto:bo.victor.thomsen@gmail.com" target="_blank">bo.victor.thomsen@gmail.com</a>> wrote:<br>
>> ><br>
>> > Hi Nyall -<br>
>> ><br>
>> > I'm running QGIS 3.4.2 on Windows ver.10.<br>
>> ><br>
>> ><br>
>> ><br>
>> > 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)<br>
>><br>
>> No, nothing that would explain this. Just minor bug fixing and the port to Qt 5.<br>
>><br>
>> I wonder if you could log the queries coming from QGIS and see if you<br>
>> can identify any changes from 2.18?<br>
>><br>
>> Nyall<br>
>><br>
>><br>
>><br>
>><br>
>> ><br>
>> > 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%<br>
>> ><br>
>> ><br>
>> ><br>
>> > I would happily ditch MS SQLServer forever for spatial work and mainly use Postgres. However, my customers have a different opinion :-(<br>
>> ><br>
>> > Den tor. 6. dec. 2018 kl. 11.17 skrev Nyall Dawson <<a href="mailto:nyall.dawson@gmail.com" target="_blank">nyall.dawson@gmail.com</a>>:<br>
>> >><br>
>> >> On Thu, 6 Dec 2018 at 20:05, Bo Victor Thomsen<br>
>> >> <<a href="mailto:bo.victor.thomsen@gmail.com" target="_blank">bo.victor.thomsen@gmail.com</a>> wrote:<br>
>> >><br>
>> >> > 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?<br>
>> >> ><br>
>> >><br>
>> >> It's on read. Writing always uses a make valid call for SQL Server to<br>
>> >> try to avoid triggering the issue.<br>
>> >><br>
>> >> > And the validity check doesn't explain the obvious time difference between the OGR driver and the native QGIS driver for SQL Server<br>
>> >><br>
>> >> Well, it would if OGR wasn't doing this check by default. What<br>
>> >> platform are you connecting from? Windows or Linux?<br>
>> >><br>
>> >> > 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 :-)<br>
>> >><br>
>> >> There's also these points: <a href="https://www.pg-versus-ms.com/" rel="noreferrer" target="_blank">https://www.pg-versus-ms.com/</a> (I think I<br>
>> >> could write as much again on the spatial side of things alone.) If you<br>
>> >> have a choice, Postgres is far superior in so many ways.<br>
>> >><br>
>> >> Nyall<br>
>> >><br>
>> >> ><br>
>> >> ><br>
>> >> ><br>
>> >> ><br>
>> >> > Den tor. 6. dec. 2018 kl. 10.17 skrev Nyall Dawson <<a href="mailto:nyall.dawson@gmail.com" target="_blank">nyall.dawson@gmail.com</a>>:<br>
>> >> >><br>
>> >> >> On Thu, 6 Dec 2018 at 19:05, Bo Victor Thomsen<br>
>> >> >> <<a href="mailto:bo.victor.thomsen@gmail.com" target="_blank">bo.victor.thomsen@gmail.com</a>> wrote:<br>
>> >> >> ><br>
>> >> >> > Hi list -<br>
>> >> >> ><br>
>> >> >> ><br>
>> >> >> ><br>
>> >> >> > 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.<br>
>> >> >> ><br>
>> >> >> ><br>
>> >> >> ><br>
>> >> >> > And then connected to both tables in QGIS. The SQL server is 3 times slower in retrieving the dataset than Postgres in QGIS!<br>
>> >> >> ><br>
>> >> >><br>
>> >> >> It's probably the extra validity checks which were added. SQL Server<br>
>> >> >> itself is broken by design when it comes to spatial data handling and<br>
>> >> >> if it encounters an invalid geometry it will silently abort the<br>
>> >> >> request and you'll be missing features from the layer. But there's *no<br>
>> >> >> way* for QGIS to detect when this occurs! Accordingly QGIS takes the<br>
>> >> >> "safer is better" approach and forces a validity check and make valid<br>
>> >> >> step as part of the queries sent to SQL Server. This avoids the<br>
>> >> >> potentially missing features, but comes at a large cost.<br>
>> >> >><br>
>> >> >> If you're 100% sure that your tables have no invalid geometries (and<br>
>> >> >> never will have any!), you *can* switch this check off. But be<br>
>> >> >> warned... if you ever introduce invalid geometries into your tables,<br>
>> >> >> you'll get data loss. The setting is under the SQL Server connection's<br>
>> >> >> properties -- "skip invalid geometry handling".<br>
>> >> >><br>
>> >> >> Let me know if this helps at all<br>
>> >> >><br>
>> >> >> Nyall<br>
>> >> ><br>
>> >> ><br>
>> >> ><br>
>> >> > --<br>
>> >> > Med venlig hilsen<br>
>> >> ><br>
>> >> > Bo Victor Thomsen<br>
>> >> ><br>
>> ><br>
>> ><br>
>> ><br>
>> > --<br>
>> > Med venlig hilsen<br>
>> ><br>
>> > Bo Victor Thomsen<br>
>> ><br>
><br>
><br>
><br>
> --<br>
> Med venlig hilsen<br>
><br>
> Bo Victor Thomsen<br>
><br>
</blockquote></div><br clear="all"><div><br></div>-- <br><div dir="ltr" class="gmail_signature" data-smartmail="gmail_signature">Med venlig hilsen<div><br></div><div>Bo Victor Thomsen</div><div><br></div></div>