Join speed performance question

[tektipdjango]

suppose you have 2 tables A and B with a join on A.Id and B.A_Id.
A.Id is indexed and not B.A_Id

what is is most efficient?

1 -
select A.Id
from
A
left join
B
on A.Id=B.A_Id

2 -
select A.Id
from
B
right join
A
on B.A_Id=A.Id

I Think solution 2 is best, is it right?

django
bug exterminator
tips'n tricks addict

 

[ArtieChoke]

I suspect they will both become an exhaustive search. If you have query analyzer, you can create the execution plan and see hoq the server is going to process it.

"I think we're all Bozos on this bus!" - Firesign Theatre

 

[fluteplr]

Won't make any difference. The optimization engine will treat them both idetically. If you want the query to run better put in the index.

 

[tektipdjango]

fluteplr,

If what you say is right, the optimizer is not efficient.

look at this algorithm:

Create a table C from A with 2 fields
ID, foreignID with C.ID=A.ID, foreignID=null (C.ID is indexed)
take each record of B table and put in the C table the B.ID in C.foreignID (it can use an index on C)

You now have a C table which is what I want

Am I right?

[highlight]Django[/highlight]
bug exterminator
tips'n tricks addict

 

[tektipdjango]

I tried with mySQL as I have not SQL SERVER on my computer.

Here are the results :

select A.Id
from
A
right join
B
on B.A_Id=A.Id

select A.Id
from
B
left join
A
on B.A_Id=A.Id

Both are good as the index on A.Id is used and only to find the matching item in A

the other solutions

select A.Id
from
A
left join
B
on B.A_Id=A.Id

select A.Id
from
B
right join
A
on B.A_Id=A.Id

are bad as there is an exhaustive search on both tables

select A.Id
from
A
inner join
B
on B.A_Id=A.Id

strangely is not good
It uses the A.Id index but to scan all the records of table A!

[highlight]Django[/highlight]
bug exterminator
tips'n tricks addict

 

[dmcmunn]

Are the datatypes of these two columns (B.A_ID and A.ID) identical ?

In olden days (pre-index hint) of SQL Server you could force the optimizer to consider an ignored index by adding something as silly as "where B.A_ID > 0" .

Not sure if any of this carp is of use to you...

Best,
dm