Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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;
share|improve this question
    
What indexes do you have on the tables? What is the execution plan now? Add the CREATE TABLE scripts. –  ypercube Mar 28 at 12:22

1 Answer 1

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

share|improve this answer
    
If there were an extra 10 columns returned, would I just add these to the INCLUDE statement as well? –  k09 Mar 28 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 Mar 29 at 21:46

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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