1

I have 2 tables (tblA and tblB) joined by ReportID key. This key is a Primary Clustered key on tblA, and is a FK on tblB. I have created a Non-clustered index on UserID and UserDate in tblA.

I have a query where I want to return a number of rows from tblB based off a ReportID selection from tblA.

My issue is the Execution Plan runs a Clustered Index SCAN on tblB. How do I get it to perform a seek? Or how can I improve the query execution?

Thanks!

SELECT 
     b.[Asset]
    ,b.[Investment]
    ,b.[Currency]
    ,b.[Address]
    ,b.[Email]
    ,b.[Region]
    ,b.[Country]
    ,b.[Sector]
FROM 
    tblB b
INNER JOIN      
    tblA a
    ON a.ReportID = b.ReportID
WHERE a.UserID = @UserID
    AND a.UserDate = @Period;
Improve this question
1
  • What indexes do you have on the tables? What is the execution plan now? Add the CREATE TABLE scripts.
    – ypercubeᵀᴹ
    Commented Mar 28, 2014 at 12:22

1 Answer 1

Reset to default
2

Put a non-clustered index on b.ReportID, and 'include' all the other columns from b that your query uses.

Improve this answer
2
  • If there were an extra 10 columns returned, would I just add these to the INCLUDE statement as well?
    – K09
    Commented Mar 28, 2014 at 15:03
  • Yes. Otherwise, your index is not enough to solve the query, and lookups would be needed. If it thinks too many lookups need to be done, it could well figure the index simply isn't good enough and divert to using the Clustered Index.
    – Rob Farley
    Commented Mar 29, 2014 at 21:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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