Results 1 to 8 of 8
  1. #1
    Join Date
    May 2003
    Location
    California Coast
    Posts
    72

    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.

    Scratching head now…

    Mike

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    What do you mean by access path? Do you mean the query optimizer execution plan?

    Also, if your tables have only 8500 and 150 rows respectively, how can you tell that one method runs consistently faster than the other? They both ought to run lickety split!

    Also, you may not be able to index your Union view, but that doesn't mean the optimizer can't use underlying table indexes when it references the view.

    blindman

  3. #3
    Join Date
    May 2003
    Location
    California Coast
    Posts
    72
    the access path = executionplan.

    View1 does use some indexes - as I mentioned some base tables do not have indexes (area to improve)

    We are using a tool called jprobe which gives detail on milisecond run time etc. A few milliseconds is not a concern but 3x increase is not acceptable.

    The question is why would a view (without 1 key index) execute faster than a table with a clustered index on the 2 values in the where clause.

  4. #4
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    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?

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    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.

    blindman

  6. #6
    Join Date
    May 2003
    Location
    California Coast
    Posts
    72
    the execution plan is for the table option is cluster index seek only (where clause is clo1='value'). Pretty straight forward.

    The execution plan for the view option is table scan & index reads with a concatenation and sort....

    Any ideas on why a view beats a table?

    Mike

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    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.

    blindman

  8. #8
    Join Date
    Oct 2001
    Location
    England
    Posts
    426
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •