I created a versioned view for a feature class with about 60,000 features and am querying the view with a simple select statement in MSSMS. The query to select 1 indexed field takes about 5 minutes to complete. We do a low volume of editing on the database, and have a nightly automated job to do a compress, rebuild indexes, and analyze datasets. Any ideas on how to improve the performance?
SELECT apn FROM gis.PARCEL_VW
crafty762 Jul 4, 2013 4:08 PM
Correct Answer
I assume you are referring to a MULTIversioned view. Your poor performance could possibly be related to multiple issues. First, are the join fields on each of the tables which participate in the view indexed? Secondly, check the physicaly size of your SDE.STATE_LINEAGES table. Is it really big with only a few rows after a compress? I am not sure how SQL Server manages its space on disk, but it may be that the lineages table has expanded to be quite large over time on disk and the time it takes to traverse it during a query may be costly. Take your database as close to state 0 as possible, get everyone out the database, export the table data, drop the table, re-create it using SQL and then re-import its records from the export. This should create the table only as big as it needs at that moment. I had success doing this in Oracle when seeing horrible performance.
See the reply in context