| Author |
 Topic  |
|
|
heze
Posting Yak Master
USA
192 Posts |
 Posted - 12/02/2006 : 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
Flowing Fount of Yak Knowledge
USA
2875 Posts |
Posted - 12/02/2006 : 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
Flowing Fount of Yak Knowledge
USA
1837 Posts |
Posted - 12/02/2006 : 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
USA
192 Posts |
Posted - 12/03/2006 : 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
Flowing Fount of Yak Knowledge
USA
1837 Posts |
Posted - 12/03/2006 : 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) |
|
|
| |
Topic |
|
diifrence in behaviour of equal queries
