| Author |
 Topic  |
|
|
learning_grsql
Posting Yak Master
165 Posts |
 Posted - 09/28/2012 : 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 table1
WHERE ID NOT IN
(
SELECT MAX(ID)
FROM Table1
where field4 = 'xyz' and date between '20120801' and '20120831'
GROUP BY field1, field2, field3)
and field4 = 'xyz' and date between '20120801' and '20120831'
|
Edited by - learning_grsql on 09/28/2012 11:27:04
|
|
|
sunitabeck
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 09/28/2012 : 09:40:28
|
| Just to be clear - are table1 and Table are two different tables? |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
India
48119 Posts |
Posted - 09/28/2012 : 10:14:58
|
whats are indexes present? did you check execution plan?
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
learning_grsql
Posting Yak Master
165 Posts |
Posted - 09/28/2012 : 11:29:27
|
@sunitabeck
sorry as usual it was my typo.
@visakh16.
I just try as below but it now takes more than 3minutes.
drop index indexname on dbo.table1
go
create nonclustered indexname on dbo.table1 (date, field4)
go
select * from table1
WHERE ID NOT IN
(
SELECT MAX(ID)
FROM Table1
where field4 = 'xyz' and date between '20120801' and '20120831'
GROUP BY field1, field2, field3)
and field4 = 'xyz' and date between '20120801' and '20120831'
|
|
|
|
sunitabeck
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 09/28/2012 : 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
India
48119 Posts |
Posted - 09/28/2012 : 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 MVP
http://visakhm.blogspot.com/
|
|
|
|
sunitabeck
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 09/28/2012 : 14:21:52
|
Thanks Visakh  |
|
|
|
learning_grsql
Posting Yak Master
165 Posts |
Posted - 09/28/2012 : 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
India
48119 Posts |
Posted - 09/28/2012 : 16:09:21
|
cool
Sunita is the star!
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
| |
Topic |
|
how 2 make query execute faster (where not in)
