<div dir="ltr"><div dir="ltr" class="gmail_signature" data-smartmail="gmail_signature"><p class="MsoNormal" style="margin:0cm 0cm 0.0001pt;line-height:normal;font-size:11pt;font-family:Calibri,sans-serif"><span lang="EN-US" style="font-size:12pt;font-family:"Times New Roman",serif">Hi list -</span></p>

<p class="MsoNormal" style="margin:0cm 0cm 0.0001pt;line-height:normal;font-size:11pt;font-family:Calibri,sans-serif"><span lang="EN-US" style="font-size:12pt;font-family:"Times New Roman",serif"> </span></p>

<p class="MsoNormal" style="margin:0cm 0cm 0.0001pt;line-height:normal;font-size:11pt;font-family:Calibri,sans-serif"><span lang="EN-US" style="font-size:12pt;font-family:"Times New Roman",serif">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.</span></p>

<p class="MsoNormal" style="margin:0cm 0cm 0.0001pt;line-height:normal;font-size:11pt;font-family:Calibri,sans-serif"><span lang="EN-US" style="font-size:12pt;font-family:"Times New Roman",serif"> </span></p>

<p class="MsoNormal" style="margin:0cm 0cm 0.0001pt;line-height:normal;font-size:11pt;font-family:Calibri,sans-serif"><span lang="EN-US" style="font-size:12pt;font-family:"Times New Roman",serif">And then connected to both tables in QGIS. The SQL server is <b>3 times</b> slower in retrieving the
dataset than Postgres in QGIS!</span></p>

<p class="MsoNormal" style="margin:0cm 0cm 0.0001pt;line-height:normal;font-size:11pt;font-family:Calibri,sans-serif"><span lang="EN-US" style="font-size:12pt;font-family:"Times New Roman",serif"> </span></p>

<p class="MsoNormal" style="margin:0cm 0cm 0.0001pt;line-height:normal;font-size:11pt;font-family:Calibri,sans-serif"><span lang="EN-US" style="font-size:12pt;font-family:"Times New Roman",serif">And it's not SQL server's fault!! I've made the same experiment using GeoServer
as a client where Postgres is only slightly faster.    </span></p>

<p class="MsoNormal" style="margin:0cm 0cm 0.0001pt;line-height:normal;font-size:11pt;font-family:Calibri,sans-serif"><span lang="EN-US" style="font-size:12pt;font-family:"Times New Roman",serif"> </span></p>

<p class="MsoNormal" style="margin:0cm 0cm 0.0001pt;line-height:normal;font-size:11pt;font-family:Calibri,sans-serif"><span lang="EN-US" style="font-size:12pt;font-family:"Times New Roman",serif">To make matters worse... In QGIS you can connect to the SQL server database using
the </span><span style="font-family:"Times New Roman",serif;font-size:12pt">"Layer" > "Add Layer" > "Add Vector layer"
- i.e. using the OGR driver - to connect to the SQL Server table. This makes
the retrieval of SQL Server data nearly as fast as Postgres.</span></p>

<p class="MsoNormal" style="margin:0cm 0cm 0.0001pt;line-height:normal;font-size:11pt;font-family:Calibri,sans-serif"><span lang="EN-US" style="font-size:12pt;font-family:"Times New Roman",serif"> </span></p>

<p class="MsoNormal" style="margin:0cm 0cm 0.0001pt;line-height:normal;font-size:11pt;font-family:Calibri,sans-serif"><span lang="EN-US" style="font-size:12pt;font-family:"Times New Roman",serif">I have to conclude, that the native QGIS SQLServer driver is really, really
bad. What's the matter with this driver? </span></p>

<p class="MsoNormal" style="margin:0cm 0cm 0.0001pt;line-height:normal;font-size:11pt;font-family:Calibri,sans-serif"><span lang="EN-US" style="font-size:12pt;font-family:"Times New Roman",serif"> </span></p>

<p class="MsoNormal" style="margin:0cm 0cm 0.0001pt;line-height:normal;font-size:11pt;font-family:Calibri,sans-serif"><span lang="EN-US" style="font-size:12pt;font-family:"Times New Roman",serif">I recollect, doing the same experiment in QGIS 2.x, that a simple fetch of
SQL server data was nearly as fast as retrieving data from a comparable Postgres
database server (I’m not talking about complex spatial queries, where Postgres
blows SQL Server totally out of the water, but simple retrieval of layer data
using a MBR search)</span></p>

<p class="MsoNormal" style="margin:0cm 0cm 0.0001pt;line-height:normal;font-size:11pt;font-family:Calibri,sans-serif"><span lang="EN-US" style="font-size:12pt;font-family:"Times New Roman",serif"> </span></p>

<p class="MsoNormal" style="margin:0cm 0cm 0.0001pt;line-height:normal;font-size:11pt;font-family:Calibri,sans-serif"><span lang="EN-US" style="font-size:12pt;font-family:"Times New Roman",serif">Unfortunately, I can't just switch to the OGR driver since I have some
write issues with this driver.</span></p>

<p class="MsoNormal" style="margin:0cm 0cm 0.0001pt;line-height:normal;font-size:11pt;font-family:Calibri,sans-serif"><span lang="EN-US" style="font-size:12pt;font-family:"Times New Roman",serif"> </span></p>

<p class="MsoNormal" style="margin:0cm 0cm 0.0001pt;line-height:normal;font-size:11pt;font-family:Calibri,sans-serif"><span lang="EN-US" style="font-size:12pt;font-family:"Times New Roman",serif">What gives ???</span></p>

<p class="MsoNormal" style="margin:0cm 0cm 0.0001pt;line-height:normal;font-size:11pt;font-family:Calibri,sans-serif"><span lang="EN-US" style="font-size:12pt;font-family:"Times New Roman",serif"> </span></p>

<p class="MsoNormal" style="margin:0cm 0cm 0.0001pt;line-height:normal;font-size:11pt;font-family:Calibri,sans-serif"><span style="font-size:12pt;font-family:"Times New Roman",serif">-- </span></p>

<p class="MsoNormal" style="margin:0cm 0cm 0.0001pt;line-height:normal;font-size:11pt;font-family:Calibri,sans-serif"><span style="font-size:12pt;font-family:Arial,sans-serif;background-image:initial;background-position:initial;background-size:initial;background-repeat:initial;background-origin:initial;background-clip:initial">Med venlig hilsen</span><span style="font-size:12pt;font-family:"Times New Roman",serif"></span></p>

<p class="MsoNormal" style="margin:0cm 0cm 0.0001pt;line-height:normal;background-image:initial;background-position:initial;background-size:initial;background-repeat:initial;background-origin:initial;background-clip:initial;font-size:11pt;font-family:Calibri,sans-serif"><span style="font-size:12pt;font-family:Arial,sans-serif"> </span></p>

<p class="MsoNormal" style="margin:0cm 0cm 0.0001pt;line-height:normal;background-image:initial;background-position:initial;background-size:initial;background-repeat:initial;background-origin:initial;background-clip:initial;font-size:11pt;font-family:Calibri,sans-serif"><span style="font-size:12pt;font-family:Arial,sans-serif">Bo Victor Thomsen</span></p>

<p class="MsoNormal" style="margin:0cm 0cm 10pt;line-height:115%;font-size:11pt;font-family:Calibri,sans-serif"> <br></p></div></div>