SQL Server Performance

using View causing index scan in underlying tables

Discussion in 'ALL SQL SERVER QUESTIONS' started by WingSzeto, Feb 23, 2012.

  1. WingSzeto Member

    I have two identifcal tables, table A and table B. Both have primary cluster index on field col1. tableA is holding current data and tableB is history data. A view is created to combine the data in tableA and tableB. There is a third table tableC which I use to retrieve specific records from both table A and B. TableC has a col2 which is a FK to col1 in both tableA and tableB. This col2 is indexed. Table A has 50 millions records and table B has 10,000 records. Table C has 50,000 records. The query below is intended to return all records matching from tableC. So the query result returns 50,000 records.

    When I look at the execute plan of the below query, optimizer choose a clustered index scan on tableA. As a result, it took a few minutes to run. Can someone explain why it is not using clustered index seek instead? Right now I have to put in a forceseek hint to avoid the scan.

    w

    Create view vw_allData
    as
    Select * from table A
    Union
    Select * from table B

    Select tableAB.col1, tableAB.col4,tableAB.col5
    from tableC with (nolock)
    inner vw_allData as tableAB with (nolock) on tableAB.col1 = tableC.col2
  2. FrankKalis Moderator

    There is no WHERE clause in your query. What else should the optimizer do than to scan the whole tables to find matching rows?
  3. Shehap MVP, MCTS, MCITP SQL Server

    As a generic outline , using views could result of much scans since a view look like table without index ….

    Therefore , it much preferred to dispense views if inside joins or where conditions and supersede them by healthy coding practices …

    You could follow up my article for this regard :

    As by now , please work out the below query and let me know your input to be able to jump to other enhancements if needed

    Select tableAB.col1, tableAB.col4,tableAB.col5

    from tableC with (nolock)

    inner tableA with (nolock)on tableA.col1 = tableC.col2

    union

    Select tableAB.col1, tableAB.col4,tableAB.col5

    fromtablewith (nolock)

    inner tableB with (nolock)on tableB.col1 = tableC.col
  4. WingSzeto Member

    Frank,

    As I illustrated in my message, the reason there isn't any where clause is because I use tableC to join the view for all the records I want to return. If tableC has 100,000 records, the query result will have 100,000 records. You can think of tableC as a filter or 'where clause' if you will. I was thinking that the join to the view is based on the primary key of the underlying tables and an existing index on tableC, my thinking was that the optimizer should use the Seek but it turns out to be the clustered index scan on the biggest table tableA. I think Shehap may have shed some light on this matter.

    Shehap,
    Thank for your suggestion. The problem is that I will need to apply this to 100 or more code in our application. This is for archiving old data purpose and we need to create a view to combine the current and old data and alias the view name to the actual table name so no coding change is needed. Any other thought about this view dilemma? You mention you have an article I can read, can u give me the link?

    w
  5. Shehap MVP, MCTS, MCITP SQL Server

    I have missed to add this link :

    http://www.sqlserverpath.org/blog/2012/01/09/towards-t-sql-queries-of-0-sec-third-part/

    But indeed, the idea of cutting off more codes in order to simplify coding syntax work against performance wise in a lot of circumstances that might end up with a culprit point of performance in case of stress workloads on a huge data entity.

    Therefore , I do believe that if not suspected to add more business logics to this view , then it is much worthwhile to dispense that view to get benefits of sufficient indexes on tables.

    If developers still persist not to change their codes, you could at least :

    1. Reduce the no of columns to reduce IO cost of views like :

    Createview vw_allData

    as

    Select col1, col4,col5

    fromtable A

    Union

    Select col1, col4,col5

    fromtable B

    2. Create sufficient indexing on the Col1 of table A and table B

    3. Use Data compression techniques of 2008 in case of huge data entity on either clustered indexes or relevant non clustered indexes.(Most likely Page compression for our case here)
  6. FrankKalis Moderator

    Yes, I understood this. But then again, how should SQL Server know that based on the whole content of one input table it should use a seek on an index on another table to find the matching rows?

Share This Page