Posted by Erland Sommarskog on 01/08/07 22:52

eighthman11 (rdshultz@nooter.com) writes:
> Hi everyone. I am updating a table with aggregate results for multiple
> columns. Below is an example of how I approached this. It works fine
> but is pretty slow. Anyone have an idea how to increase performance.

This sort of queries usually run faster if you rewrite them into
the proprietary UPDATE FROM:


UPDATE #MyTable
SET Hiredate = h.Hiredate,
TerminationDate = h.TerminationDate,
ReHireDate = h.ReHireDate
FROM #MyTable m
JOIN (SELECT HRCo, HRRef,
HireDate = Min(CASE Code WHEN 'OHDATE' THEN DateChanged END),
TerminationDate = MIN(CASE Type WHEN 'N' THEN DateChanged END),
ReHireDate = MAX(CASE Code WHEN 'HIRE' THEN DateChanged END)
FROM HREH
GROUP BY HrCo, HRRef) AS h ON m.HRCo = h.HRCo
AND m.HRRef = h.HRRef

The thing in parentheses is a derived table. Think of this as a logical
temp table. It is not materialized, and the computation order can very
well be different. Derived tables are part of ANSI SQL. What is
proprietary, and thus not portable, is the use of FROM-JOIN in UPDATE.



--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]