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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy