January 6, 2016 at 11:51 pm
I was given this query to tune and believe there's a better way to do it using a set-based operation but for the life of me (:blush:) I'm embarrassed to say that I just can't figure it out. The query basically fetches account IDs from a table and inserts them into a temp table, then uses a loop to fetch the most recent activity date from other table for a given account ID, then updates the temp table with the date. I can see the RBAR and have tried to read up on Jeff Moden's articles, but for the life of me I can't seem to understand it enough to "APPLY" it to this situation - without making me feel like a complete idiot, could anyone help me figure this out?
The tt.mid is an account ID from the TTable which should link to the AcctID in the temporary table
Code has been modified to use all temp tables with some sample data. In the real scenario these tables have millions and millions of rows
/* Create & Populate Accounts */
CREATE TABLE #Accounts (ID int IDENTITY(1,1), AcctID int, StatusChangeDate DATETIME)
INSERT INTO #Accounts (AcctID)
VALUES (5011),(5012),(5060),(5067),(5080),(5090),(5092),(5097),(5101),(5104)
/* Create & Populate The CCTable */
CREATE TABLE #CCTable (LutID varchar(15), LutCode int)
INSERT INTO #CCTable (LutID, LutCode) VALUES
('SomeValue', 1053),
('SomeValue', 1065),
('SomeValue', 1066),
('SomeValue', 1096),
('SomeValue', 1098),
('SomeValue', 1100),
('SomeValue', 1105),
('SomeValue', 1111),
('SomeValue', 1162)
/* Create & Populate The Main Table (Millions of rows) */
CREATE TABLE #TTable (mtid int, mid int, tpyValue int, tpyDE varchar(25), tpyModifiedDate datetime)
INSERT INTO #TTable (mtid, mid, tpyValue, tpyDE, tpyModifiedDate)
VALUES
(51,5080,1053,'SomeOtherValue','2010-05-14 14:16:44.000'),
(51,5060,1066,'SomeOtherValue','2013-04-02 16:01:32.000'),
(51,5060,1065,'SomeOtherValue','2013-04-03 15:45:44.000'),
(51,5090,1111,'SomeOtherValue','2013-05-11 10:17:59.000'),
(51,5080,1096,'SomeOtherValue','2013-11-18 13:58:11.000'),
(51,5012,1066,'SomeOtherValue','2014-02-09 00:15:49.000'),
(51,5067,1066,'SomeOtherValue','2014-02-09 00:15:50.000'),
(51,5012,1098,'SomeOtherValue','2014-02-13 00:18:22.000'),
(51,5067,1098,'SomeOtherValue','2014-02-13 00:18:23.000'),
(51,5011,1066,'SomeOtherValue','2014-03-01 01:08:39.000'),
(51,5090,1111,'SomeOtherValue','2014-03-01 01:08:39.000'),
(51,5090,1066,'SomeOtherValue','2014-03-01 01:08:39.000'),
(51,5097,1066,'SomeOtherValue','2014-03-01 01:08:40.000'),
(51,5101,1066,'SomeOtherValue','2014-03-01 01:08:40.000'),
(51,5092,1066,'SomeOtherValue','2014-03-01 01:08:38.000'),
(51,5060,1105,'SomeOtherValue','2014-03-01 01:08:39.000'),
(51,5060,1066,'SomeOtherValue','2014-03-01 01:08:39.000'),
(51,5104,1066,'SomeOtherValue','2014-03-01 01:08:38.000'),
(51,5104,1098,'SomeOtherValue','2014-03-05 00:16:00.000'),
(51,5011,1098,'SomeOtherValue','2014-03-05 00:16:02.000')
/* Declare Variables */
DECLARE @MinRow int, @MaxRow INT, @AccountID INT, @StatusChangeDate DATETIME
SELECT @MinRow = MIN(ID), @MaxRow = Max(ID) FROM #Accounts
/* Fetch Most Recent StatusChangeDate from table so we can update the #Accounts Temp Table */
WHILE @MinRow <= @MaxRow
BEGIN
SET @AccountID = (SELECT AcctID FROM #Accounts WHERE ID = @MinRow)
SELECT TOP 1
@StatusChangeDate = tt.tpyModifiedDate
FROM #TTable AS tt
LEFT JOIN #CCTable AS ct
ON tt.[tpyValue] = ct.LutCode
AND ct.[LUTid] = 'SomeValue'
WHERE tt.mid = @AccountID
AND tt.mtid = 51
AND tt.tpyDE = 'SomeOtherValue'
ORDER BY tt.tpyModifiedDate DESC
UPDATE #Accounts
SET StatusChangeDate = @StatusChangeDate
WHERE AcctID = @AccountID
SET @MinRow = @MinRow +1
END
SELECT * FROM #Accounts
There has to be a way to do this so it performs well when they remove the SELECT TOP 100 and attempt to pull in the most recent modified date from the TTable which bypasses the need for a loop and a temp table altogether...The SELECT TOP 100 was removed from this example because the #Accounts table has been pre-loaded with only 10 rows for testing/illustrative purposes
Thanks in advance
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
January 7, 2016 at 1:55 am
Quick suggestion, just make certain that the right indices are in place.
😎
/* SELECT GIVING THE DESIRED OUTPUT */
SELECT
AC.ID
,AC.AcctID
,MAX(TT.tpyModifiedDate) AS tpyModifiedDate
FROM #Accounts AC
INNER JOIN #TTable TT
ON AC.AcctID = TT.mid
INNER JOIN #CCTable CT
ON TT.tpyValue = CT.LutCode
WHERE TT.mtid = 51
AND TT.tpyDE = 'SomeOtherValue'
AND CT.LutID = 'SomeValue'
GROUP BY AC.ID
,AC.AcctID;
/* #Accounts UPDATE STATEMENT */
;WITH BASE_DATA AS
(
SELECT
AC.ID
,AC.AcctID
,MAX(TT.tpyModifiedDate) AS tpyModifiedDate
FROM #Accounts AC
INNER JOIN #TTable TT
ON AC.AcctID = TT.mid
INNER JOIN #CCTable CT
ON TT.tpyValue = CT.LutCode
WHERE TT.mtid = 51
AND TT.tpyDE = 'SomeOtherValue'
AND CT.LutID = 'SomeValue'
GROUP BY AC.ID
,AC.AcctID
)
UPDATE ACU
SET ACU.StatusChangeDate = BD.tpyModifiedDate
FROM BASE_DATA BD
INNER JOIN #Accounts ACU
ON BD.AcctID = ACU.AcctID
;
SELECT
*
FROM #Accounts;
January 8, 2016 at 2:19 pm
Thanks a lot for this, had to create an index on the larger table been hit but it does work well!
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
January 8, 2016 at 11:08 pm
MyDoggieJessie (1/8/2016)
Thanks a lot for this, had to create an index on the larger table been hit but it does work well!
You are very welcome.
😎
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply