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
_______________________________________________ Qgis-user mailing list Qgis-user@lists.osgeo.org List info: https://lists.osgeo.org/mailman/listinfo/qgis-user Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user