Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 how 2 make query execute faster (where not in)

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 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
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?
Go to Top of Page

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 MVP
http://visakhm.blogspot.com/

Go to Top of Page

learning_grsql
Posting Yak Master

230 Posts

Posted - 2012-09-28 : 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'

Go to Top of Page

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;
Go to Top of Page

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 MVP
http://visakhm.blogspot.com/

Go to Top of Page

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-09-28 : 14:21:52
Thanks Visakh
Go to Top of Page

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....

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-28 : 16:09:21
cool
Sunita is the star!

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
   

- Advertisement -