I'm trying to get a SQL Server 2008 R2 spatial database up and running to streamline the production of maps using QGIS.
Currently I just have a handful of spatial tables (a postcode level point table and a few polygon tables for various boundary levels).
My initial plan was to use these to create views (primarily point in polygon counts based on UK postcodes) which I could then pull into QGIS to make the data processing side of map production a lot faster and less fiddly.
Unfortunately my plan has been scuppered somewhat by the amount of time it takes just to connect to my SQL Server using the MSSQL layer tool. QGIS seems to process each table/view prior to allowing me to select any of them. Not only is this extremely slow, it also means that each time I add a new view or table to my database, the whole process is going to get slower and slower until it becomes unusable.
Does anyone have any pointers on how I might be able to speed the QGIS-SQL Server connection process up?
I am beginning to think that saving views is a bad idea and that I should maybe use 'select ... into...' scripts to generate tables and minimise the processing required. I'd be interested in peoples' views on this.
geometry_columnsmetadata table in your database? If you can set one of these up and check the box in the 'Create a New MSSQL connection' dialog to only look there for geometry, then that should prevent the delay you encounter as the whole database is checked for geometry every time you connect to it. (I think, anyway. I've been puzzling over this myself).