Author |
Topic |
learning_grsql
Posting Yak Master
230 Posts |
Posted - 2012-09-28 : 09:31:13
|
I just wrote a code as below to find the list of duplicates, but it takes 2 minutes 45 seconds to execute.is there any way I can make it faster?select * from table1WHERE ID NOT IN(SELECT MAX(ID)FROM Table1where field4 = 'xyz' and date between '20120801' and '20120831'GROUP BY field1, field2, field3)and field4 = 'xyz' and date between '20120801' and '20120831' |
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-09-28 : 09:40:28
|
Just to be clear - are table1 and Table are two different tables? |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-09-28 : 10:14:58
|
whats are indexes present? did you check execution plan?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
learning_grsql
Posting Yak Master
230 Posts |
Posted - 2012-09-28 : 11:29:27
|
@sunitabecksorry as usual it was my typo.@visakh16.I just try as below but it now takes more than 3minutes.drop index indexname on dbo.table1gocreate nonclustered indexname on dbo.table1 (date, field4)goselect * from table1WHERE ID NOT IN(SELECT MAX(ID)FROM Table1where field4 = 'xyz' and date between '20120801' and '20120831'GROUP BY field1, field2, field3)and field4 = 'xyz' and date between '20120801' and '20120831' |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-09-28 : 11:34:10
|
See if this performs any better:SELECT * FROM( SELECT *, RANK() OVER (PARTITION BY field1,field2, field3 ORDER BY ID DESC) AS RN FROM table1 WHERE field4 = 'xyz' AND date BETWEEN '20120801' AND '20120831') SELECT * FROM cte WHERE RN > 1; |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-09-28 : 12:33:18
|
quote: Originally posted by sunitabeck See if this performs any better:SELECT * FROM( SELECT *, RANK() OVER (PARTITION BY field1,field2, field3 ORDER BY ID DESC) AS RN FROM table1 WHERE field4 = 'xyz' AND date BETWEEN '20120801' AND '20120831') ELECT * FROM ctet WHERE RN > 1;
fixed typos------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-09-28 : 14:21:52
|
Thanks Visakh |
|
|
learning_grsql
Posting Yak Master
230 Posts |
Posted - 2012-09-28 : 16:07:13
|
Thanks visakh and Sunitabeck as usual.it works fine and it executes in 17 seconds.... |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-09-28 : 16:09:21
|
coolSunita is the star!------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|