UPDATE a table with aggregate results for multiple columns

[eighthman11]

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.
Thanks for any help.

UPDATE #MyTable
SET HireDate=(Selec t Min(Case When Code = 'OHDATE' then DateChanged
else null end)
From HREH
Where #MyTable.HRCo=H REH.HRCo and
#MyTable.HRRef= HREH.HRRef ),
TerminationDate =(select Max(Case When Type = 'N' then
DateChanged else null end)
From HREH
Where #MyTable.HRCo=H REH.HRCo and
#MyTable.HRRef= HREH.HRRef ),
ReHireDate=(sel ect MAX(Case When Code = 'HIRE' then
DateChanged else null end)
From HREH
Where #MyTable.HRCo=H REH.HRCo and #MyTable.HRRef= HREH.HRRef )

[jim_geissman@countrywide.com]

Re: UPDATE a table with aggregate results for multiple columns

You might get more speed by creating a temp table (or subquery)
from HREH for each HRCo and HRRef pair, populated with MIN(),
MAX() and MAX(), with GROUP BY HRCo and HRRef. Then update
#MyTable from that, which has a single row per case. This may
depend on the relative sizes of HREH and #MyTable. If HREH has
a lot more cases than #MyTable, the temp table or subquery could
be from the join of the the two, to limit it to the cases present in
#MyTable.

Jim

[Erland Sommarskog]

Re: UPDATE a table with aggregate results for multiple columns

eighthman11 (rdshultz@noote r.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.TerminationDa te,
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@sommarsk og.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