April 28, 2008 at 8:03 am
select * from main_table m (nolock)
where exists (select 's' from childa a (nolock)
where a.a = m.a )
and exists (select 's' from childb b (nolock)
where b.a = m.c )
this query will be effective if main table make join with childb table first.but its alway's making join with childa table after that only its joining with childb table .
is there any key words to force the compiler change the execution plan like what i specefied ?
April 28, 2008 at 8:09 am
Proper indexes and up to date statistics. Compiler hints are to be avoided wherever possible.
Can you post the schema of the tables, the indexes defined and the aprox row counts involved please?
Edit: You may want to try changing those exists to inner joins. It's unlikely to make a difference, but is worth a try.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 28, 2008 at 8:24 am
I'd agree with Gail. Go with inner joins where you can and use good indexing.
April 28, 2008 at 8:25 am
my childb table having less data compare with childa table .eventhough its taking childa table . i have clustered index on the childb table .
April 28, 2008 at 8:35 am
Can you post the schema of the tables and their indexes please?
Without that, we're just guessing.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply