April 19, 2008 at 11:38 pm
I just found out that if I have a foreign key, plan for join shows table scan on detail table. On ASA, when you create a foreign key, it creates an index for it and uses it for natural joins.
Am I doing something wrong, or a separate index has to be created?
select * from master M join detail D on D.master_id=M.id where D.id=:id
--index seek on both tables
select * from master M join detail D on D.master_id=M.id where M.id=:id
--index seek on master and table scan on detail
create index ix_detail_master_id on detail(master_id)
select * from master M join detail D on D.master_id=M.id where M.id=:id
--index seek on both tables
To check integrity on detail, no index is needed, as it can use primary key of referenced table. But what if you delete a record in master table? Does it perform table scan on all referencing tables to ensure integrity?
Robert.
April 20, 2008 at 9:59 am
Yes, foreign key itself doesn't create an index. This is quite common misconception. You have to create an index on foreign key columns to speedup queries.
Piotr
...and your only reply is slàinte mhath
April 20, 2008 at 12:52 pm
Indeed, you'll need to add the index yourself.
This script can help:
http://www.sqlservercentral.com/scripts/Indexing/61391/
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
April 20, 2008 at 3:38 pm
Thanks for all responses. This fact (or common misconception) surprises me.
Pretty useful script, alzdba.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply