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. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

How do I rewrite to improve performance of the below query.

select S.col1, S.col2, MaxDDate
from s_tbl s 
  left join 
    (select col1, MAX(DDate) MaxDDate 
     from F_tbl 
     group by col1
    ) f 
    on f.col1 = s.col1 
where s.col3 in (1,4,6,9,17) 
  and s.col4 = 1 
  and (DateDiff(Month, GETDATE(), MaxDDate) <= 3 or MaxDDate is null) 
order by s.col5
share|improve this question
Execution plan.. – Mihai Jan 22 '14 at 11:15
table structure, indexes ... – ypercubeᵀᴹ Jan 22 '14 at 11:57
I'll echo what was said above. It'll be a lot easier to assist if we had the DDL for the tables and some sample data. At the very least a post execution plan. – Thomas Stringer Jan 22 '14 at 13:37
Thanks for all the offers to help guys.. sorry since it was a little critical, I had to expedite the task. I created an index - followed trial and error method - but it really worked! I basically created an index on F_Tbl on col1 and Ddate. I don't want to use the "Answer your own Question" tab because this is not a universal solution for this problem. Thanks again! – user32027 Jan 23 '14 at 4:52

Your Answer


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

Browse other questions tagged or ask your own question.