[Qgis-user] Performance issue with SQLServer data source

[Marco Casalboni]

Hi all,
I have an issue with my configuration in QGIS using MSSQL data source.
I want to serve a certain number of MSSQL layers, but the performance is a
bit unstable, maybe because my configuration is bad.

I use qgis server 3.18 on debian 10.

First of all, after some research on google, I enabled  the "Skip invalid
geometry handling" option, and that already meant a lot.
I also tried to enable and disable the "Use estimated table parameters"
option, but I don't see any difference in my SQL Server Profiler trace.

Talking about SQL Server Profiler trace, I notice that every now and then,
each layer performs 4 queries:
- one to read the computed column names: SELECT name FROM sys.columns WHERE
is_computed = 1 AND object_id = OBJECT_ID(<xxx>)
- one to read the column metadata: exec sp_columns @table_name = N'<xxxx>',
@table_owner = '<yyyyy>'
- one to read the primary keys: exec sp_pkeys @table_name = N'<xxxx>',
@table_owner = '<yyyy>'
- one to read the spatial extension: SELECT min(bounding_box_xmin),
min(bounding_box_ymin), max(bounding_box_xmax), max(bounding_box_ymax) FROM
sys.spatial_index_tessellations WHERE object_id = OBJECT_ID('<xxxx>').

Now, these operations may have a low cost, but I am wondering why the
system must execute them every now and then.
In my case there's no need to read the primary key, I will not want to
perform any write operation (I guess the PK is for that). I don't have any
computed columns, so I guess this query is avoidable.
And for the spatial extension, in my case it may be better if I could
manually set the bounding box once for every layer in my configuration.

Although I think it is possible to avoid some repeated queries, the
performance is quite stable until the layer data is a table. If the layer
data is a view, then the performance is quite critical, and for each
layers, the number of executed queries is 7:
- one to read the computed column names (as before)
- one to read the column metadata (as before)
- one to read the column metadata (as before)
- one to determine if the primary key is unique: select count(distinct
[<primarykey>]), count([< primarykey >]) from <xxxx>.
- one to read the spatial extension (as before)
now, because it's a view, the last query returns no data, and the loss of
performance is in the next 2 queries, which performs a full table(view)
scan to determine the bounding box (using STPointN function) and the
feature count, the first one with a fixed clause (WHERE
(ABS(CAST((BINARY_CHECKSUM([[<primary key>]])) as int)) % 100) = 42) and
the second one without any clause.

In this configuration the performance is pretty bad, because the system
wastes time querying the same data over and over.
Obviously, in my production environment I am forced to use the views.

The question is: is there a way to set these informations once, even
manually, for each layer? Or maybe I am missing/mistaking some
configurations?

kindly regards,
Marco
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/qgis-user/attachments/20210621/f79ec301/attachment.html>