SQL Server Performance

Views, unions, table scans and seeks

Discussion in 'T-SQL Performance Tuning for Developers' started by Jazz, Jun 10, 2003.

  1. Jazz New Member

    I'm having difficulty making a set of non-performant(and hence useless) queries into a set of performing (and gleaming) queries. My problem is that the difference between those queries which perform and those which don't, on face of it, seem small.
    I have a query such as:

    select *
    from ctcvw_corr_summary_archive inner join complainant_reviews
    on (ctcvw_corr_summary_archive.case_ref = complainant_reviews.case_ref)
    where complainant_reviews.case_ref='77513'

    Returns (15) rows instantly (hooray). However a query such as:

    select *
    from ctcvw_corr_summary_archive inner join complainant_reviews
    on (ctcvw_corr_summary_archive.case_ref = complainant_reviews.case_ref)
    where complainant_reviews.comp_key=29

    Returns the same data but takes a disasterous 20 secs (booooo).

    The execution plan of the first query shows that an index seek takes place on each of the underlying tables of the view (ctcvw_corr_summary_archive) before the results are merged.

    However, the execution plan of the latter query shows that each underlying table of the view (ctcvw_corr_summary_archive) is scanned then merged before a bookmark lookup applied.

    I don't really understand why there is such a great difference or what to check for next.

    I have tried index hints such as:

    select *
    from
    ctcvw_corr_summary_archive(index(case_ref)) inner join complainant_reviews
    on (ctcvw_corr_summary_archive.case_ref = complainant_reviews.case_ref)
    where complainant_reviews.case_ref='77513'

    However the query analyser simply states:'Warning: Index hints supplied for view 'ctcvw_corr_summary_archive' will be ignored.'

    any help will be greatly appreciated

  2. vbkenya New Member

    For the second query your WHERE clause works with the 'comp_key' column.

    -Is there an index on this column in the underlying table?
    -What is the selectivity of this column ?




    NHO
  3. bambola New Member

    Are you using the index hint in the view as well? Doing so will result this warnning.
    Generally speaking, I would try to avoid using index hints as much as possible. Though in few cases it's inavitable.

    It would help if you post the table and view structures and indexes.

    Bambola.
  4. gaurav_bindlish New Member

    I agree with vbKenya, the second query uses 'comp_key' column. If there is no index on this column, the query analyzer will go for a rable scan. Does index case_ref have comp_key as one of the columns in the index? If not, the query optimizer will not use this index and will flash this error. Other thing is that even if it has the column but it is not the intial column in the index, the optimizer will ignore this hint as it cann't use the indes in this case as well.

    HTH.

    Gaurav
  5. Jazz New Member

    I have reduced the problem described into a more simplifed form. The table scanning affect occurs only when a significant amount of data exists.

    I have two identical tables in separate databases (called correspondence). I create view:

    create view testview as
    select corr_key, policy_no from database1.dbo.correspondence
    union all
    select corr_key, policy_no from database2.dbo.correspondence

    The query:
    select * from testview where corr_key = 333

    returns instantly. The execution plan shows that the query uses an index seek on corr_key on each of the underlying tables before before concatenating the results.

    The query:
    select * from testview where policy_no = 'xxxx'

    completes in 5 seconds, returning less data. The execution plan shows an index seek on corr_key for one of the underlying tables in the view. The other underlying table requires a table scan, a 'compute scalar' on the field policy_no, then a filter.

    A non-clustered index exists on both tables on both fields






  6. gaurav_bindlish New Member

    Is the index same on both the tables? What about the data? If there are very less no. of rows in the table, optimizer may ignore the index on the table. Conversely if there is large amount of data and the query is not that selective, then also the index may be ignored. See the difference in no. of rows affected by query in both tables. If this is same, try updating the statistics for these indexes.

    HTH.

    Gaurav
  7. Jazz New Member

    For testing I used DTS to transfer the object correspondence from database1 to database2. Therefore the object should be identical. I have also run 'update statistics' on both tables. There are 578407 rows in each table. There are 578407 unique corr_key values and 53014 unique policy_no values.
  8. gaurav_bindlish New Member

    I am not very sure if DTS cretes the indexes. Can you please confirm that the indexes are similar? How about the index? What is the index structure?

    Gaurav
  9. bambola New Member

    I see what you mean. I created to similar tables on 2 databases with the same 2 indexed (one on int the other on varchar) and a view on them. Searching by the int column does a seek while the other does a scan. The only way I could have an index seek was using the index hint. It gave the warning, but it also did the seek.
    I did the same in the same database, and it was doing seek in both cases. So I guess it has something to do with the fact you are using 2 databases.

    Bambola.
  10. Jazz New Member

    I can confirm though that (for testing purposes) the data and indexes in the two tables are identical. My issue with the query optimiser is that it is choosing a different query plan when the command is issued as a view rather than as a select.

    The following queries all perform an index seek on the index 'policy_no' and are performant:
    Q1 - select * from database1.dbo.correspondence where policy_no = 'xxxx'

    Q2 - select * from database2.dbo.correspondence where policy_no = 'xxxx'

    However when I create a query to select data in union a table scan occurs.
    create view testview as
    select corr_key, policy_no from database1.dbo.correspondence
    union all
    select corr_key, policy_no from database2.dbo.correspondence

    select * from testview where policy_no = 'xxxx'
    /* results in a table scan and poor performance */

    Expanding the view to encompass further databases and tables reduces the performance further; only the first table is 'seeked' further tables are all 'scanned'

    create view testview as
    select corr_key, policy_no from database1.dbo.correspondence /*seeked*/
    union all
    select corr_key, policy_no from database2.dbo.correspondence /*scanned*/
    union all
    select corr_key, policy_no from database3.dbo.correspondence /*scanned*/


    I wish to know how (or if it is possible) to tell the optimiser (as part of the view?) to use a particular search method.


  11. Jazz New Member

    Bambola, Gaurav, Thank you for taking the taking the time to read and try. Any suggestions you are able to make will be greatly appreciated.
  12. bambola New Member

    I just tried it in the office and it is doing index seek in both cases. The difference is that at home I have Personal edition and here Developer. What edition are you using? try SELECT @@VERSION in QA.

    Bambola.
  13. Jazz New Member

    select @@version reveals:

    Microsoft SQL Server 2000 - 8.00.534 (Intel X86)
    Nov 19 2001 13:23:50
    Copyright (c) 1988-2000 Microsoft Corporation
    Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 3)


    :-(

  14. bambola New Member

    I suggest you update your sql server to sp3 <img src='/community/emoticons/emotion-1.gif' alt=':)' /><br /><br />Bambola.

Share This Page