July 8, 2017 at 2:33 am
I have update query which I have modified to insert and update.Please let me know if this query is correct or it is going into infinite loop.
Orginal query:
UPDATE #BL_15_STATEMENT
SET am_quantity = cm.am_trd,
pr_trd = cm.pr_repr
FROM COMPONENT_MONITOR cm,
#BL_6_ACCR_COMPONENT b6ac ,
#COMPONENT_MONITOR cmt
WHERE #BL_15_STATEMENT.id_cnt_typ = "R"
AND #BL_15_STATEMENT.id_cnt = b6ac.id_cnt
AND #BL_15_STATEMENT.id_orig = b6ac.id_orig
AND b6ac.id_typ_imnt_cmpnt = "SEC"
AND b6ac.id_cmpnt = cm.id_cmpnt
AND AND cm.dt_start = (SELECT Max(cm2.dt_start) FROM COMPONENT_MONITOR cm2
WHERE cm2.id_cmpnt = b6ac.id_cmpnt
AND cm2.dt_start <= #BL_15_STATEMENT.dt_val)
Transformed query:
CREATE TABLE #COMPONENT_MONITOR
(id_cmpnt int,
dt_start datetime)
INSERT INTO #COMPONENT_MONITOR
SELECT cm2.id_cmpnt,Max(cm2.dt_start)
FROM COMPONENT_MONITOR cm2 ,#BL_15_STATEMENT
WHERE cm2.dt_start <= #BL_15_STATEMENT.dt_val
UPDATE #BL_15_STATEMENT
SET am_quantity = cm.am_trd,
pr_trd = cm.pr_repr
FROM COMPONENT_MONITOR cm,
#BL_6_ACCR_COMPONENT b6ac, --(index IX_cnt1)
#COMPONENT_MONITOR cmt
WHERE #BL_15_STATEMENT.id_cnt_typ = "R"
AND #BL_15_STATEMENT.id_cnt = b6ac.id_cnt
AND #BL_15_STATEMENT.id_orig = b6ac.id_orig
AND b6ac.id_typ_imnt_cmpnt = "SEC"
AND b6ac.id_cmpnt = cm.id_cmpnt
AND cm.dt_start = cmt.dt_start
AND cm.id_cmpnt = cmt.id_cmpnt
July 8, 2017 at 7:02 am
An update statement can't go into an infinite loop, not by itself. To get an infinite loop, you need a loop, ie a WHILE or a procedure that calls itself
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 10, 2017 at 6:59 am
savibp3 - Saturday, July 8, 2017 2:33 AMI have update query which I have modified to insert and update.Please let me know if this query is correct or it is going into infinite loop.Orginal query:
UPDATE #BL_15_STATEMENT
SET am_quantity = cm.am_trd,
pr_trd = cm.pr_repr
FROM COMPONENT_MONITOR cm,
#BL_6_ACCR_COMPONENT b6ac ,
#COMPONENT_MONITOR cmt
WHERE #BL_15_STATEMENT.id_cnt_typ = "R"
AND #BL_15_STATEMENT.id_cnt = b6ac.id_cnt
AND #BL_15_STATEMENT.id_orig = b6ac.id_orig
AND b6ac.id_typ_imnt_cmpnt = "SEC"
AND b6ac.id_cmpnt = cm.id_cmpnt
AND AND cm.dt_start = (SELECT Max(cm2.dt_start) FROM COMPONENT_MONITOR cm2
WHERE cm2.id_cmpnt = b6ac.id_cmpnt
AND cm2.dt_start <= #BL_15_STATEMENT.dt_val)Transformed query:
CREATE TABLE #COMPONENT_MONITOR
(id_cmpnt int,
dt_start datetime)
INSERT INTO #COMPONENT_MONITOR
SELECT cm2.id_cmpnt,Max(cm2.dt_start)
FROM COMPONENT_MONITOR cm2 ,#BL_15_STATEMENT
WHERE cm2.dt_start <= #BL_15_STATEMENT.dt_val
UPDATE #BL_15_STATEMENT
SET am_quantity = cm.am_trd,
pr_trd = cm.pr_repr
FROM COMPONENT_MONITOR cm,
#BL_6_ACCR_COMPONENT b6ac, --(index IX_cnt1)
#COMPONENT_MONITOR cmt
WHERE #BL_15_STATEMENT.id_cnt_typ = "R"
AND #BL_15_STATEMENT.id_cnt = b6ac.id_cnt
AND #BL_15_STATEMENT.id_orig = b6ac.id_orig
AND b6ac.id_typ_imnt_cmpnt = "SEC"
AND b6ac.id_cmpnt = cm.id_cmpnt
AND cm.dt_start = cmt.dt_start
AND cm.id_cmpnt = cmt.id_cmpnt
Your code is in the form of what I refer to as an "illegal joined UPDATE". If you look carefully, the target of the update is being used as a join in the WHERE clause but it is NOT in the FROM clause. The reason I call it "illegal" is because you won't find such an example in any of the MS documentation on UPDATE anywhere ever. I've never done a deep dive on it to figure out the exact mechanism for why such updates take so long but I have run into the problem several times. The fix is always the same... refactor the code to be a legal form of update. My first experience with this problem was on an 8 CPU server (long time ago) and it slammed 4 of the CPU's into the wall for two hours before the DBA killed it. Once we fixed the query, it took less than 2 seconds. Similar results have been realized every time I've suggested someone "legalize" their UPDATE.
To state the rule which MS has never actually stated but has always implied in the examples they provide, the target of the update must always appear in the FROM clause when joining to another table. Along with that, you should seriously consider using the ANSI join methods instead of the ol' "equi-join" method that you're using. The general "rules" there are that the join criteria go in ON clauses and the filtering goes in the WHERE clause. (There are exceptions when certain requirements of OUTER JOINs need to be realized).
Hoping that I didn't duff it when I refactored it but, following the rules I just stated, your UPDATE statement should take the following form. Notice that we're also updating the ALIAS of the target table just to keep life simple.
UPDATE bl15
SET am_quantity = cm.am_trd,
pr_trd = cm.pr_repr
FROM #BL_15_STATEMENT bl15
JOIN #BL_6_ACCR_COMPONENT b6ac ON b6ac.id_cnt = bl15.id_cnt
AND b6ac.id_orig = bl15.id_orig
JOIN COMPONENT_MONITOR cm ON cm.id_cmpnt = b6ac.id_cmpnt
JOIN #COMPONENT_MONITOR cmt ON cmt.dt_start = cm.dt_start
AND cmt.id_cmpnt = cm.id_cmpnt
WHERE #BL_15_STATEMENT.id_cnt_typ = "R"
AND b6ac.id_typ_imnt_cmpnt = "SEC"
;
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply