diifrence in behaviour of equal queries

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 2000 Forums

SQL Server Development (2000)

diifrence in behaviour of equal queries

Author

Topic

heze
Posting Yak Master

192 Posts

Posted - 2006-12-02 : 21:52:53

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
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2006-12-02 : 23:03:01

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

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-12-02 : 23:57:30

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

heze
Posting Yak Master

192 Posts

Posted - 2006-12-03 : 00:01:39

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

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-12-03 : 11:17:34

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)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources