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;
  • What indexes do you have on the tables? What is the execution plan now? Add the CREATE TABLE scripts. – ypercubeᵀᴹ Mar 28 '14 at 12:22
2

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

| 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 '14 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 '14 at 21:46

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.