Perfomance in Query; filter order

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

SQL Server 2008 Forums

Transact-SQL (2008)

Perfomance in Query; filter order

Author

Topic

avijit_mca
Posting Yak Master

109 Posts

Posted - 2010-06-30 : 08:17:44

table A

table B

I need data from table A =which are not avail bale in B

Select * from a where not exists (select * from b where empid=a.empid)

Now my question :

Say Table A has 1 lak rows

Say table B has 80 k rows which match with table A.

It means first table A 1 lak rows compare with 80 k rows in table B.

But say in table A out of 1 lak emp, 10 thousand emp already left the organisation.

Soi want filter DOR>=getdate()

nOW:

Select * from a where not exists (select * from b where empid=a.empid) AND DOR>=getdate()

OR

Select * from a where DOR>=getdate() AND not exists (select * from b where empid=a.empid)

MY OBJECTIVE IS TO BE MORE EFFECTIVE MY QUERY IN TERMS OF PERFORMANCE.

I WANT TO FIRST RETRIEVE 90 K ROWS & THEN WANT TO COMPARE WITH B TABLE.

SO WHICH CODE IT WILL BE BETTER.

PLS TELL ME IN WHICH SEQUENCE FILTER HAPPENED

Regards,
avijit

mohd.taheri
Starting Member

5 Posts

Posted - 2010-06-30 : 08:32:44

You want to select Data from table A wich is not in table B
Try the bellow Query.
the performance is amazing

Select *
From TableA a
left join TableB b on a.empid=b.empid
where b.empid is Null

Regards

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-06-30 : 08:34:51

What is the execution plan is telling you?

No, you're never too old to Yak'n'Roll if you're too young to die.

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-06-30 : 09:58:54

quote:
[i]PLS TELL ME IN WHICH SEQUENCE FILTER HAPPENED

It depends, and not on the order that you specify them in the where clause. Depends on the indexes available and on which the optimiser thinks is the faster method.

http://sqlinthewild.co.za/index.php/2007/07/29/order-of-execution/

--
Gail Shaw
SQL Server MVP

avijit_mca
Posting Yak Master

109 Posts

Posted - 2010-07-01 : 03:08:56

mohd.taheri:

i know that query. But if u think performance in that case my query will be more faster than left join.

Anyway my question was the sequence for where clause.

Regards,
avijit

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources