<div dir="ltr"><font face="arial, helvetica, sans-serif">Hey Steven,</font><div><font face="arial, helvetica, sans-serif"><br></font></div><div><font face="arial, helvetica, sans-serif">There is a trick you can do to get your layers to load a bit quicker. The thing that takes the most time when loading a layer is calculating the extents, this is done each time the layer is opened and for large layers this can have a pretty big performance hit. So to fix that issue I have made the SQL driver search for a <span style="color:rgb(0,0,0)">min_x, min_y, max_x, max_y in the </span></font><span style="color:rgb(0,0,0);font-family:arial,helvetica,sans-serif">geometry_columns table </span><span style="color:rgb(0,0,0);font-family:arial,helvetica,sans-serif">in order to just read the extent so there is no on the fly calculation needed when the layer is loaded.</span></div>
<div><span style="color:rgb(0,0,0)"><font face="arial, helvetica, sans-serif"><br></font></span></div><div style><span style="color:rgb(0,0,0)"><font face="arial, helvetica, sans-serif">So your geometry_columns table should look like this:</font></span></div>
<div style><span style="color:rgb(0,0,0);font-family:Cambria,Georgia,'Times New Roman',Times,serif;font-size:medium"><br></span></div><div style><pre class="" style="padding:0px;margin-top:0px;margin-bottom:0px;border:0px;color:rgb(0,0,0)">
<span class="" style="color:rgb(102,102,0)">[</span><span class="">f_table_catalog</span><span class="" style="color:rgb(102,102,0)">]</span><span class=""> </span><span class="" style="color:rgb(102,102,0)">[</span><span class="">varchar</span><span class="" style="color:rgb(102,102,0)">](</span><span class="" style="color:rgb(0,102,102)">128</span><span class="" style="color:rgb(102,102,0)">)</span><span class=""> NOT NULL</span><span class="" style="color:rgb(102,102,0)">,</span><span class=""><br>
</span><span class="" style="color:rgb(102,102,0)">[</span><span class="">f_table_schema</span><span class="" style="color:rgb(102,102,0)">]</span><span class=""> </span><span class="" style="color:rgb(102,102,0)">[</span><span class="">varchar</span><span class="" style="color:rgb(102,102,0)">](</span><span class="" style="color:rgb(0,102,102)">128</span><span class="" style="color:rgb(102,102,0)">)</span><span class=""> NOT NULL</span><span class="" style="color:rgb(102,102,0)">,</span><span class=""><br>
</span><span class="" style="color:rgb(102,102,0)">[</span><span class="">f_table_name</span><span class="" style="color:rgb(102,102,0)">]</span><span class=""> </span><span class="" style="color:rgb(102,102,0)">[</span><span class="">varchar</span><span class="" style="color:rgb(102,102,0)">](</span><span class="" style="color:rgb(0,102,102)">256</span><span class="" style="color:rgb(102,102,0)">)</span><span class=""> NOT NULL</span><span class="" style="color:rgb(102,102,0)">,</span><span class=""><br>
</span><span class="" style="color:rgb(102,102,0)">[</span><span class="">f_geometry_column</span><span class="" style="color:rgb(102,102,0)">]</span><span class=""> </span><span class="" style="color:rgb(102,102,0)">[</span><span class="">varchar</span><span class="" style="color:rgb(102,102,0)">](</span><span class="" style="color:rgb(0,102,102)">256</span><span class="" style="color:rgb(102,102,0)">)</span><span class=""> NOT NULL</span><span class="" style="color:rgb(102,102,0)">,</span><span class=""><br>
</span><span class="" style="color:rgb(102,102,0)">[</span><span class="">coord_dimension</span><span class="" style="color:rgb(102,102,0)">]</span><span class=""> </span><span class="" style="color:rgb(102,102,0)">[</span><span class="" style="color:rgb(0,0,136)">int</span><span class="" style="color:rgb(102,102,0)">]</span><span class=""> NOT NULL</span><span class="" style="color:rgb(102,102,0)">,</span><span class=""><br>
</span><span class="" style="color:rgb(102,102,0)">[</span><span class="">srid</span><span class="" style="color:rgb(102,102,0)">]</span><span class=""> </span><span class="" style="color:rgb(102,102,0)">[</span><span class="" style="color:rgb(0,0,136)">int</span><span class="" style="color:rgb(102,102,0)">]</span><span class=""> NOT NULL</span><span class="" style="color:rgb(102,102,0)">,</span><span class=""><br>
</span><span class="" style="color:rgb(102,102,0)">[</span><span class="">geometry_type</span><span class="" style="color:rgb(102,102,0)">]</span><span class=""> </span><span class="" style="color:rgb(102,102,0)">[</span><span class="">varchar</span><span class="" style="color:rgb(102,102,0)">](</span><span class="" style="color:rgb(0,102,102)">30</span><span class="" style="color:rgb(102,102,0)">)</span><span class=""> NOT NULL</span><span class="" style="color:rgb(102,102,0)">,</span><span class=""><br>
</span><span class="" style="color:rgb(102,102,0)">[</span><span class="">min_x</span><span class="" style="color:rgb(102,102,0)">]</span><span class=""> </span><span class="" style="color:rgb(102,102,0)">[</span><span class="" style="color:rgb(0,0,136)">double</span><span class="" style="color:rgb(102,102,0)">]</span><span class=""> NULL</span><span class="" style="color:rgb(102,102,0)">,</span><span class=""><br>
</span><span class="" style="color:rgb(102,102,0)">[</span><span class="">min_y</span><span class="" style="color:rgb(102,102,0)">]</span><span class=""> </span><span class="" style="color:rgb(102,102,0)">[</span><span style="color:rgb(0,0,136);font-family:arial">double</span><span class="" style="font-family:arial;color:rgb(102,102,0)">]</span><span class="" style="font-family:arial"> NULL</span><span class="" style="font-family:arial;color:rgb(102,102,0)">,</span><br>
<span class="" style="color:rgb(102,102,0)">[</span><span class="">max_x</span><span class="" style="color:rgb(102,102,0)">]</span><span class=""> </span><span class="" style="color:rgb(102,102,0)">[</span><span style="color:rgb(0,0,136);font-family:arial">double</span><span class="" style="font-family:arial;color:rgb(102,102,0)">]</span><span class="" style="font-family:arial"> NULL</span><span class="" style="font-family:arial;color:rgb(102,102,0)">,</span><br>
<span class="" style="color:rgb(102,102,0)">[</span><span class="">max_y</span><span class="" style="color:rgb(102,102,0)">]</span><span class=""> </span><span class="" style="color:rgb(102,102,0)">[</span><span style="color:rgb(0,0,136);font-family:arial">double</span><span class="" style="font-family:arial;color:rgb(102,102,0)">]</span><span class="" style="font-family:arial"> NULL</span><br>
</pre><pre class="" style="padding:0px;margin-top:0px;margin-bottom:0px;border:0px;color:rgb(0,0,0)"><span class=""><br></span></pre><pre class="" style="padding:0px;margin-top:0px;margin-bottom:0px;border:0px;color:rgb(0,0,0)">
<span class=""><font face="arial, helvetica, sans-serif">The last four columns are the new ones and you can calculate a rough extents to go in the table like:</font></span></pre><pre class="" style="padding:0px;margin-top:0px;margin-bottom:0px;border:0px;color:rgb(0,0,0)">
<span class=""><br></span></pre><pre class="" style="padding:0px;margin-top:0px;margin-bottom:0px;border:0px"><table style="border-collapse:collapse;border:none;font-family:Cambria,Georgia,'Times New Roman',Times,serif;font-size:medium;white-space:normal">
<tbody><tr><td class="" style="border-collapse:collapse;border:none;background-color:transparent"><pre class="" style="padding:0px;margin-top:0px;margin-bottom:0px;border:0px;color:rgb(170,170,170);text-align:right">
</pre></td><td style="border-collapse:collapse;border:none;background-color:transparent"><pre class="" style="padding:0px;margin-top:0px;margin-bottom:0px;border:0px"><span class="" style="color:rgb(0,0,136)">select</span><span class=""> min</span><span class="" style="color:rgb(102,102,0)">([</span><span class="">ogr_geometry</span><span class="" style="color:rgb(102,102,0)">].</span><span class="" style="color:rgb(102,0,102)">STPointN</span><span class="" style="color:rgb(102,102,0)">(</span><span class="" style="color:rgb(0,102,102)">1</span><span class="" style="color:rgb(102,102,0)">).</span><span class="">STX</span><span class="" style="color:rgb(102,102,0)">),</span><span class=""> <br>
min</span><span class="" style="color:rgb(102,102,0)">([</span><span class="">ogr_geometry</span><span class="" style="color:rgb(102,102,0)">].</span><span class="" style="color:rgb(102,0,102)">STPointN</span><span class="" style="color:rgb(102,102,0)">(</span><span class="" style="color:rgb(0,102,102)">1</span><span class="" style="color:rgb(102,102,0)">).</span><span class="">STY</span><span class="" style="color:rgb(102,102,0)">),</span><span class=""> <br>
max</span><span class="" style="color:rgb(102,102,0)">([</span><span class="">ogr_geometry</span><span class="" style="color:rgb(102,102,0)">].</span><span class="" style="color:rgb(102,0,102)">STPointN</span><span class="" style="color:rgb(102,102,0)">(</span><span class="" style="color:rgb(0,102,102)">1</span><span class="" style="color:rgb(102,102,0)">).</span><span class="">STX</span><span class="" style="color:rgb(102,102,0)">),</span><span class=""> <br>
max</span><span class="" style="color:rgb(102,102,0)">([</span><span class="">ogr_geometry</span><span class="" style="color:rgb(102,102,0)">].</span><span class="" style="color:rgb(102,0,102)">STPointN</span><span class="" style="color:rgb(102,102,0)">(</span><span class="" style="color:rgb(0,102,102)">1</span><span class="" style="color:rgb(102,102,0)">).</span><span class="">STY</span><span class="" style="color:rgb(102,102,0)">)</span><span class=""><br>
FROM </span><span class="" style="color:rgb(102,102,0)">[</span><span class="">TABLE</span><span class="" style="color:rgb(102,102,0)">]</span></pre><pre class="" style="padding:0px;margin-top:0px;margin-bottom:0px;border:0px">
<span class="" style="color:rgb(102,102,0)"><br></span></pre><pre class="" style="padding:0px;margin-top:0px;margin-bottom:0px;border:0px"><span class=""><font face="arial, helvetica, sans-serif" color="#000000">An entry in the table might look like:</font></span></pre>
<pre class="" style="padding:0px;margin-top:0px;margin-bottom:0px;border:0px"><span class=""><font face="arial, helvetica, sans-serif" color="#000000"><br></font></span></pre><pre class="" style="padding:0px;margin-top:0px;margin-bottom:0px;border:0px">
<span class=""><font face="arial, helvetica, sans-serif" color="#000000"><a href="http://i.imgur.com/gq8DayI.png">http://i.imgur.com/gq8DayI.png</a><br></font></span></pre><pre class="" style="padding:0px;margin-top:0px;margin-bottom:0px;border:0px">
<span class=""><font face="arial, helvetica, sans-serif" color="#000000"><br></font></span></pre><pre class="" style="padding:0px;margin-top:0px;margin-bottom:0px;border:0px"><font face="arial, helvetica, sans-serif" color="#000000">Note: This will only work in the current dev version of QGIS.</font></pre>
<pre class="" style="padding:0px;margin-top:0px;margin-bottom:0px;border:0px"><font color="#000000" face="arial, helvetica, sans-serif"><br></font></pre><pre class="" style="padding:0px;margin-top:0px;margin-bottom:0px;border:0px">
<font color="#000000" face="arial, helvetica, sans-serif">- Nathan</font></pre><pre class="" style="padding:0px;margin-top:0px;margin-bottom:0px;border:0px"><span class=""><font color="#000000" face="arial, helvetica, sans-serif"><br>
</font></span></pre></td></tr></tbody></table></pre></div>
</div><div class="gmail_extra"><br><br><div class="gmail_quote">On Sat, Jun 29, 2013 at 12:00 AM, Steven Campbell <span dir="ltr"><<a href="mailto:s.campbell@poole.gov.uk" target="_blank">s.campbell@poole.gov.uk</a>></span> wrote:<br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div lang="EN-GB" link="blue" vlink="purple"><div><p class="MsoNormal"><span style="font-family:"Arial","sans-serif"">Hi<u></u><u></u></span></p>
<p class="MsoNormal"><span style="font-family:"Arial","sans-serif""><u></u> <u></u></span></p><p class="MsoNormal"><span style="font-family:"Arial","sans-serif"">Does anyone else have significant time delays when trying to open large datasets from a SQL database in QGIS? I have uploaded my Ordnance survey MasterMap data (coverage is the size of an average English County) and although the initial connection to identify the table is very quick, the loading of the table (even when zoomed in to a very big scale (i.e. 1:100) it takes a very long time to open. Once opened the speed is very quick, but getting it to open takes a lot of patience as the application appears to hang.<u></u><u></u></span></p>
<p class="MsoNormal"><span style="font-family:"Arial","sans-serif""><u></u> <u></u></span></p><p class="MsoNormal"><span style="font-family:"Arial","sans-serif"">On a slightly related topic, if I have connected to one MSSQL database, QGIS then struggles if I try to connect to an additional database, again it just appears to hang for a considerable period of time (30 minutes or more at times), it seems odd especially as the connection to the first SQL database is almost instant....<u></u><u></u></span></p>
<p class="MsoNormal"><span style="font-family:"Arial","sans-serif""><u></u> <u></u></span></p><p class="MsoNormal"><span style="font-family:"Arial","sans-serif"">Any suggestions as to how I can improve this speed would be greatly appreciated.<u></u><u></u></span></p>
<p class="MsoNormal"><span style="font-family:"Arial","sans-serif""><u></u> <u></u></span></p><p class="MsoNormal"><span style="font-family:"Arial","sans-serif"">Thanks<u></u><u></u></span></p>
<p class="MsoNormal"><span style="font-family:"Arial","sans-serif""><u></u> <u></u></span></p><p class="MsoNormal"><span style="font-family:"Arial","sans-serif"">Steve Campbell<u></u><u></u></span></p>
<p class="MsoNormal"><span style="font-family:"Arial","sans-serif"">GIS Manager<u></u><u></u></span></p><p class="MsoNormal"><span style="font-family:"Arial","sans-serif"">Corporate Strategy and Communications<u></u><u></u></span></p>
<p class="MsoNormal"><span style="font-family:"Arial","sans-serif"">Borough of Poole | Civic Centre | Poole BH15 2RU<u></u><u></u></span></p><p class="MsoNormal"><span style="font-family:"Arial","sans-serif"">Tel: 01202 633 362<u></u><u></u></span></p>
<p class="MsoNormal"><span style="font-family:"Arial","sans-serif"">Email: <a href="mailto:s.campbell@poole.gov.uk" target="_blank">s.campbell@poole.gov.uk</a><u></u><u></u></span></p><p class="MsoNormal">
<span style="font-family:"Arial","sans-serif"">Website:<a href="http://www.boroughofpoole.com/" target="_blank">www.boroughofpoole.com</a><u></u><u></u></span></p><p class="MsoNormal"><b><i><span style="font-family:"Arial","sans-serif";color:#00b050">Think Green! Please Recycle<u></u><u></u></span></i></b></p>
<p class="MsoNormal"><u></u> <u></u></p></div><p></p><p>DISCLAIMER:<br>This email and any files transmitted with it may be confidential, legally privileged and protected in law and are intended solely for the use of the individual to whom it is addressed. The copyright in all documentation is the property of the Borough of Poole and this email and any documentation must not be copied or used other than as strictly necessary for the purpose of this email, without prior written consent which may be subject to conditions. Any view or opinions presented are solely those of the author and do not necessarily represent those of the Borough of Poole. The Borough of Poole reserves the right to inspect incoming and outgoing emails. If you have received this email in error please contact the sender by return and confirm that its contents have been destroyed. Telephone enquiries should be directed to the Borough switchboard on 01202 633633.</p>
</div><br>_______________________________________________<br>
Qgis-user mailing list<br>
<a href="mailto:Qgis-user@lists.osgeo.org">Qgis-user@lists.osgeo.org</a><br>
<a href="http://lists.osgeo.org/mailman/listinfo/qgis-user" target="_blank">http://lists.osgeo.org/mailman/listinfo/qgis-user</a><br>
<br></blockquote></div><br></div>