SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 diifrence in behaviour of equal queries
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

heze
Posting Yak Master

USA
192 Posts

Posted - 12/02/2006 :  21:52:53  Show Profile  Reply with Quote
Hi

im running the same query against 2 tables in different databases. Both the tables have exactly the same schema, however, when I run

select *
from [myDB1]..myTable
where not id in (select id from another table where not id is null)
------
--and then
select *
from [myDB2]..myTable
where not id in (select id from another table where not id is null)
--whereas query 2 takes 7 seconds, query 1 takes more than 3 minutes

the only difference I can see up to now is that table queried in myDb2 has indexes while the other has not.

Does anyone have some suggestion of how to approach the problem?

thanks


jezemine
Flowing Fount of Yak Knowledge

USA
2885 Posts

Posted - 12/02/2006 :  23:03:01  Show Profile  Visit jezemine's Homepage  Reply with Quote
indexes certainly play an important role in the performance of select queries.

probably if you look at the execution plans for each of these queries, you will find that they are different.


SqlSpec: a fast and comprehensive data dictionary generator for
SQL Server 2000/2005, Analysis Server 2005, Access 97/2000/XP/2003
http://www.elsasoft.org
Go to Top of Page

snSQL
Flowing Fount of Yak Knowledge

USA
1837 Posts

Posted - 12/02/2006 :  23:57:30  Show Profile  Reply with Quote
If I asked you to find a number in the phone book from a person's name or from a person's address, which one could you do more quickly? That's because a phone book has an index by the person's name, but not by their address.

So in short - yes, indexes are arguably the single most important factor in query performance. And not just indexes, but the right indexes regularly maintained (update statistics and rebuild or reorganize).
Go to Top of Page

heze
Posting Yak Master

USA
192 Posts

Posted - 12/03/2006 :  00:01:39  Show Profile  Reply with Quote
in this case, then, I believe the indexes are badly placed since its the indexed table the problematic one, I will check the indexing.

thanks
Go to Top of Page

snSQL
Flowing Fount of Yak Knowledge

USA
1837 Posts

Posted - 12/03/2006 :  11:17:34  Show Profile  Reply with Quote
By the way, you should rewrite the query to use the EXISTS operator

select *
from [myDB1]..myTable A
where not exists (select * from anothertable B where B.id = A.id)
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000