qUERY Performance

Question

Post reply

qUERY Performance

Dreamsz

SSC Eights!

Points: 911
More actions
February 18, 2008 at 9:26 pm

#120412

Hi
I am stuck in a simple query as i made it simple before it was more complicated but as am a DBA not a hardcore developer can you guys please look it at give me any helpful suggestion
I am doing a self join on a table which has 4million rows in it and when i run the below query its taking more than 40 mins to execute so can i twik/tune more please help!!!
Thanks
select A.Column1, B.Column1, count(A.Column1) as Frequency
,sum(B.Column2-A.Column2) as Difference
from TEST_TABLE 1 A with (readuncommitted)
inner join TEST_TABLE 1 B with (readuncommitted)
on A.Column3 = B.column3
where
A.column1 != B.column1 and
B.column1 in (select distinct Column1 from TEST_TABLE 2 with (readuncommitted) where dATE > @dtCurrentDate and Date < @dtCurrentDate + 1)
and A.Column1 not in (select column1 from TEST_TABLE3 with (readuncommitted))
and B.Column1 not in (select Column1 from TEST_TABLE4 with (readuncommitted))
group by A.Column1, B.Column1

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

HardCoder Ten Centuries Points: 1045 More actions · Answer 1

Hi,

Can you please check your query one more time, as it seems not right.

As the query itself will return 0 records.

select A.Column1, B.Column1, count(A.Column1) as Frequency
,sum(B.Column2-A.Column2) as Difference
from TEST_TABLE 1 A with (readuncommitted)
inner join TEST_TABLE 1 B with (readuncommitted)
on A.Column3 = B.column3
where
A.column1 != B.column1

Thanks

Mohammad Irfan
http://matespoint.blogspot.com
http://www.irfit.com

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

One this that may make a small difference, you don't need a distinct in a subquery used in an IN clasue.

Can you post the schema of the tables and the index definitions. Aprox rowcounts would also help.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Christopher Stobbs SSC-Insane Points: 21098 More actions · Answer 3

Hi All,

Would it not also be faster to use Left Joins instead for WHERE IN (Select) statements?

Thanks

Chris

----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley

:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]

SQL-4-Life