Using QGIS, rendering from a view takes significantly longer than rendering the same data from the source table. The view is just a select all from source table. The database is SQL Server 2012.
-
The first thing I would try is determine what the queries are that QGIS is sending to SQL Server for each of the view and table. This can be done using the profiler tool in SQL Server. Then you can take QGIS out of the equation and determine what the database for the queries and why they differ.– MickyTCommented Sep 28, 2016 at 22:27
Add a comment
|
2 Answers
I believe that the underlying query (used to make the view) will be executed every time you try to access the view, where if you are just using the source table the select will much simpler.
This previous answer and the others on the thread support my reasoning: https://stackoverflow.com/a/7755497/5982697
I know there is a "materialized view" option that I have used in postgres, which basically creates a new table from the query that defines the view. If there is a similar option in SQL server 2012, maybe that would fast enough. However, you will have to refresh the materialized view when rows change in the source table.
https://www.postgresql.org/docs/9.5/static/sql-creatematerializedview.html
-
1Yes, Views are merely 'saved queries' that look like tables, but whenever they are queried, will run the underlying SQL each time - so the more data / logic the SQL behind the view has - the longer it will take to render. But it sounds like your view is hitting a linked server or something similar? Commented Sep 28, 2016 at 15:08
-
If the view is simply "create view as select * from table" it is just the same to make queries from the table or from this simple view. I agree with @Jay Cummins when he thinks that the difference is in how QGIS gets the metadata. Commented Sep 28, 2016 at 18:13
-
For the postgres spatial views, after QGIS executes the initial distinct count query, then spatial view is really fast after that. If you are experiencing slowness after the initial load, then its something different than my answer. Commented Sep 29, 2016 at 14:03
I've noticed the same thing on spatial views that return a large amount of data in PostgreSql and it is caused by QGIS issuing a SELECT count(distinct (id))=count((id)) FROM schema.view to verify the key that is defined for the layer is unique. I assume for an actual table, it checks for the database constraint, trusts it, and moves on.
Same thing probably is probably occurring in SQL Server--you could verify it with SQL Server Profiler.
answered Sep 28, 2016 at 14:48
