sql server data warehouse: Basic Question on SQL SELECT Performance -- sql server data warehouse

Basic Question on SQL SELECT Performance

Joseph Geretz
3/21/2004 3:10:10 PM

sql server data warehouse:

Hi, I hope this is an appropriate group to ask a question about basic SQL
SELECT performance.

I'm creating a VIEW which draws together two tables; T_ONE and T_MANY.
Basically, the VIEW creates a LEFT JOIN from T_ONE to T_MANY so that the Ad
Hoc SQL submitted from the software is simply a SELECT * from the VIEW. (I
like to use views to keep software-embedded SQL as simple as possible.)

Now here's the thing. The item from the WHERE clause when selecting from
this view is from a column in T_MANY. Does this indicate that for
performance reasons I should place T_MANY on the left side of the JOIN? I've
always thought that relative JOIN placement defines logical join
relationship, but that the optimizer would intelligently decide upon the
quickest path to the data, regardless of where a particular table is placed
in the JOIN.

Disclaimer: I'm using a different DBMS right now, Interbase, and was getting
absolutely horrible performance until I switched T_MANY around to the left
side of the join. We're migrating to SQL Server and I'm wondering whether
what I'm seeing is actually a feature of SQL, or whether Interbase simply
doesn't 'have the brains' to optimize the query properly. Would this be a
problem in SQL Server as well? Would an INNER JOIN be more efficient than a
LEFT join (effectively, our relationship constraints mandate that these must
be INNER joins anyway - it's not possible for an entry to exist in either
table without at least one match in the other table) or should it make no
difference?

Thanks for your advice.

Joe Geretz

Re: Basic Question on SQL SELECT Performance

Shyamkumar
3/26/2004 7:44:36 AM

Defintely Inner join is better then Outer Joins.
In the Query, is that you are only filtering the rows between the two tables
with a where clause or is it that you are trying to find out all the left
items and replacing them with values.
From my understanding you are trying to do a simple join. So please use
Inner Join and it should give you a good performance and make sure indexes
are done properly on the tables where the join is made. And if it contains
too many rows then collect statistics before you do the join.

Shyam
[quoted text, click to view]