Unanswered: performandce of view vs table is not as expecte
I have a view (view1) which unions 2 tables. Due to the union the view cannot be indexed. In an attempt to improve performance I created a table (Table1) which is actually built from select into from view1. Table1 has 2 indexes, 1 cluster, 1 non cluster. There area 2 main queries which hit table1. Query1 is on the 2 columns in the cluster index and query2 is against the 1 column for the non cluster index. The access path for the queries running against table1 is thousands times faster than running against view1.
Now here’s the actual issue. When I had the developers use the table1 versus view1, view1 was 3 times FASTER than table1. I have verified datatypes and lengths etc. so there is no implicit conversions occurring. I checked the access path on the test server and it is definitely using the table1 and the supporting indexes. Does anyone have an idea as to the reason the view (which is actually missing some indexes on the base tables) is running faster than the table with the cluster index?
Additional facts: the base metadata tables are updated on a quarterly basis and the summary table will be rebuilt as part of that process.
The two metadata tables have 8500 and 150 rows.
Try running profiler against the queries the developers are running. You might be able to pinpoint the bottleneck (and as an added benefit, you can test their exact statements). Maybe they are querying ranges on the indexes that are too big?
Your execution plan should indicate where the bottlenecks are. Its possible that in one circumstance the optimizer is using a cached query plan, or even cached data pages, while in the other it is not. I would not look for consistent execution times from SQL Server. The optimizer can choose different strategies as tables grow or index cardinality changes.
I don't know what your actual execution times were. Milliseconds, 1 second, five seconds?
If the execution times are pretty quick it could be the difference between using a precompiled view as opposed to a free sql statement where the optimizer would need some (admittedly short) time to devise an execution strategy.
Can think of a number of things that could cause this.
A few things to think about
What sort of times are you talking about here.
What are the actual queries that are being run.
Calling stored procs or embedded sql
What is jprobe measuring.
How reliable/accurate is it.