SQL Server

This page covers advanced troubleshooting and optimisation techniques for QGIS projects that use Microsoft SQL Server as a data source.

Slow QGIS Project or Catalogue Loading

If a QGIS project backed by SQL Server is taking a long time to load, QGIS may be spending significant time inspecting layer metadata, calculating layer extents, and validating geometry before the project is fully available to Pozi.

For large SQL Server-backed projects, one possible optimisation is to maintain the SQL Server geometry_columns metadata table and configure the QGIS connection to use that metadata instead of recalculating it during layer discovery.

What it can help with

This approach can reduce some of the overhead involved when QGIS loads SQL Server layers, including:

• extent lookup
• geometry metadata lookup
• optionally, primary key lookup if the metadata is maintained

Depending on the QGIS connection settings used, it may also reduce other checks performed when layers are added or opened.

QGIS connection options involved

When editing a SQL Server connection in QGIS, the relevant options are:

• Only look in the geometry_columns metadata table
• Use layer extent from geometry_columns table
• optionally, Use primary key from geometry_columns table

These settings tell QGIS to rely more heavily on the maintained SQL Server metadata instead of deriving the same information dynamically.

When to consider this

This technique is most relevant when:

• the project uses SQL Server for many or most layers
• the layers are already spatially indexed and generally well configured
• the project still loads slowly after normal optimisation
• catalogue loading or project updates are timing out or taking an unusually long time

Before using this approach, first review the existing guidance on:

• Project Configuration
• Layer Performance
• Publish as Vector Layer

Risks and caveats

This optimisation introduces some manual maintenance and should be used carefully.

• Incorrect geometry_columns entries can cause layers to appear incorrectly or fail to appear in QGIS.
• The f_geometry_column value must match the actual geometry field name exactly.
• Stale or overly broad extents can produce confusing map behaviour.
• Primary key metadata should only be used if it is maintained accurately.
• Skipping invalid geometry handling should be approached cautiously. Invalid geometries can still prevent layers from displaying correctly in QGIS or Pozi.

If you are unsure whether this optimisation is appropriate for your environment, test it first on a copy of the SQL Server connection and a small number of problematic layers.

Implementation guidance

This Admin Guide does not prescribe a single SQL script or database workflow for maintaining geometry_columns.

For implementation details, background information, and examples, see:

• QGIS: Connecting to MS SQL Server
• QGIS Not Showing SQL Geometry
• QGIS geometry_columns and SQL Server spatial
• Pozi Community: Optimising a QGIS Project (available to Pozi Community members only)

Work with your database administrator or Pozi support provider if you need help deciding whether this optimisation is suitable for a particular SQL Server-backed project.