Query training -- Complex queries

From: DW (None)
Date: 02/25/04 

Date: Wed, 25 Feb 2004 13:46:33 -0800

Can anyone please suggest a book (or a training class) that gets into
the guts of complex queries? I need to understand, for example, the
internal differences between this update query:

Update PositionsEOM
Set MktVal = PM.Price * PositionsEOM.Quantity_ASD
>From PositionsEOM Inner Join [Price Master] As PM On
    PositionsEOM.Fund_ID = PM.Fund_ID And
    PositionsEOM.Position_Date = PM.Price_Date

and this one:

Update PositionsEOM
Set MktVal = Derived.MktVal From
(Select PM.Price * PositionsEOM.Quantity_ASD As MktVal
>From PositionsEOM Inner Join [Price Master] As PM On
    PositionsEOM.Fund_ID = PM.Fund_ID And
    PositionsEOM.Position_Date = PM.Price_Date) As Derived

The second example ran for 20 minutes and I cancelled it; the first one
finished in 2.5 minutes. You wouldn't KNOW that you could write "Set"
expressions like the first example, from ANY of the online
documentation. Even though the second example uses a derived table, I
don't see much of a logical difference between them, and would not
expect the run time to be different.

Also, I decided that when you mix inner and outer joins in a select,
which I do sometimes, if you change the order, the result set WILL be
different (not just the time it takes). (I think this is true...) When
you build complicated select statements in Enterprise Manager's view
designer window, it doesn't really take this into account (you can say
"Select all rows from tableA", but you might really be getting all rows
from the result of the inner join on tableA and tableB).

I found one online article that mentioned that if you mix inner and
outer joins in a query, you CAN control the order of the joins -- and it
shows various ways to do that, including parentheses and derived or
virtual tables. But only ONE online article. It's VERY rare to see any
of this documented, and SQL Books Online sure doesn't show
examples.........

The examples for Update queries usually show that you can set price to
price*1.05, and helpfully explains that this will increase all prices by
5 percent. That's trivial. Then they move on to other subjects...

I don't know where to come across such learning; SQL Books Online sure
doesn't go into these kinds of examples. Their syntax diagrams are
probably complete, but of course they are huge and somewhat hard to
understand.

Any help will be greatly appreciated.

David Walker

Loading