Basic Question on SQL SELECT Performance

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 GeretzDefintely 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
"Joseph Geretz" <jgeretz@.nospam.com> wrote in message
news:OxNKHB4DEHA.1544@.TK2MSFTNGP09.phx.gbl...
> 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
>
>