QGIS » QGIS Application

What renderer/symbol style are you using?

What renderer/symbol style are you using?
I'm using default QGIS style.

The same occurs using WFS:

ly1 WFS/linestring

2.8.x = 16 ms
2.14.x+ = 517 ms

ly2 WFS/polygon

2.8.x = 17 ms
2.14.x+ = 219 ms

ly3 WFS/point

2.8.x = 15 ms
2.14.x+ = 647 ms

Always using the same bookmark and data.

I have 20 other machines with the same results.

Sorry, I don't know if my answer was clear. I'm using default Single Symbol style without any change.

Hmm... that's quite odd. You should definitely see an improvement in 2.18.1.

Are you using labels in these tests?

The last time I tested It I did a dirt install.
To cross It out I set up a new VM with Windows 7 32 bits, made a snapshot before installing any software.
I didn't tweaked any setting in QGIS but enable Debug: Map Canvas Refresh

Here the new results:

Yes, I was using simple style without labels or any change to default QGIS style.

Latest update: Using 2.18.4

Render time seems to keep rising every update.

Every relatively big layer I have now became a slug with version 2.18.4.

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)

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

As shown above, PostgreSQl had a 94% render time reduction and MSSQL only 31%, that considering a 99% features displayed reduction.

I did some more testing...

Using the same map, layers and zoom levels.

Feature list and count:

Zoom: 1:400.000

Zoom: 1:7.000

Wider zoom levels:
2.8.9 > 2.14.12 = + 41% render time
2.14.12 > 2.18.4 = + 290% render time

Smaller zoom levels:
2.8.9 > 2.14.12 = + 7% render time
2.14.12 > 2.18.4 = + 4604% render time

I know MSSQL data provider is not the focus in development but it's impossible to work with It in 2.18.4.

I know MSSQL data provider is not the focus in development but it's impossible to work with It in 2.18.4.

please raise this matter on the users and/or developers mailing lists.

Giovanni Manghi wrote:

I know MSSQL data provider is not the focus in development but it's impossible to work with It in 2.18.4.

please raise this matter on the users and/or developers mailing lists.

Ok.

Each one would be more suitable: User or Developers mailing list? I've never joined one before.
I'm no developer but I'd like to contribute as I can.

Andre Jesus wrote:

Giovanni Manghi wrote:

I know MSSQL data provider is not the focus in development but it's impossible to work with It in 2.18.4.

please raise this matter on the users and/or developers mailing lists.

Ok.

Each one would be more suitable: User or Developers mailing list? I've never joined one before.
I'm no developer but I'd like to contribute as I can.

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.

Andre Jesus wrote:

Giovanni Manghi wrote:

I know MSSQL data provider is not the focus in development but it's impossible to work with It in 2.18.4.

please raise this matter on the users and/or developers mailing lists.

Ok.

Each one would be more suitable: User or Developers mailing list? I've never joined one before.
I'm no developer but I'd like to contribute as I can.

One question: have you any idea if this also affects other providers, like PostGIS?

Giovanni Manghi wrote:

One question: have you any idea if this also affects other providers, like PostGIS?

It doesn't.

Running the same bench test using Shapefiles or PostGIS both gives me ~1500 ms

Just checking how performance went with other data providers over the 3 versions:

Using the same map but with a different zoom level (1:177.000)

full printscreens

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.

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.

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.

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.

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.

It's not related how QGIS request data from the database as all version uses the same intersects method with the same limit.
And that kills my theory he may be request all geometries all the time.

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

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.

Again a 3 times avg:

It could be a good lead to find out what went wrong, but my knowledge ends here.

Update versions: 2.14.13 and 2.18.5

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:

2.14.13
3500 ms mssql
1500 ms postgis

2.18.5
19443 ms mssql
1400 ms postgis

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)

Edit: 2.14.13 feels faster now

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).

I think the performance degradation does impact all providers, just not as much as It impacts MSSQL (see note #15752-20).

Update: 2.14.14 LTS / 2.18.7

Using the same layers used to benchmark on note 20.
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.
I had an OS problem and I lost 2.8.9 installer, that's why there is no results for it.

MSSQL performance is still terrible, but PostGIS had a nice improvement.

see also: https://lists.osgeo.org/pipermail/qgis-psc/2017-May/005258.html

Nyall Dawson wrote:

Applied in changeset qgis|5798a82c8011ea7f44a1ed1d55ef0719786e8056.

Dear Andre Jesus would you mind see if the above commit improves things as expected? thanks!

Giovanni Manghi wrote:

Nyall Dawson wrote:

Applied in changeset qgis|5798a82c8011ea7f44a1ed1d55ef0719786e8056.

Dear Andre Jesus would you mind see if the above commit improves things as expected? thanks!

see also https://drive.google.com/file/d/0B8VD1V_leaeWNmpaOFNxZGxicVU/view?usp=sharing

reopened as requested here

https://lists.osgeo.org/pipermail/qgis-psc/2017-May/005319.html

Giovanni Manghi wrote:

Dear Andre Jesus would you mind see if the above commit improves things as expected? thanks!

Sorry I was on vacation.

Are these changes applied to the final Windows build?
Comparing the numbers alone I don't see improvements, but in the daily usage I find 2.14.15 LTR to be good enough.
On the other hand, 2.18.x is a totally skip.

I compared It using #note-14 because I could use the same layers/scales.

Using QGIS 2.18.9 64 bits, Windows Standalone installer. Projected layers.

Using QGIS 2.14.15 LTR 64 bits, Windows Standalone installer. Projected layers.

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)

Andre Jesus wrote:

Giovanni Manghi wrote:

Dear Andre Jesus would you mind see if the above commit improves things as expected? thanks!

Sorry I was on vacation.

Are these changes applied to the final Windows build?
Comparing the numbers alone I don't see improvements, but in the daily usage I find 2.14.15 LTR to be good enough.
On the other hand, 2.18.x is a totally skip.

I compared It using #note-14 because I could use the same layers/scales.

Using QGIS 2.18.9 64 bits, Windows Standalone installer. Projected layers.

Using QGIS 2.14.15 LTR 64 bits, Windows Standalone installer. Projected layers.

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?

Nyall Dawson wrote:

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?

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.

I installed QGIS-OSGeo4W-2.99.0-23-Setup-x86_64.exe (05-Jun-2017 06:29 463M) from the weekly builds (http://qgis.org/downloads/weekly/).
The results are close or worse than the QGIS 2.18.9 final build times.

You could compare it against qgis-ltr-dev. That's a debug enabled 2.14 release.

What's the commit number from the about screen in the weekly you are running? I suspect its probably from before the recent changes.

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.

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!

I'm confused - did you attach the wrong table above? It looks like it's still slower to me?

Nyall Dawson wrote:

I'm confused - did you attach the wrong table above? It looks like it's still slower to me?

Comparing 2.18.x vs 2.14.x both dev versions, 2.18 is slower than 2.14.
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.

I confess I got a little excited there and maybe the 2.18 still 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...

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%.

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 https://github.com/qgis/QGIS/pull/4642)

Can you run these benchtests directly on your sql server to get the timing for the query on the server alone (no qgis involved):

- select everything from the layers , no where clause
- select everything from the layers, with an 'WHERE geom.STIsValid() = 1' clause
- select from the layers using a restricted bounding box check (eg something like 'WHERE geom.Filter(Polygon::STGeomFromText('POLYGON)',4326)) = 1', but with the correct crs/etc)
- same as above, but with a 'WHERE geom.STIsValid() = 1 AND geom.Filter....etc)

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.

results: https://drive.google.com/open?id=11GY4OxK0Xy2HULLrJ1xB4MuLAZcpF-MP7ADUKLiHBRg

tl;dr

Using IsValid has a huge impact in performance in points and polygons, linestrings also has a big slow down but not as much.
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!

just another 2 cents: geom.filter is slightly better than the geom.STIntersects it's currently used (57dc3c7eff2337411c163222a4e5b7562fffd457)

Updated results:

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.

And the 2.18.x becoming the next LTR version it really worries me.

New results: 2.18.13, 2.14.19, 2.8.9

Hello,

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.

This makes 2.18.16 unusable unless I don't use SQL sources (which is notreally an option).

I've looked into this issue in depth and there's no easy answers here.

We have to choose between:

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.)

or

2. Slower performance - yet handling invalid geometries correctly and ensuring that all features from the table are present in the QGIS layer.

There's no alternative... save from begging MS to add support for disabling the automatic geometry validity check!

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.

Hi,

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.

Looking forward to this!

Nyall Dawson wrote:

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.)

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.
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.

Nyall Dawson wrote:

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.

That would be best approach. There is no beginners in QGIS that start using QGIS with a full relational database as data source.

Maybe the validation could occur only when the feature is added or before saving new/modified geometry, not every time a select is made.

Yet, the checkbox is the simpler and fastest solution.

by the way, I could not wait for a solution so i migrated by data to Postgresql/Postgis