7

I have a Records table with over 100 columns and very many rows, and a nonclustered index on 5 fields based on my access paths:

CREATE NONCLUSTERED INDEX [IX_Records_CustomerID]
ON [dbo].[Records] (
    [CustomerID] ASC, -- int
    [IsInvalid] ASC, -- int
    [IsProcessed] ASC, -- bit
    [IsRejected] ASC, -- bit
    [RecordName] ASC, -- varchar(12)
;

The 5 fields do not include the primary key RecordID, which is the column in the clustered index.

Here is my poorly performing query:

SELECT * FROM Records WHERE CustomerID IN (181, 283, 505)

The execution plan shows that it performs a Clustered Index Scan, which I understand is because I'm selecting columns that are not included in the index. In Management Studio, I change the query to:

SELECT CustomerID, IsInvalid, IsProcessed, IsRejected, RecordName FROM Records 
    WHERE CustomerID IN (181, 283, 505)

And the execution plan shows an Index Seek, and the query execution time drops from 44 seconds to 2 seconds. However, I lack the liberty in the application to replace the * with only the columns I need and have included in my index.

Is there any way around the clustered index scan when I'm locked into SELECT *?

  • That is by-design. It's cheaper to scan the table for all fields than to do 2 operations, one on the index, and one on the table. – Max Vernon Jul 10 '14 at 2:50
  • 3
    If you care about performance, you'll change the app. Oh, and "performant" means "one who performs in a play, etc". – Max Vernon Jul 10 '14 at 2:52
  • 1
    What's in the IN (…) subquery? – Greenstone Walker Jul 10 '14 at 9:43
  • 2
    As a workaround, can you create a view (with just the 5 columns you need), and then let the app use SELECT * FROM that_view WHERE CustomerID IN (...); ? – ypercubeᵀᴹ Jul 10 '14 at 9:53
  • 1
    @MaxVernon Thank you! "Performant" gets thrown around a lot where I work, now I know! I've revised my wording in the question. – Chris Schiffhauer Jul 10 '14 at 13:54
7

If you need columns in the output that aren't covered by the index, the optimizer has to make a choice:

  1. Perform a table / clustered index scan (therefore all columns are there)
  2. Perform a seek, then perform lookups to retrieve the columns not covered

Which way it will choose depends on a variety of things, including how narrow the index is, how many rows match the predicate, etc. You can force a seek with the FORCESEEK hint, but I suspect it will end up performing the same or worse than the scan SQL Server has chosen in your case.

Some options:

  1. Change the app to run a proper query. I listed this first for a reason.
  2. Create a view that selects only the columns you need:

    CREATE VIEW dbo.myview
    WITH SCHEMABINDING
    AS
      SELECT col1, col2, col3 FROM dbo.tablename;
    

    Then you can change the app to SELECT * from this view. Or you can get even more creative and rename the original table, and change the name of this view to what the name of the table used to be. Breaking change, obviously; proceed with caution.

  3. Add all of the other columns to the key or INCLUDE list for the index. If these are hard-coded values and always the ones used, you may consider a filtered index.

  • 2
    Thank you and @MaxVernon for the emphasis on the importance of changing the query. I'll get buy-in from my colleagues and make that happen. – Chris Schiffhauer Jul 10 '14 at 13:56
0

Having those other fields in the index is just making it worse if you're always using select *. Try creating index just for CustomerID. That might help, but it depends on several things like how many IDs there is in the in list and how many rows per CustomerID there is in the table.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.