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?
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.
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.
Thanks for your response. In our case the states had grown quite numerous so getting those back down surely helped as you suggested. Also I found that we were running out of disk space becasue a maintenance plan wasn't deleting .bak files correctly.
