QGIS » QGIS Application

Description: ============
Opening a .QGS which has a layer from a MS SQL Server view, takes drastically more startup
time than when the layer is connection directly with the underlying table/featureclass.
The difference in my testcase from ~1 sec. to ~157 sec. (see below),
and occurs at everyone QGIS start up!
 
Testcase & side conditions: ===========================
I did some evaluation and testing with the SQL Server Profiler and a
table/featureclass with ~ 1.7 millions rows/objects.
 
Here are the results of the profiling:
 
1. SQL Trace from starting qgis with the underlying table/featureclass its.dkm_gst => execution time ~1sec.
 
-----------------------------------------------------------------------------------------------
exec sp_datatype_info 11
go
SET QUOTED_IDENTIFIER ON
go
SELECT name FROM sys.columns WHERE is_computed = 1 AND object_id = OBJECT_ID('[its].[DKM_GST]')
go
exec sp_columns @table_name = N'DKM_GST', @table_owner = 'its'
go
exec sp_pkeys @table_name = N'DKM_GST', @table_owner = 'its'
go
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('[its].[DKM_GST]')
go
SET QUOTED_IDENTIFIER ON
go
SELECT [OBJECTID],[SHAPE]FROM [its].[DKM_GST] where [SHAPE].STIsValid() = 1 AND [SHAPE].STIntersects([geometry]::STGeomFromText('POLYGON)',31255)) = 1
go
SET QUOTED_IDENTIFIER ON
go
SELECT [OBJECTID],[SHAPE]FROM [its].[DKM_GST] where [SHAPE].STIsValid() = 1 AND [SHAPE].STIntersects([geometry]::STGeomFromText('POLYGON)',31255)) = 1
go
-----------------------------------------------------------------------------------------------
 
2. SQL trace from starting qgis with the corresponding flat view its.v_dkm_gst (=> select * from [its].[DKM_GST]) => execution time ~157 sec.
 
-----------------------------------------------------------------------------------------------
exec sp_datatype_info 11
go
SET QUOTED_IDENTIFIER ON
go
SELECT name FROM sys.columns WHERE is_computed = 1 AND object_id = OBJECT_ID('[its].[v_DKM_GST]')
go
exec sp_columns @table_name = N'v_DKM_GST', @table_owner = 'its'
go
exec sp_pkeys @table_name = N'v_DKM_GST', @table_owner = 'its'
go
select count(distinct [OBJECTID]), count([OBJECTID]) from [its].[v_DKM_GST]
go
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('[its].[v_DKM_GST]')
go
select min([SHAPE].MakeValid().STPointN(1).STX), min([SHAPE].MakeValid().STPointN(1).STY), max([SHAPE].MakeValid().STPointN(1).STX), max([SHAPE].MakeValid().STPointN(1).STY) from [its].[v_DKM_GST]
go
SET QUOTED_IDENTIFIER ON
go
SELECT [OBJECTID],[SHAPE]FROM [its].[v_DKM_GST] where [SHAPE].STIsValid() = 1 AND [SHAPE].STIntersects([geometry]::STGeomFromText('POLYGON)',31255)) = 1
go
SET QUOTED_IDENTIFIER ON
go
SELECT [OBJECTID],[SHAPE]FROM [its].[v_DKM_GST] where [SHAPE].STIsValid() = 1 AND [SHAPE].STIntersects([geometry]::STGeomFromText('POLYGON)',31255)) = 1
go
-----------------------------------------------------------------------------------------------
 
Reason: =======

=> Detecting the bounding box of the layer! takes ~156 sec. (1.700.000 rows!!!)
 
select min([SHAPE].MakeValid().STPointN(1).STX), min([SHAPE].MakeValid().STPointN(1).STY), max([SHAPE].MakeValid().STPointN(1).STX), max([SHAPE].MakeValid().STPointN(1).STY) from [its].[v_DKM_GST]
go
 
In case of the layer directly connected to the table the query
 
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('[its].[DKM_GST]')
 
returns the bounding box via the SQL Server Metatable,
 
In case of the view
 
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('[its].[v_DKM_GST]')
go
 
returns Null (of course is not physical table)!
 
Solution Proposals: ===================
Extending the qgis metatable "dbo.geometry_columns" with 4 bounding box fields and also a field for the primary key,
than the detection of the bounding box and the primary key field goes to the responsibility of the project/db-administration!?!
 
Manually inserting into the "sys.spatial_index_tessellations" with a dummy record doesn't work, because
it's forbidden => Ad hoc updates to system catalogs are not allowed!
 
After the initial startup, any further action (Zooming, Panning, etc.) works with the same excellent performance as with the underlying table!
 
Hopefully I could describe the issue sharply, thanks in Advance!
Alex
 
PS: I assigned the priority to high, because under enterprise conditions often you have
no direct access to the underlying tables and of course it occurs on each QGIS starting!
I didn't test it under QGIS Server conditions.