I have just been through trying to optimise the speed of connection between SQL Server and QGIS and I have found two ways of speeding up the connection.
1) Create a geometry_columns table for the database which speeds up reading in the tables to the selection screen
2) Create spatial indices on individual tables which vastly increases the speed of draw of layers within QGIS
1) Create geometry_columns table on the database
When connecting SQL Server to QGIS if you don’t implement a geometry_columns table QGIS will slow down as it scans each table within the SQL Server connection to find the type (point polygon etc) and the SRID projection.
TSQL to create the geometry_columns table
CREATE TABLE [dbo].[geometry_columns](
[f_table_catalog] [varchar](128) NOT NULL,
[f_table_schema] [varchar](128) NOT NULL,
[f_table_name] [varchar](256) NOT NULL,
[f_geometry_column] [varchar](256) NOT NULL,
[coord_dimension] [int] NOT NULL,
[srid] [int] NOT NULL,
[geometry_type] [varchar](30) NOT NULL,
CONSTRAINT [geometry_columns_pk] PRIMARY KEY CLUSTERED
next for each table you will need to fill our the geometry_columns table as follows;
Here’s an example where I have a database called Corporate Address Gazeteer within which I have a table of planning applications (T001). Within that table the geometry is held in the Coordinates field
All tables within the connection (probably one database) will need to be recorded in this table. Provided the ‘Only look in the geometry_columns metadata table’ option is saved with the connection when connecting to a saved connection QGIS will only show those layers listed in the geometry_columns table. Below is the check box where QGIS is told only to look in the geometry_columns table (note server and database names are imaginary)
2) Next speed up the draw on screen of SQL Server layers by creating Spatial Indices on each of the tables and ensuring that the database as a whole has a ‘spatial_ref_sys’ table.
TSQL to create ‘spatial_ref_sys’ table
CREATE TABLE [dbo].[spatial_ref_sys](
[srid] [int] NOT NULL,
[auth_name] [varchar](256) NULL,
[auth_srid] [int] NULL,
[srtext] [varchar](2048) NULL,
[proj4text] [varchar](2048) NULL
) ON [PRIMARY]
Next run the following TSQL on the ‘spatial_ref_sys’ table
INSERT into spatial_ref_sys (srid, auth_name, auth_srid, proj4text, srtext) values ( 27700, 'epsg', 27700, '+proj=tmerc +lat_0=49 +lon_0=-2 +k=0.9996012717 +x_0=400000 +y_0=-100000 +ellps=airy +datum=OSGB36 +units=m +no_defs ', 'PROJCS["OSGB 1936 / British National Grid",GEOGCS["OSGB 1936",DATUM["OSGB_1936",SPHEROID["Airy 1830",6377563.396,299.3249646,AUTHORITY["EPSG","7001"]],AUTHORITY["EPSG","6277"]],PRIMEM["Greenwich",0,AUTHORITY["EPSG","8901"]],
This was obtained from EPSG link below – swap out your SRID if you are not from the UK
(Warning : Just be careful with pure cut and paste of above HTML. The code tag in wordpress is struggling to cope with all the brackets and commas and formatting is a bit off here best to cut and paste from below link)
The great thing about running this is that you can place the projection in the sys table and users will not then need to select a new projection everytime they add a new layer an otherwise repetitive task. For the UK we only require one record within this table 27700 – if you have multiple projections you will need an entry for each individual projection.
I have slightly altered the reference obtained from the above link as it had placed a 9 infront of the first SRID reference – deleting it stopped the projection appearing for each user I have changed it in the above TSQL so it is correct but the graphic below still shows the 9 in front of the 27700.
For each table we need to create spatial indices.
Creating a spatial index on a table.
Go to the table in question and expand the table structure so that you can see the index on the table right click on index and select – New Index
You should be presented with the following screen
It should be noted that you choose Spatial for the type of index and in order for you to be allowed to do this the table should already have a clustered index field (in the picture example this is already in existance) and you will need to know the geometry column of the table in question. You give the spatial index and attach it to the geometry field in the table. In the graphic this is Coordinates
Next you need to give bounding box to the spatial index. The below figures are those used for West Lothian in this case
min x = 285380
min y = 651170
max x = 314890
max y = 680340
You can now press OK and the spatial index should be added to the table
If you have done it right when you expand the index field it should look something like this