November 20, 2015 at 8:29 am
Just thought I'd toss in a CLUSTERED INDEX on the data, and see what the following looked like on execution plan:
--===== If it exists, drop the test table to make reruns easier in SSMS.
-- This is NOT a part of the solution.
IF OBJECT_ID('tempdb..#TestTable','U') IS NOT NULL
BEGIN
DROP TABLE #TestTable;
END
--===== Create the test table. This is NOT a part of the solution.
-- We're just creating test data here.
SELECT td.*
INTO #TestTable
FROM (
SELECT '3/31/2015',1,'Demo' UNION ALL
SELECT '4/30/2015',1,'Demo' UNION ALL
SELECT '5/31/2015',1,'New' UNION ALL
SELECT '3/31/2015',2,'Wrong' UNION ALL
SELECT '4/30/2015',2,'Wrong' UNION ALL
SELECT '5/31/2015',2,'Correct'
) AS td (asOfDate, identifier, class4);
CREATE UNIQUE CLUSTERED INDEX IX_TestTable_identifier_asOfDate ON #TestTable
(
identifier ASC,
asOfDate DESC
);
--===== Show the "BEFORE" content of the table.
SELECT *
FROM #TestTable;
-- DO THE UPDATE
WITH MAX_VALUES AS (
SELECT TT.identifier, MAX(TT.asOfDate) AS MAX_DATE
FROM #TestTable AS TT
GROUP BY TT.identifier
)
UPDATE T
SET T.class4 = T2.class4
FROM #TestTable AS T
INNER JOIN MAX_VALUES AS MV
ON T.identifier = MV.identifier
INNER JOIN #TestTable AS T2
ON MV.MAX_DATE = T2.asOfDate
AND MV.identifier = T2.identifier
WHERE T.asOfDate <> T2.asOfDate;
--===== Show the "AFTER" content of the table.
SELECT *
FROM #TestTable;
I get 2 clustered index seeks, a clustered index scan, a stream aggregate, two nested loops, a top, an eager spool (Table Spool), and a clustered index update on just the 4 rows that need it. As I don't have a 2012 instance to test with, I couldn't use anything but 2008 R2 code. Let me know how well that compares.... I'll look at it as a learning exercise.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
November 20, 2015 at 11:07 am
Gotta stick my 2 cents in - how about this?
update #testTable set class4 = d.class4 from
#testTable a inner join
( select identifier,class4 from
(select identifier,max(asofDate) asOfDate from #testTable group by identifier ) b
outer apply (select class4 from #TestTable c where c.identifier = b.identifier and c.asOfDate = b.asofdate) c) d on d.identifier = a.identifier
select * from #testtable
Viewing 2 posts - 16 through 16 (of 16 total)
You must be logged in to reply to this topic. Login to reply