April 10, 2023 at 3:17 pm
hello,
I am having a major performance issue ,
with this part of code which is still blocked on my server , Attached is the part of the code that is causing the problem.
DELETE R FROM #RESULTS_DEM AS R
INNER JOIN dbo.Dem_trans AS DEM
ON DEM.NUM_DEM = R.NUM_DEM
WHERE ( NOT EXISTS (
SELECT TOP 1 1
FROM #LIST_STA_DEM AS W LEFT OUTER JOIN dbo.DT_INT AS D ON W.COD_TRA_CLI=D.COD_TRA
WHERE ((W.COD_TRA_CLI='010100' OR DEM.NUM_DEM=D.NUM_DEM OR DEM.COD_TRA_CLI=W.COD_TRA_CLI ) AND W.STA_DEM = DEM.STA_DEM)
)) */
To solve this problem I thought of replacing the variable table with a temporary table and putting an index on the COD_TRA_CLI and STA_DEM column
DELETE R
FROM #RESULTS_DEM AS R
INNER JOIN dbo.Dem_trans AS DEM ON DEM.NUM_DEM = R.NUM_DEM
WHERE NOT EXISTS
(
SELECT TOP 1 1
FROM #LIST_STA_DEM AS W
LEFT OUTER JOIN dbo.DT_INT AS D ON W.COD_TRA_CLI = D.COD_TRA
WHERE W.COD_TRA_CLI = '010100' AND W.STA_DEM = DEM.STA_DEM
);
DELETE R
FROM #RESULTS_DEM AS R
INNER JOIN dbo.Dem_trans AS DEM ON DEM.NUM_DEM = R.NUM_DEM
WHERE NOT EXISTS
(
SELECT TOP 1 1
FROM #LIST_STA_DEM AS W
LEFT OUTER JOIN dbo.DT_INT AS D ON W.COD_TRA_CLI = D.COD_TRA
WHERE DEM.NUM_DEM = D.NUM_DEM AND W.STA_DEM = DEM.STA_DEM
);
DELETE R
FROM #RESULTS_DEM AS R
INNER JOIN dbo.Dem_trans AS DEM ON DEM.NUM_DEM = R.NUM_DEM
WHERE NOT EXISTS
(
SELECT TOP 1 1
FROM #LIST_STA_DEM AS W
LEFT OUTER JOIN dbo.DT_INT AS D ON W.COD_TRA_CLI = D.COD_TRA
WHERE DEM.COD_TRA_CLI = W.COD_TRA_CLI AND W.STA_DEM = DEM.STA_DEM
);
Here is the execution plan :https://www.brentozar.com/pastetheplan/?id=r18tQvLCi
thanks for your help
April 11, 2023 at 12:50 am
Not sure if this would help but it might be worth a try depending on the amount of data this would generate instead of doing a WHERE NOT EXISTS -- create a temporary table with all the negative conditions as follows -- note I could not test this but if not correct, I am sure you can get my meaning so could modify it to work. The benefit here is you can then anlyze a bit more closely what the issue is as you have separated the two elements completely and who knows after you have done this you might even find where the original issue was.
CREATE TABLE #DemNotIncluded
( NUM_DEM TYPE NOT NULL
,PRIMARY KEY ( NUM_DEM )
);
INSERT INTO #DemNotIncluded
WITH (TABLOCKX)
SELECT [dt].[NUM_DEM]
FROM [dbo].[DemTrans] AS [dt]
------
INNER JOIN #ListStaDem AS [lsd]
ON [lsd].[STA_DEM] = [dt].[STA_DEM]
------
LEFT JOIN dbo.DT_INT AS [di]
ON [di].[COD_TRA] = [lsd].[COD_TRA_CLI]
------
-- Be sure to arrange the ORs in the most likely to least likely to occur order
WHERE [lsd].[COD_TRA_CLI] = '010100'
OR [dt].[COD_TRA_CLI] = [lsd].[COD_TRA_CLI]
OR [dt].[NUM_DEM] = [di].[NUM_DEM]
DELETE [rd]
FROM #ResultsDem AS [rd]
WHERE [rd].[NUM_DEM] IN ( SELECT * FROM #DemNotIncluded )
April 11, 2023 at 1:45 pm
Why is this left join used in your "WHERE NOT EXISTS " parts?
LEFT OUTER JOIN dbo.DT_INT AS D
ON W.COD_TRA_CLI = D.COD_TRA
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
April 11, 2023 at 7:20 pm
Why is this left join used in your "WHERE NOT EXISTS " parts?
LEFT OUTER JOIN dbo.DT_INT AS D
ON W.COD_TRA_CLI = D.COD_TRA
I belive because they are pulling back the COD_TRA_CLI value from the dbo.DT_INT table. My guess is that it does not always exist within that table for every record which is usually why one uses a LEFT JOIN but I could be wrong.
April 12, 2023 at 5:53 am
This was removed by the editor as SPAM
April 12, 2023 at 9:57 am
Johan Bijnens wrote:Why is this left join used in your "WHERE NOT EXISTS " parts?
LEFT OUTER JOIN dbo.DT_INT AS D
ON W.COD_TRA_CLI = D.COD_TRAI belive because they are pulling back the COD_TRA_CLI value from the dbo.DT_INT table. My guess is that it does not always exist within that table for every record which is usually why one uses a LEFT JOIN but I could be wrong.
in the "exists" -clause, it does not make a difference to the results of the query used in the exists-clause !
So it should at least be removed from the query in the exists-clause.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
April 12, 2023 at 2:37 pm
Try inserting the rows that need to be deleted into a temporary table then another step to delete the rows:
SELECT DEM.NUM_DEM
INTO #RESULTS_TO_DELETE
FROM dbo.Dem_trans AS DEM
WHERE NOT EXISTS(SELECT *
FROM #LIST_STA_DEM AS W
LEFT JOIN dbo.DT_INT AS D
ON W.COD_TRA_CLI=D.COD_TRA
WHERE (W.COD_TRA_CLI='010100'
OR DEM.NUM_DEM = D.NUM_DEM
OR DEM.COD_TRA_CLI = W.COD_TRA_CLI)
AND W.STA_DEM = DEM.STA_DEM
)
;
DELETE R
FROM #RESULTS_DEM AS R
WHERE EXISTS(SELECT *
FROM #RESULTS_TO_DELETE D
WHERE D.NUM_DEM = R.NUM_DEM)
;
You can then look at optimising the initial SELECT statement by adding indexes, rewriting the statement etc...
April 12, 2023 at 8:56 pm
Dennis Jensen wrote:Johan Bijnens wrote:Why is this left join used in your "WHERE NOT EXISTS " parts?
LEFT OUTER JOIN dbo.DT_INT AS D
ON W.COD_TRA_CLI = D.COD_TRAI belive because they are pulling back the COD_TRA_CLI value from the dbo.DT_INT table. My guess is that it does not always exist within that table for every record which is usually why one uses a LEFT JOIN but I could be wrong.
in the "exists" -clause, it does not make a difference to the results of the query used in the exists-clause ! So it should at least be removed from the query in the exists-clause.
Okay I am not understanding your statement because if you were to remove that LEFT JOIN from within the SELECT statement used within the WHERE NOT EXISTS clause -- you would most likely get a different and incorrect result set. Which I do not see helps the OP in anyway.
That being said that LEFT JOIN, if it is some kind of issue, is fairly minor in comparison to the query in its totality and what the OP is attempting to do, at least from my current perspective. So again I do not see how this would help the OP?
However, if perhaps you could explain your point with a bit more detail as to why you feel the way you do about it, even if it does not help the OP I might learn something and that would be awesome.
April 12, 2023 at 9:13 pm
on the original query the left outer join was being used
DELETE R FROM #RESULTS_DEM AS R
INNER JOIN dbo.Dem_trans AS DEM
ON DEM.NUM_DEM = R.NUM_DEM
WHERE ( NOT EXISTS (
SELECT TOP 1 1
FROM #LIST_STA_DEM AS W LEFT OUTER JOIN dbo.DT_INT AS D ON W.COD_TRA_CLI=D.COD_TRA
WHERE ((W.COD_TRA_CLI='010100' OR DEM.NUM_DEM=D.NUM_DEM OR DEM.COD_TRA_CLI=W.COD_TRA_CLI ) AND W.STA_DEM = DEM.STA_DEM)
)) */
on the 3 queries the OP did trying to solve the issue the left outer join was left as a residual from the original. - on those, query 1 and query 3 DO NOT use table dbo.DT_INT at all (this is the table on the left outer ) - as such that join can be removed as it is not being used, and the query engine is likely removing it from the equation on its own.
but it seems the OP has left the building - would be interested to see what your first reply does to performance - that was going to be my suggestion as well. (e.g. use a temp table to hold what is to be deleted up front and do a straight delete afterwards.
April 12, 2023 at 11:29 pm
on the original query the left outer join was being used
on the 3 queries the OP did trying to solve the issue the left outer join was left as a residual from the original.
Thanks for that explanation, I thought you were referring to the original query or the query presented just before your post. It was not obvious you were talking about 2 of the OP's 3 subsequent queries. Good catch though.
Note I only glanced at those 3 alternate queries as they seemed a bit of overkill for the issue at hand and everything of importance was included in that first query.
April 13, 2023 at 8:45 am
This was removed by the editor as SPAM
April 18, 2023 at 7:08 am
This was removed by the editor as SPAM
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply