Join Small Table to Big Table or Vice Versa, does it matter?

[serge]

If I join Table1 to Table2 with a WHERE condition, is
it the same if I would join Table2 to Table1 considering
that the size of the tables are different.

Let's assume Table2 is much bigger than Table1.

I've never used MERGE, HASH JOINs etc, do any of
these help in this scenario?


Thank you

[Erland Sommarskog]

Re: Join Small Table to Big Table or Vice Versa, does it matter?

serge (sergea@nospam. ehmail.com) writes:[color=blue]
> If I join Table1 to Table2 with a WHERE condition, is
> it the same if I would join Table2 to Table1 considering
> that the size of the tables are different.
>
> Let's assume Table2 is much bigger than Table1.[/color]

For an inner join the order does not matter.
[color=blue]
> I've never used MERGE, HASH JOINs etc, do any of
> these help in this scenario?[/color]

These are optimizer hints, and you should use them if you can get
good performance in any other way.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[David Portas]

Re: Join Small Table to Big Table or Vice Versa, does it matter?

> If I join Table1 to Table2 with a WHERE condition, is[color=blue]
> it the same if I would join Table2 to Table1 considering
> that the size of the tables are different.[/color]

Yes.
[color=blue]
> I've never used MERGE, HASH JOINs etc, do any of
> these help in this scenario?[/color]

No.

--
David Portas
SQL Server MVP
--

"serge" <sergea@nospam. ehmail.com> wrote in message
news:7_%te.8275 8$Jk6.1151808@w agner.videotron .net...[color=blue]
> If I join Table1 to Table2 with a WHERE condition, is
> it the same if I would join Table2 to Table1 considering
> that the size of the tables are different.
>
> Let's assume Table2 is much bigger than Table1.
>
> I've never used MERGE, HASH JOINs etc, do any of
> these help in this scenario?
>
>
> Thank you
>[/color]

[Karim]

Re: Join Small Table to Big Table or Vice Versa, does it matter?

On Tue, 21 Jun 2005 17:52:02 -0400, serge wrote:
[color=blue]
> If I join Table1 to Table2 with a WHERE condition, is
> it the same if I would join Table2 to Table1 considering
> that the size of the tables are different.
>
> Let's assume Table2 is much bigger than Table1.[/color]

It doesn't matter because the query optimizer will go through thousands of
optimization iterations and choose the best plan it found so far in the
time frame it knows it should not spend any further.
[color=blue]
>
> I've never used MERGE, HASH JOINs etc, do any of
> these help in this scenario?[/color]

no.. The query optimizer is smart enough to choose the best plan.


Tony
--

http://www.dotnet-hosting.com

Free web hosting with ASP.NET & SQL Server