June 8, 2014 at 1:03 am
Hi Everyone,
Total records : 2,50,0000.
select A.C1,
Max(DateDiff(D, Coalesce (Date1,Date2,Date3),Getdate())),
from T1 as A
Join T2 B on A.rnum = B.rnum and A.lnum = B.lnum AND A.type = 'L'
GROUP BY A.C1
I have got non clustered index on C1
I want to query data as fast as i can, Please have a look and let me..Thanks in advance.
June 8, 2014 at 2:27 am
suggest you read this and then post back again.
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
June 8, 2014 at 8:05 am
Yes, please review that article...and then upload your current execution plan. Just guessing, but you'll most like have a clustered index scan on T1 and T2 (unless it's a heap).
Maybe you might want to try creating a non clustered index on T1 using RootNum, LineNum, and Type as key columns, and C1, Date_Physician_Reviewed, Date_Nurse_Reviewed, CODER_RVWD_DT as included columns.
And consider something similar to T2 as the JOIN hits the same columns in T2 (for RootNum, LineNum)
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
June 8, 2014 at 9:52 am
Reformatted your code to read it easier:
select
A.C1,
Max(DateDiff(D, Coalesce (Date_Physician_Reviewed,Date_Nurse_Reviewed,CODER_RVWD_DT),Getdate())),
'MN QA REPORT - PASSED RECONSIDERATION REPAYMENT'
from
T1 as A
Join T2 B
on A.Rootnum = B.rootnum
and A.linenum = B.linenum
AND A.type = 'L'
GROUP BY
A.C1
The only columns that I know belong to a specific table are the columns that you prefixed with table aliases. The tables in the COALESCE function I have no idea which table they come from so hard to tell you much regarding them.
I could see an index on RootNum, LineNum, Type on table T1 and RootNum, LineNum on table T2. With as few columns being returned I would add the columns C1 from T1 to that index and the columns Date_Physician_Reviewed, Date_Nurse_Reviewed, CODER_RVWD_DT to the aapropriate indexes on which ever table they reside.
Want or need more? You need to post more information. In this case you should read the second article I reference below in my signature block.
June 8, 2014 at 10:28 am
Thank you everyone for your valuable reply.i have created indexes and its working fine.Thanks
June 8, 2014 at 10:43 am
Awesome, glad it worked.
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
June 9, 2014 at 5:09 pm
What you really need to do first is add the best clustered index to both tables. Then and only then do you add other, non-clustered, covering indexes if they are still required.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply