https://issues.qgis.org/https://issues.qgis.org/favicon.ico2016-10-25T09:58:06ZQGIS Issue TrackingQGIS Application - Bug report #15752: Degradation of rendering performances in MSSQL providerhttps://issues.qgis.org/issues/15752?journal_id=732432016-10-25T09:58:06ZNyall Dawson
<ul></ul><p>What renderer/symbol style are you using?</p> QGIS Application - Bug report #15752: Degradation of rendering performances in MSSQL providerhttps://issues.qgis.org/issues/15752?journal_id=732442016-10-25T09:58:15ZNyall Dawson
<ul><li><strong>Status</strong> changed from <i>Open</i> to <i>Feedback</i></li></ul> QGIS Application - Bug report #15752: Degradation of rendering performances in MSSQL providerhttps://issues.qgis.org/issues/15752?journal_id=732452016-10-25T10:26:32ZAndre Jesusandrerenatoj@gmail.com
<ul></ul><p><cite>What renderer/symbol style are you using</cite>?<br />I'm using default QGIS style.</p>
<p>The same occurs using WFS:</p>
<p><strong>ly1 WFS/linestring</strong></p>
<p>2.8.x = 16 ms<br />2.14.x+ = 517 ms</p>
<p><strong>ly2 WFS/polygon</strong></p>
<p>2.8.x = 17 ms<br />2.14.x+ = 219 ms</p>
<p><strong>ly3 WFS/point</strong></p>
<p>2.8.x = 15 ms<br />2.14.x+ = 647 ms</p>
<p>Always using the same bookmark and data.</p>
<p>I have 20 other machines with the same results.</p> QGIS Application - Bug report #15752: Degradation of rendering performances in MSSQL providerhttps://issues.qgis.org/issues/15752?journal_id=732462016-10-25T10:29:09ZAndre Jesusandrerenatoj@gmail.com
<ul></ul><p>Sorry, I don't know if my answer was clear. I'm using default Single Symbol style without any change.</p> QGIS Application - Bug report #15752: Degradation of rendering performances in MSSQL providerhttps://issues.qgis.org/issues/15752?journal_id=732482016-10-25T22:21:53ZNyall Dawson
<ul><li><strong>Status</strong> changed from <i>Feedback</i> to <i>Closed</i></li></ul><p>Fixed in changeset <a class="changeset" href="https://issues.qgis.org/projects/qgis/repository/revisions/5798a82c8011ea7f44a1ed1d55ef0719786e8056" title="Speed up point layer rendering - don't calculate unused label obstacles Cuts render time by ~60%...">5798a82c8011ea7f44a1ed1d55ef0719786e8056</a>.</p> QGIS Application - Bug report #15752: Degradation of rendering performances in MSSQL providerhttps://issues.qgis.org/issues/15752?journal_id=734812016-11-25T04:52:31ZSteve Lowman
<ul><li><strong>Target version</strong> changed from <i>Version 2.18</i> to <i>Version 2.14</i></li><li><strong>Status</strong> changed from <i>Closed</i> to <i>Reopened</i></li></ul><p>Has this been fixed in 2.18 but not 2.14? I just changed the Target version from 2.18 to 2.14 - hope that's ok.</p>
<p>We use a large feature set of UK Ordnance Survey MasterMap (OSMM) Topographic Layer in shapefiles in the LTR (currently 2.14.8), about 100K ha area. The data are stored on a local server in the same building, so bandwidth is not a big part of this issue. Topographic Line is geometrically the most complex OSMM feature set, and this has a noticeable lag in rendering with this large feature set. The lag is > 2 secs at 1:20K scale, slower at smaller scales (larger visible extent), quicker at larger scales (smaller visible extent).</p>
<p>Our workaround is to break up the feature set into five smaller chunks, and then the lag is a lot less at any given scale. This is a pain, because we have to divide it up every time we get an updated OSMM feature set.</p>
<p>When this first started happening was the first release of 2.14, and I assumed it was probably caused by the more complex geometry engine that allows curved lines.</p> QGIS Application - Bug report #15752: Degradation of rendering performances in MSSQL providerhttps://issues.qgis.org/issues/15752?journal_id=734832016-11-25T10:58:30ZNyall Dawson
<ul><li><strong>Status</strong> changed from <i>Reopened</i> to <i>Closed</i></li></ul><p>These fixes are included in 2.14.9 and 2.18.1. Try with those versions. Otherwise this is likely a different issue and a new report needs to be opened.</p>
<p>Fyi, new geoemtry was introduced in 2.10</p> QGIS Application - Bug report #15752: Degradation of rendering performances in MSSQL providerhttps://issues.qgis.org/issues/15752?journal_id=735082016-11-30T06:50:14ZAndre Jesusandrerenatoj@gmail.com
<ul><li><strong>Target version</strong> changed from <i>Version 2.14</i> to <i>Version 2.18</i></li><li><strong>Status</strong> changed from <i>Closed</i> to <i>Reopened</i></li></ul><p>I did the compare again using QGIS 2.18.1</p>
<p><img src="https://s17.postimg.org/p5b8324nj/DATA.png" alt="" /></p>
<p>I can see some improvements rendering polygons, but not on points and linestrings.</p>
<p>ps.: Render times are rounded.</p> QGIS Application - Bug report #15752: Degradation of rendering performances in MSSQL providerhttps://issues.qgis.org/issues/15752?journal_id=735092016-11-30T13:35:38ZNyall Dawson
<ul></ul><p>Hmm... that's quite odd. You should definitely see an improvement in 2.18.1.</p>
<p>Are you using labels in these tests?</p> QGIS Application - Bug report #15752: Degradation of rendering performances in MSSQL providerhttps://issues.qgis.org/issues/15752?journal_id=735162016-12-01T06:51:34ZAndre Jesusandrerenatoj@gmail.com
<ul></ul><p>The last time I tested It I did a dirt install. <br />To cross It out I set up a new VM with Windows 7 32 bits, made a snapshot before installing any software.<br />I didn't tweaked any setting in QGIS but enable Debug: Map Canvas Refresh</p>
<p>Here the new results:</p>
<p><img src="https://s18.postimg.org/kxwxdp4kp/data2.png" alt="" /></p>
<p>Yes, I was using simple style without labels or any change to default QGIS style.</p>
<p><img src="https://s17.postimg.org/lnh8gvuxb/data3.png" alt="" /></p> QGIS Application - Bug report #15752: Degradation of rendering performances in MSSQL providerhttps://issues.qgis.org/issues/15752?journal_id=739652017-01-04T22:20:59ZGiovanni Manghigiovanni.manghi@gmail.com
<ul><li><strong>Category</strong> set to <i>Map Canvas</i></li></ul> QGIS Application - Bug report #15752: Degradation of rendering performances in MSSQL providerhttps://issues.qgis.org/issues/15752?journal_id=748682017-03-06T08:44:53ZAndre Jesusandrerenatoj@gmail.com
<ul></ul><p>Latest update: Using 2.18.4</p>
<p>Render time seems to keep rising every update.</p>
<p>Every relatively big layer I have now became a slug with version 2.18.4.</p>
<p>To me, It looks like QGIS doesn't pass the bounding box when querying data from the database, bringing all data all the time. If I query 1000 or all 170.000 features, the rendering time is about the same. While, if I do the same using PostgreSQL I jump from 42 ms (1.000 feature count) to 2400 ms (173.000 features)</p>
<pre>
173.000 polygon layer, average render time (3 runs):
1:125.000 (173.000 features)
Postgres: 2383 ms
Mssql: 20151 ms
1:4.000 (1.300 features)
Postgres: 140 ms
Mssql: 13756 ms
</pre>
<p>As shown above, PostgreSQl had a 94% render time reduction and MSSQL only 31%, that considering a 99% features displayed reduction.</p> QGIS Application - Bug report #15752: Degradation of rendering performances in MSSQL providerhttps://issues.qgis.org/issues/15752?journal_id=748702017-03-06T08:52:09ZGiovanni Manghigiovanni.manghi@gmail.com
<ul><li><strong>Affected QGIS version</strong> changed from <i>2.18.0</i> to <i>2.18.4</i></li></ul> QGIS Application - Bug report #15752: Degradation of rendering performances in MSSQL providerhttps://issues.qgis.org/issues/15752?journal_id=750412017-03-07T09:05:50ZAndre Jesusandrerenatoj@gmail.com
<ul></ul><p>I did some more testing...</p>
<p>Using the same map, layers and zoom levels.</p>
<p>Feature list and count:<br /><img src="http://i.imgur.com/qHBfJee.png" alt="" /></p>
<p>Zoom: 1:400.000<br /><img src="http://i.imgur.com/opB6UHE.png" alt="" /></p>
<p><img src="http://i.imgur.com/LyWia7I.png" alt="" /></p>
<p>Zoom: 1:7.000<br /><img src="http://i.imgur.com/JJc3wvR.png" alt="" /></p>
<p><img src="http://i.imgur.com/vcf3SaK.png" alt="" /></p>
<p>Wider zoom levels:<br />2.8.9 > 2.14.12 = + 41% render time<br />2.14.12 > 2.18.4 = <strong>+ 290% render time</strong></p>
<p>Smaller zoom levels:<br />2.8.9 > 2.14.12 = + 7% render time<br />2.14.12 > 2.18.4 = <strong>+ 4604% render time</strong></p>
<p>I know MSSQL data provider is not the focus in development but it's impossible to work with It in 2.18.4.</p> QGIS Application - Bug report #15752: Degradation of rendering performances in MSSQL providerhttps://issues.qgis.org/issues/15752?journal_id=750422017-03-07T09:09:40ZGiovanni Manghigiovanni.manghi@gmail.com
<ul></ul><blockquote>
<p>I know MSSQL data provider is not the focus in development but it's impossible to work with It in 2.18.4.</p>
</blockquote>
<p>please raise this matter on the users and/or developers mailing lists.</p> QGIS Application - Bug report #15752: Degradation of rendering performances in MSSQL providerhttps://issues.qgis.org/issues/15752?journal_id=750462017-03-07T09:35:41ZAndre Jesusandrerenatoj@gmail.com
<ul></ul><p>Giovanni Manghi wrote:</p>
<blockquote><blockquote>
<p>I know MSSQL data provider is not the focus in development but it's impossible to work with It in 2.18.4.</p>
</blockquote>
<p>please raise this matter on the users and/or developers mailing lists.</p>
</blockquote>
<p>Ok.</p>
<p>Each one would be more suitable: User or Developers mailing list? I've never joined one before.<br />I'm no developer but I'd like to contribute as I can.</p> QGIS Application - Bug report #15752: Degradation of rendering performances in MSSQL providerhttps://issues.qgis.org/issues/15752?journal_id=750502017-03-07T09:49:32ZGiovanni Manghigiovanni.manghi@gmail.com
<ul></ul><p>Andre Jesus wrote:</p>
<blockquote>
<p>Giovanni Manghi wrote:</p>
<blockquote><blockquote>
<p>I know MSSQL data provider is not the focus in development but it's impossible to work with It in 2.18.4.</p>
</blockquote>
<p>please raise this matter on the users and/or developers mailing lists.</p>
</blockquote>
<p>Ok.</p>
<p>Each one would be more suitable: User or Developers mailing list? I've never joined one before.<br />I'm no developer but I'd like to contribute as I can.</p>
</blockquote>
<p>the developers one (which is not followed just by developers) is the right place for me: there you find the people that may help understand because of this regression.</p> QGIS Application - Bug report #15752: Degradation of rendering performances in MSSQL providerhttps://issues.qgis.org/issues/15752?journal_id=750942017-03-08T03:17:25ZGiovanni Manghigiovanni.manghi@gmail.com
<ul></ul><p>Andre Jesus wrote:</p>
<blockquote>
<p>Giovanni Manghi wrote:</p>
<blockquote><blockquote>
<p>I know MSSQL data provider is not the focus in development but it's impossible to work with It in 2.18.4.</p>
</blockquote>
<p>please raise this matter on the users and/or developers mailing lists.</p>
</blockquote>
<p>Ok.</p>
<p>Each one would be more suitable: User or Developers mailing list? I've never joined one before.<br />I'm no developer but I'd like to contribute as I can.</p>
</blockquote>
<p>One question: have you any idea if this also affects other providers, like PostGIS?</p> QGIS Application - Bug report #15752: Degradation of rendering performances in MSSQL providerhttps://issues.qgis.org/issues/15752?journal_id=751022017-03-08T05:44:31ZAndre Jesusandrerenatoj@gmail.com
<ul></ul><p>Giovanni Manghi wrote:</p>
<blockquote>
<p>One question: have you any idea if this also affects other providers, like PostGIS?</p>
</blockquote>
<p>It doesn't.</p>
<p>Running the same bench test using Shapefiles or PostGIS both gives me ~1500 ms</p> QGIS Application - Bug report #15752: Degradation of rendering performances in MSSQL providerhttps://issues.qgis.org/issues/15752?journal_id=751372017-03-08T12:28:03ZAndre Jesusandrerenatoj@gmail.com
<ul></ul><p>Just checking how performance went with other data providers over the 3 versions:</p>
<p>Using the same map but with a different zoom level (1:177.000)<br /><img src="http://i.imgur.com/7xm8tHf.png" alt="" /></p>
<p><a href="http://imgur.com/a/Sdw0e" class="external">full printscreens</a></p>
<p>While doing the benchmark, QGIS force closed 5 times using 2.18.4 and 2 using 2.14.12 when refreshing MSSQL layers. None with the other providers.</p>
<p>2.8.9 is by far the fastest, but It's understandable by the amount of features added to QGIS It would slow It down.</p> QGIS Application - Bug report #15752: Degradation of rendering performances in MSSQL providerhttps://issues.qgis.org/issues/15752?journal_id=751412017-03-08T13:46:14ZGiovanni Manghigiovanni.manghi@gmail.com
<ul></ul><blockquote>
<p>2.8.9 is by far the fastest, but It's understandable by the amount of features added to QGIS It would slow It down.</p>
</blockquote>
<p>well... I don't think that we should assume that is all ok also with other providers... it seems to me that is pretty bad also for postgis and shapefiles.</p>
<p>I noticed a very noticeable degradation of performances in QGIS3 master compared to 2.18.4 and reported it, hopefully this will be sorted and fixed for all providers.</p> QGIS Application - Bug report #15752: Degradation of rendering performances in MSSQL providerhttps://issues.qgis.org/issues/15752?journal_id=751782017-03-09T08:04:03ZAndre Jesusandrerenatoj@gmail.com
<ul></ul><p>It's not related how QGIS request data from the database as all version uses the same intersects method with the same limit. <br />And that kills my theory he may be request all geometries all the time.</p>
<pre>SELECT [OBJECTID],[GEOMETRY]FROM [dbo].[LIGACOES] where [GEOMETRY].STIsValid() = 1 AND [GEOMETRY].STIntersects([geometry]::STGeomFromText('POLYGON((589164.07187500002328306 8262041.58740026596933603, 614628.80312499997671694 8262041.58740026596933603, 614628.80312499997671694 8286332.41259973403066397, 589164.07187500002328306 8286332.41259973403066397, 589164.07187500002328306 8262041.58740026596933603))',29191)) = 1</pre>
<p>But checking SQL Server Profiler I noticed different values in CPU cycles and duration, even they all using the exactly same query, the values ares persistent.</p>
<p>Again a 3 times avg:</p>
<p><img src="http://i.imgur.com/gInM45M.png" alt="" /></p>
<p>It could be a good lead to find out what went wrong, but my knowledge ends here.</p> QGIS Application - Bug report #15752: Degradation of rendering performances in MSSQL providerhttps://issues.qgis.org/issues/15752?journal_id=753692017-03-31T03:15:07ZAndre Jesusandrerenatoj@gmail.com
<ul></ul><p>Update versions: 2.14.13 and 2.18.5</p>
<p>Loading a 5 layers project (feature count: 201.528 points, 36.521 linestrings, 24.957 linestrings, 9.231 polygons, 9.875 polygons), 5x avg canvas refresh time, roughly rounded values:</p>
<p><strong>2.14.13</strong><br />3500 ms mssql<br />1500 ms postgis</p>
<p><strong>2.18.5</strong><br />19443 ms mssql<br />1400 ms postgis</p>
<p>2.14.13 seems to have stabilized the performance using MSSQL. On the other hand 2.18.5 is getting worse, jumping from ~15.000 ms to ~20.000 ms (25% increase)</p>
<p>Edit: 2.14.13 feels faster now</p> QGIS Application - Bug report #15752: Degradation of rendering performances in MSSQL providerhttps://issues.qgis.org/issues/15752?journal_id=754572017-04-12T14:54:25ZNyall Dawson
<ul><li><strong>Status</strong> changed from <i>Reopened</i> to <i>Feedback</i></li></ul><p>To summarise - I believe that when disregarding the comparisons between release and debug builds the issue is reduced to a regression in the mssql provider alone. Is this correct?</p> QGIS Application - Bug report #15752: Degradation of rendering performances in MSSQL providerhttps://issues.qgis.org/issues/15752?journal_id=754622017-04-13T02:09:52ZAndre Jesusandrerenatoj@gmail.com
<ul></ul><p>None of the tests I did here were using debug versions (by that time I didn't even know I was able to install and use them).</p>
<p>I think the performance degradation does impact all providers, just not as much as It impacts MSSQL (see note <a class="issue tracker-1 status-5 priority-5 priority- closed" href="https://issues.qgis.org/issues/15752#note-20" title="Degradation of rendering performances in MSSQL provider (Closed)">#15752-20</a>).</p> QGIS Application - Bug report #15752: Degradation of rendering performances in MSSQL providerhttps://issues.qgis.org/issues/15752?journal_id=756362017-04-29T04:23:42ZGiovanni Manghigiovanni.manghi@gmail.com
<ul><li><strong>Status</strong> changed from <i>Feedback</i> to <i>Open</i></li></ul><p>I think that note 20 says a lot. Many thanks to the reported for this very detailed analysis!</p> QGIS Application - Bug report #15752: Degradation of rendering performances in MSSQL providerhttps://issues.qgis.org/issues/15752?journal_id=757152017-04-30T15:06:20ZGiovanni Manghigiovanni.manghi@gmail.com
<ul><li><strong>Regression?</strong> set to <i>Yes</i></li></ul> QGIS Application - Bug report #15752: Degradation of rendering performances in MSSQL providerhttps://issues.qgis.org/issues/15752?journal_id=757992017-04-30T15:08:55ZGiovanni Manghigiovanni.manghi@gmail.com
<ul><li><strong>Priority</strong> changed from <i>Severe/Regression</i> to <i>High</i></li></ul> QGIS Application - Bug report #15752: Degradation of rendering performances in MSSQL providerhttps://issues.qgis.org/issues/15752?journal_id=787752017-04-30T23:10:55ZGiovanni Manghigiovanni.manghi@gmail.com
<ul><li><strong>Easy fix?</strong> set to <i>No</i></li></ul> QGIS Application - Bug report #15752: Degradation of rendering performances in MSSQL providerhttps://issues.qgis.org/issues/15752?journal_id=793442017-05-02T16:09:56ZAndre Jesusandrerenatoj@gmail.com
<ul></ul><p>Update: 2.14.14 LTS / 2.18.7</p>
<p><img src="http://i.imgur.com/urWF6Dq.png" alt="" /></p>
<p>Using the same layers used to benchmark on note 20. <br />Testing the first run and 3 consecutive canvas refreshes using 1:255.000 and 1:7.000 scales. Using default OSGeo installation, no extra plugin installed.<br />I had an OS problem and I lost 2.8.9 installer, that's why there is no results for it.</p>
<p>MSSQL performance is still terrible, but PostGIS had a nice improvement.</p> QGIS Application - Bug report #15752: Degradation of rendering performances in MSSQL providerhttps://issues.qgis.org/issues/15752?journal_id=798352017-05-18T21:25:04ZGiovanni Manghigiovanni.manghi@gmail.com
<ul><li><strong>Description</strong> updated (<a href="/journals/diff/79835?detail_id=68581" title="View differences">diff</a>)</li></ul><p>see also <a class="issue tracker-1 status-5 priority-5 priority- closed" href="https://issues.qgis.org/issues/16577" title="Extremely slower time to open attribute table in 2.18.7 compared to 2.14.14 (Closed)">#16577</a></p> QGIS Application - Bug report #15752: Degradation of rendering performances in MSSQL providerhttps://issues.qgis.org/issues/15752?journal_id=798462017-05-19T06:56:58ZJürgen Fischerjef@norbit.de
<ul><li><strong>Related to</strong> <i><a class="issue tracker-1 status-5 priority-5 priority- closed" href="/issues/16577">Bug report #16577</a>: Extremely slower time to open attribute table in 2.18.7 compared to 2.14.14</i> added</li></ul> QGIS Application - Bug report #15752: Degradation of rendering performances in MSSQL providerhttps://issues.qgis.org/issues/15752?journal_id=799082017-05-20T19:37:11ZGiovanni Manghigiovanni.manghi@gmail.com
<ul></ul><p>see also: <a class="external" href="https://lists.osgeo.org/pipermail/qgis-psc/2017-May/005258.html">https://lists.osgeo.org/pipermail/qgis-psc/2017-May/005258.html</a></p> QGIS Application - Bug report #15752: Degradation of rendering performances in MSSQL providerhttps://issues.qgis.org/issues/15752?journal_id=799792017-05-23T08:47:17ZNyall Dawson
<ul><li><strong>Status</strong> changed from <i>Open</i> to <i>Closed</i></li><li><strong>% Done</strong> changed from <i>0</i> to <i>100</i></li></ul><p>Applied in changeset <a class="changeset" href="https://issues.qgis.org/projects/qgis/repository/revisions/5798a82c8011ea7f44a1ed1d55ef0719786e8056" title="Speed up point layer rendering - don't calculate unused label obstacles Cuts render time by ~60%...">qgis|5798a82c8011ea7f44a1ed1d55ef0719786e8056</a>.</p> QGIS Application - Bug report #15752: Degradation of rendering performances in MSSQL providerhttps://issues.qgis.org/issues/15752?journal_id=799862017-05-23T10:52:09ZGiovanni Manghigiovanni.manghi@gmail.com
<ul></ul><p>Nyall Dawson wrote:</p>
<blockquote>
<p>Applied in changeset <a class="changeset" href="https://issues.qgis.org/projects/qgis/repository/revisions/5798a82c8011ea7f44a1ed1d55ef0719786e8056" title="Speed up point layer rendering - don't calculate unused label obstacles Cuts render time by ~60%...">qgis|5798a82c8011ea7f44a1ed1d55ef0719786e8056</a>.</p>
</blockquote>
<p>Dear Andre Jesus would you mind see if the above commit improves things as expected? thanks!</p> QGIS Application - Bug report #15752: Degradation of rendering performances in MSSQL providerhttps://issues.qgis.org/issues/15752?journal_id=799952017-05-23T13:08:26ZGiovanni Manghigiovanni.manghi@gmail.com
<ul></ul><p>Giovanni Manghi wrote:</p>
<blockquote>
<p>Nyall Dawson wrote:</p>
<blockquote>
<p>Applied in changeset <a class="changeset" href="https://issues.qgis.org/projects/qgis/repository/revisions/5798a82c8011ea7f44a1ed1d55ef0719786e8056" title="Speed up point layer rendering - don't calculate unused label obstacles Cuts render time by ~60%...">qgis|5798a82c8011ea7f44a1ed1d55ef0719786e8056</a>.</p>
</blockquote>
<p>Dear Andre Jesus would you mind see if the above commit improves things as expected? thanks!</p>
</blockquote>
<p>see also <a class="external" href="https://drive.google.com/file/d/0B8VD1V_leaeWNmpaOFNxZGxicVU/view?usp=sharing">https://drive.google.com/file/d/0B8VD1V_leaeWNmpaOFNxZGxicVU/view?usp=sharing</a></p> QGIS Application - Bug report #15752: Degradation of rendering performances in MSSQL providerhttps://issues.qgis.org/issues/15752?journal_id=801202017-05-26T14:23:37ZGiovanni Manghigiovanni.manghi@gmail.com
<ul><li><strong>Subject</strong> changed from <i>Slow render time</i> to <i>Degradation of rendering performances in MSSQL provider</i></li><li><strong>Status</strong> changed from <i>Closed</i> to <i>Open</i></li></ul> QGIS Application - Bug report #15752: Degradation of rendering performances in MSSQL providerhttps://issues.qgis.org/issues/15752?journal_id=801222017-05-26T14:32:25ZGiovanni Manghigiovanni.manghi@gmail.com
<ul></ul><p>reopened as requested here</p>
<p><a class="external" href="https://lists.osgeo.org/pipermail/qgis-psc/2017-May/005319.html">https://lists.osgeo.org/pipermail/qgis-psc/2017-May/005319.html</a></p> QGIS Application - Bug report #15752: Degradation of rendering performances in MSSQL providerhttps://issues.qgis.org/issues/15752?journal_id=802862017-06-03T06:03:11ZNyall Dawson
<ul><li><strong>Status</strong> changed from <i>Open</i> to <i>Closed</i></li></ul><p>Applied in changeset <a class="changeset" href="https://issues.qgis.org/projects/qgis/repository/revisions/62af54ecb2666d0bde0f7af102d663ded5cda97d" title="[mssql] Use Filter instead of STIntersects to improve query performance ...and refine validity t...">qgis|62af54ecb2666d0bde0f7af102d663ded5cda97d</a>.</p> QGIS Application - Bug report #15752: Degradation of rendering performances in MSSQL providerhttps://issues.qgis.org/issues/15752?journal_id=803092017-06-06T12:39:21ZAndre Jesusandrerenatoj@gmail.com
<ul></ul><p>Giovanni Manghi wrote:</p>
<blockquote>
<p>Dear Andre Jesus would you mind see if the above commit improves things as expected? thanks!</p>
</blockquote>
<p>Sorry I was on vacation.</p>
<p>Are these changes applied to the final Windows build? <br />Comparing the numbers alone I don't see improvements, but in the daily usage I find 2.14.15 LTR to be good enough. <br />On the other hand, 2.18.x is a totally skip.</p>
<p>I compared It using #note-14 because I could use the same layers/scales.</p>
<p>Using QGIS 2.18.9 64 bits, Windows Standalone installer. Projected layers.<br /><img src="http://i.imgur.com/GG5aYsH.png" alt="" /></p>
<p>Using QGIS 2.14.15 LTR 64 bits, Windows Standalone installer. Projected layers.<br /><img src="http://i.imgur.com/rUUy2hU.png" alt="" /></p> QGIS Application - Bug report #15752: Degradation of rendering performances in MSSQL providerhttps://issues.qgis.org/issues/15752?journal_id=803102017-06-06T12:42:40ZGiovanni Manghigiovanni.manghi@gmail.com
<ul></ul><p>you probably have to try qgis master or 2.18.9 nightly or wait the next 2.18 build (but I'm not sure the fix has been backported)</p>
<p>Andre Jesus wrote:</p>
<blockquote>
<p>Giovanni Manghi wrote:</p>
<blockquote>
<p>Dear Andre Jesus would you mind see if the above commit improves things as expected? thanks!</p>
</blockquote>
<p>Sorry I was on vacation.</p>
<p>Are these changes applied to the final Windows build? <br />Comparing the numbers alone I don't see improvements, but in the daily usage I find 2.14.15 LTR to be good enough. <br />On the other hand, 2.18.x is a totally skip.</p>
<p>I compared It using #note-14 because I could use the same layers/scales.</p>
<p>Using QGIS 2.18.9 64 bits, Windows Standalone installer. Projected layers.<br /><img src="http://i.imgur.com/GG5aYsH.png" alt="" /></p>
<p>Using QGIS 2.14.15 LTR 64 bits, Windows Standalone installer. Projected layers.<br /><img src="http://i.imgur.com/rUUy2hU.png" alt="" /></p>
</blockquote> QGIS Application - Bug report #15752: Degradation of rendering performances in MSSQL providerhttps://issues.qgis.org/issues/15752?journal_id=803192017-06-06T19:02:37ZNyall Dawson
<ul></ul><p>These changes aren't in master yet, or any of the released 2.18 versions. Can you please install the qgis-rel-dev version using osgeo4w add test using that?</p> QGIS Application - Bug report #15752: Degradation of rendering performances in MSSQL providerhttps://issues.qgis.org/issues/15752?journal_id=803212017-06-06T21:28:20ZAndre Jesusandrerenatoj@gmail.com
<ul></ul><p>Nyall Dawson wrote:</p>
<blockquote>
<p>These changes aren't in master yet, or any of the released 2.18 versions. Can you please install the qgis-rel-dev version using osgeo4w add test using that?</p>
</blockquote>
<p>Aren't those versions debug-enabled? I remember from another issue the comparison between final and dev build irrelevant as the performance is degraded because of the debug flags.</p>
<p>I installed QGIS-OSGeo4W-2.99.0-23-Setup-x86_64.exe (05-Jun-2017 06:29 463M) from the weekly builds (<a class="external" href="http://qgis.org/downloads/weekly/">http://qgis.org/downloads/weekly/</a>). <br />The results are close or worse than the QGIS 2.18.9 final build times.</p> QGIS Application - Bug report #15752: Degradation of rendering performances in MSSQL providerhttps://issues.qgis.org/issues/15752?journal_id=803242017-06-06T22:19:32ZNyall Dawson
<ul></ul><p>You could compare it against qgis-ltr-dev. That's a debug enabled 2.14 release.</p>
<p>What's the commit number from the about screen in the weekly you are running? I suspect its probably from before the recent changes.</p> QGIS Application - Bug report #15752: Degradation of rendering performances in MSSQL providerhttps://issues.qgis.org/issues/15752?journal_id=803252017-06-06T22:20:50ZNyall Dawson
<ul></ul><p>Actually I just realised you mentioned 2.99. The fixes aren't in master builds yet. You'll need to test qgis-rel-dev from osgeo4w, that's the only pre-built version with these changes available.</p> QGIS Application - Bug report #15752: Degradation of rendering performances in MSSQL providerhttps://issues.qgis.org/issues/15752?journal_id=803322017-06-07T13:00:36ZAndre Jesusandrerenatoj@gmail.com
<ul></ul><p><img src="https://i.imgsafe.org/7f7d806fd5.png" alt="" /></p>
<p>Even with the debug flags qgis-rel-dev was faster than the 2.18 final release. I guess we can expect the same performance between 2.14.x and 2.18.x for now on. Thank you, Nyall!</p> QGIS Application - Bug report #15752: Degradation of rendering performances in MSSQL providerhttps://issues.qgis.org/issues/15752?journal_id=803352017-06-07T19:27:50ZNyall Dawson
<ul></ul><p>I'm confused - did you attach the wrong table above? It looks like it's still slower to me?</p> QGIS Application - Bug report #15752: Degradation of rendering performances in MSSQL providerhttps://issues.qgis.org/issues/15752?journal_id=803362017-06-07T19:49:52ZAndre Jesusandrerenatoj@gmail.com
<ul></ul><p>Nyall Dawson wrote:</p>
<blockquote>
<p>I'm confused - did you attach the wrong table above? It looks like it's still slower to me?</p>
</blockquote>
<p>Comparing 2.18.x vs 2.14.x both dev versions, 2.18 is slower than 2.14.<br />But when comparing 2.18.x dev to 2.18.x final, even with the debug flags the dev is faster than the final version.</p>
<p>I confess I got a little excited there and maybe the 2.18 <strong>still</strong> doesn't have the same performance as 2.14 has, but it's a great improvement, the render time may drop another 30-40% with debug off...</p>
<hr />
<p>Just out of curiosity, I installed QGIS 2.8.9-2 x86 again, which is the fastest version so far, to see how far off the 2.14.x is from it. It's pretty close, 2.8.9 is still the fastest but only by ~8%.</p> QGIS Application - Bug report #15752: Degradation of rendering performances in MSSQL providerhttps://issues.qgis.org/issues/15752?journal_id=803392017-06-07T22:25:56ZNyall Dawson
<ul></ul><p>Ok - let's do some more tests. I'm curious if it's the added IsValid check which is causing these remaining regressions (you can see discussion about why this check is required at <a class="external" href="https://github.com/qgis/QGIS/pull/4642">https://github.com/qgis/QGIS/pull/4642</a>)</p>
<p>Can you run these benchtests directly on your sql server to get the timing for the query on the server alone (no qgis involved):</p>
<p>- select everything from the layers , no where clause<br />- select everything from the layers, with an 'WHERE geom.STIsValid() = 1' clause<br />- select from the layers using a restricted bounding box check (eg something like 'WHERE geom.Filter(Polygon::STGeomFromText('<abbr title="(... ">POLYGON</abbr>)',4326)) = 1', but with the correct crs/etc)<br />- same as above, but with a 'WHERE geom.STIsValid() = 1 AND geom.Filter....etc)</p>
<p>I'd like to see how much of an impact the validity checks are adding on your query times. It would be good to narrow down the regression to the presence of the validity check and not other factors so we can refine further investigation.</p> QGIS Application - Bug report #15752: Degradation of rendering performances in MSSQL providerhttps://issues.qgis.org/issues/15752?journal_id=803422017-06-08T12:46:32ZAndre Jesusandrerenatoj@gmail.com
<ul></ul><p>results: <a class="external" href="https://drive.google.com/open?id=11GY4OxK0Xy2HULLrJ1xB4MuLAZcpF-MP7ADUKLiHBRg">https://drive.google.com/open?id=11GY4OxK0Xy2HULLrJ1xB4MuLAZcpF-MP7ADUKLiHBRg</a></p>
<p>tl;dr</p>
<p>Using IsValid has a huge impact in performance in points and polygons, linestrings also has a big slow down but not as much. <br />What was interesting to see was how big the difference when using both bbox filter and isValid together is. It basically sums bbox filter + isValid cpu times and doubles it!</p>
<p>just another 2 cents: geom.filter is slightly better than the geom.STIntersects it's currently used (<a class="changeset" href="https://issues.qgis.org/projects/qgis/repository/revisions/57dc3c7eff2337411c163222a4e5b7562fffd457" title="[mssql] Fix layer not showing with invalid geometry">57dc3c7eff2337411c163222a4e5b7562fffd457</a>)</p> QGIS Application - Bug report #15752: Degradation of rendering performances in MSSQL providerhttps://issues.qgis.org/issues/15752?journal_id=805482017-06-27T17:42:42ZAndre Jesusandrerenatoj@gmail.com
<ul></ul><p>Updated results:</p>
<p><img src="http://i.imgur.com/xQiIbsw.png" alt="" /></p> QGIS Application - Bug report #15752: Degradation of rendering performances in MSSQL providerhttps://issues.qgis.org/issues/15752?journal_id=815182017-08-10T12:51:54ZAndre Jesusandrerenatoj@gmail.com
<ul></ul><p>Sorry to keep bothering about it, but I don't think it's closed as the current 2.18.x versions are still 6x slower than the 2.14.x versions.</p>
<p>And the 2.18.x becoming the next LTR version it really worries me.</p> QGIS Application - Bug report #15752: Degradation of rendering performances in MSSQL providerhttps://issues.qgis.org/issues/15752?journal_id=815262017-08-10T14:41:35ZGiovanni Manghigiovanni.manghi@gmail.com
<ul><li><strong>Status</strong> changed from <i>Closed</i> to <i>Feedback</i></li></ul><p>Andre Jesus wrote:</p>
<blockquote>
<p>Sorry to keep bothering about it, but I don't think it's closed as the current 2.18.x versions are still 6x slower then the 2.14.x versions.</p>
<p>And the 2.18.x becoming the next LTR version it really worries me.</p>
</blockquote>
<p>I assume that have you tried the latest available 2.18, correct?</p> QGIS Application - Bug report #15752: Degradation of rendering performances in MSSQL providerhttps://issues.qgis.org/issues/15752?journal_id=815272017-08-10T14:57:06ZAndre Jesusandrerenatoj@gmail.com
<ul><li><strong>File</strong> <a href="/attachments/download/11326/2.18.11.jpg">2.18.11.jpg</a> added</li></ul><p>Giovanni Manghi wrote:</p>
<blockquote>
<p>I assume that have you tried the latest available 2.18, correct?</p>
</blockquote>
<p>I keep testing every version for improvements.</p> QGIS Application - Bug report #15752: Degradation of rendering performances in MSSQL providerhttps://issues.qgis.org/issues/15752?journal_id=815422017-08-11T07:27:36ZGiovanni Manghigiovanni.manghi@gmail.com
<ul><li><strong>Affected QGIS version</strong> changed from <i>2.18.4</i> to <i>2.18.11</i></li><li><strong>Status</strong> changed from <i>Feedback</i> to <i>Open</i></li></ul> QGIS Application - Bug report #15752: Degradation of rendering performances in MSSQL providerhttps://issues.qgis.org/issues/15752?journal_id=833072017-10-02T19:31:01ZAndre Jesusandrerenatoj@gmail.com
<ul></ul><p>New results: 2.18.13, 2.14.19, 2.8.9</p>
<p><img src="https://i.imgur.com/TPOq61c.png" alt="" /></p> QGIS Application - Bug report #15752: Degradation of rendering performances in MSSQL providerhttps://issues.qgis.org/issues/15752?journal_id=868222018-02-15T13:41:30ZStijn Van der Lindenstijn.vanderlinden@kb.vlaanderen.be
<ul></ul><p>Hello,</p>
<p>Is this still being looked at? With the upcoming release of QGIS 3.0, the LTR version will become 2.18.x instead of 2.14.x. I just checked 2.18.16 versus 2.14.22 and the problem persists. Loading a MSSQL layer in 2.18.16 takes several minutes compared to the same layer in 2.14.22, which is loaded in a matter of seconds.</p>
<p>This makes 2.18.16 unusable unless I don't use SQL sources (which is notreally an option).</p> QGIS Application - Bug report #15752: Degradation of rendering performances in MSSQL providerhttps://issues.qgis.org/issues/15752?journal_id=882672018-03-04T10:11:20ZGiovanni Manghigiovanni.manghi@gmail.com
<ul><li><strong>Affected QGIS version</strong> changed from <i>2.18.11</i> to <i>2.18.16</i></li></ul> QGIS Application - Bug report #15752: Degradation of rendering performances in MSSQL providerhttps://issues.qgis.org/issues/15752?journal_id=882862018-03-05T01:34:17ZNyall Dawson
<ul></ul><p>I've looked into this issue in depth and there's no easy answers here.</p>
<p>We have to choose between:</p>
<p>1. Full performance - yet undefined behavior whenever a layer contains invalid geometries. (Undefined behavior here will mean missing features from the mssql table in the QGIS layer. Not just those with invalid geometries, but any feature present AFTER an invalid geometry is encountered will also be discarded.)</p>
<p>or</p>
<p>2. Slower performance - yet handling invalid geometries correctly and ensuring that all features from the table are present in the QGIS layer.</p>
<p>There's no alternative... save from begging MS to add support for disabling the automatic geometry validity check!</p>
<p>So what I propose is adding a new checkbox to the MS SQL connection properties dialog - "No invalid geometry handling". If checked, you're telling QGIS that you accept all the risks and want to disable the invalid geometry handling. You'll be missing features if your tables have invalid geometries. If unchecked (default), you tell QGIS to keep running the slower code which correctly handles invalid geometries in the table.</p> QGIS Application - Bug report #15752: Degradation of rendering performances in MSSQL providerhttps://issues.qgis.org/issues/15752?journal_id=883032018-03-05T09:29:43ZStijn Van der Lindenstijn.vanderlinden@kb.vlaanderen.be
<ul></ul><p>Hi,</p>
<p>I think that would be a very good solution. The SQL geometry databases I use are very well maintained and have a quality control that insures a very low chance of invalid geometries.</p>
<p>Looking forward to this!</p> QGIS Application - Bug report #15752: Degradation of rendering performances in MSSQL providerhttps://issues.qgis.org/issues/15752?journal_id=883062018-03-05T11:09:03ZAndre Jesusandrerenatoj@gmail.com
<ul></ul><p>Nyall Dawson wrote:</p>
<blockquote>
<p>1. Full performance - yet undefined behavior whenever a layer contains invalid geometries. (Undefined behavior here will mean missing features from the mssql table in the QGIS layer. <strong>Not just those with invalid geometries, but any feature present AFTER an invalid geometry is encountered will also be discarded</strong>.)</p>
</blockquote>
<p>I saw this happened more than once in previous 2.x version. It would not draw any geometry if there was one invalid geometry in the bbox.<br />At least It would let me pin point the nearby location where that invalid geometry is if a validation were not enough to detect it.</p>
<p>Nyall Dawson wrote:</p>
<blockquote>
<p>So what I propose is adding a new checkbox to the MS SQL connection properties dialog - "No invalid geometry handling". If checked, you're telling QGIS that you accept all the risks and want to disable the invalid geometry handling. You'll be missing features if your tables have invalid geometries. If unchecked (default), you tell QGIS to keep running the slower code which correctly handles invalid geometries in the table.</p>
</blockquote>
<p>That would be best approach. There is no beginners in QGIS that start using QGIS with a full relational database as data source.</p>
<p>Maybe the validation could occur only when the feature is added or before saving new/modified geometry, not every time a select is made.</p>
<p>Yet, the checkbox is the simpler and fastest solution.</p>
<p>by the way, I could not wait for a solution so i migrated by data to Postgresql/Postgis</p> QGIS Application - Bug report #15752: Degradation of rendering performances in MSSQL providerhttps://issues.qgis.org/issues/15752?journal_id=937402018-10-02T20:18:36ZNyall Dawson
<ul><li><strong>Category</strong> changed from <i>Map Canvas</i> to <i>Data Provider/MSSQL</i></li></ul> QGIS Application - Bug report #15752: Degradation of rendering performances in MSSQL providerhttps://issues.qgis.org/issues/15752?journal_id=940002018-10-08T04:31:24ZNyall Dawson
<ul><li><strong>Assignee</strong> set to <i>Nyall Dawson</i></li><li><strong>Status</strong> changed from <i>Open</i> to <i>In Progress</i></li></ul> QGIS Application - Bug report #15752: Degradation of rendering performances in MSSQL providerhttps://issues.qgis.org/issues/15752?journal_id=940042018-10-08T06:14:54ZNyall Dawson
<ul><li><strong>Status</strong> changed from <i>In Progress</i> to <i>Closed</i></li></ul><p>Applied in changeset <a class="changeset" href="https://issues.qgis.org/projects/qgis/repository/revisions/dafeaf4372b9e029160438345cce852dbfcf0741" title="[mssql][needs-docs] Add connection setting to ignore invalid geometry handling Sets whether the ...">qgis|dafeaf4372b9e029160438345cce852dbfcf0741</a>.</p>