Re: Query training -- Complex queries
From: Adam Machanic (amachanic_at_air-worldwide.nospamallowed.com)
Date: 02/25/04
- Next message: Geoff N. Hiten: "Re: Restore points available"
- Previous message: Rand Boyd [MSFT]: "RE: Copy Table + data from database A to database B"
- In reply to: DW: "Query training -- Complex queries"
- Next in thread: Erland Sommarskog: "Re: Query training -- Complex queries"
- Messages sorted by: [ date ] [ thread ]
Date: Wed, 25 Feb 2004 17:06:57 -0500
You might want to check out _Advanced Transact-SQL For SQL Server_ by Itizik
Ben-Gan and Tom Moreau. Pretty good book.
As for your queries, the first one filters the rows to update with the JOIN.
The second one will update every single row in the PositionsEOM table once
for every row returned in the derived table, as there is no correlation
between the two tables (you have not JOINed them.)
"DW" <None> wrote in message news:u#t6Wj##DHA.3272@TK2MSFTNGP09.phx.gbl...
> 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
- Next message: Geoff N. Hiten: "Re: Restore points available"
- Previous message: Rand Boyd [MSFT]: "RE: Copy Table + data from database A to database B"
- In reply to: DW: "Query training -- Complex queries"
- Next in thread: Erland Sommarskog: "Re: Query training -- Complex queries"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
