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.
Author |
Topic |
heze
Posting Yak Master
192 Posts |
Posted - 2006-12-02 : 21:52:53
|
Hiim running the same query against 2 tables in different databases. Both the tables have exactly the same schema, however, when I runselect *from [myDB1]..myTable where not id in (select id from another table where not id is null)--------and thenselect *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 minutesthe 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 operatorselect *from [myDB1]..myTable Awhere not exists (select * from anothertable B where B.id = A.id) |
|
|
|
|
|
|
|