November 12, 2019 at 2:31 am
In the below SQL statement (also attached as DOC file) it has been identified that this particular top part of the statement (highlighted in Grey in the attachment; starting with IF .. and before BEGIN) is responsible for the attached very high number of reads.
And this particular part runs the slowest, taking 22 minutes.
Any ideas of how to best rewrite it for better performance? 2 attachments.
IF 0 < (SELECT COUNT(*)
FROM #GRatings WITH(NOLOCK)
WHERE o_RTG = -1
AND iFromElig = 'Y') AND NOT EXISTS
(SELECT 1
FROM #GRatings X WITH(NOLOCK)
JOIN GTree GT WITH (NOLOCK)
ON X.i_group_gid = GT.Child_gid
JOIN Bi_Pa_As BPA WITH (NOLOCK)
ON BPA.entity_gid IN (GT.Child_gid, GT.Super_gid, GT.Parent_gid)
WHERE BPA.rs = 'A')
BEGIN
/*<DocGen_Nested_SP>usp……</DocGen_Nested_SP>*/
EXEC uspGetGDefaultRating @oRatingTableGID = @lDefaultRatingGID OUTPUT
UPDATE #GRatings
SET o_RTG = @lDefaultRatingGID
,RateLocation = 'Z'
WHERE o_RTG = -1
AND iFromElig = 'Y';
END
Likes to play Chess
November 12, 2019 at 2:39 am
All indexes are tuned to their best (for the current statement) so I am specifically looking for rewriting this IF statement.
May be 2 or 3 IFs are better? like populating a variable in each of them Y/N and then execute the main part of code within another IF only comparing the values of variables , not running any complicated statement inside same IF but in separate IFs. This is the most obvious I can think of at the moment. Entirely getting rid from the entire IF statement is another consideration that comes to mind.
Yet may be it can be re-written/reshaped better in a certain obvious to someone way to improve its performance?
Likes to play Chess
November 12, 2019 at 4:27 am
I would start by trying to replace
IF 0 < (SELECT COUNT(*)
with
IF EXISTS (SELECT 1
Next check your statistics. the massive disparity between estimated and actual rows points to bad stats.
November 12, 2019 at 8:43 am
Please post an actual (as opposed to estimated) execution plan as a .plan attachment. You can use your plan interpreter to mask real table names if necessary. Do this after updating statistics, as suggested by Des.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
November 12, 2019 at 1:29 pm
Should I update statistics first on all tables participating in the joins? including the #temp table?
Likes to play Chess
November 12, 2019 at 1:33 pm
You can rewrite the SQL as this:
IF EXISTS(SELECT *
FROM #GRatings
WHERE o_RTG = -1
AND iFromElig = 'Y') BEGIN
IF NOT EXISTS(SELECT *
FROM #GRatings X
INNER JOIN GTree GT
ON X.i_group_gid = GT.Child_gid
INNER JOIN Bi_Pa_As BPA
ON BPA.rs = 'A'
AND BPA.entity_gid = GT.Child_gid) BEGIN
IF NOT EXISTS(SELECT *
FROM #GRatings X
INNER JOIN GTree GT
ON X.i_group_gid = GT.Child_gid
INNER JOIN Bi_Pa_As BPA
ON BPA.rs = 'A'
AND BPA.entity_gid = GT.Super_gid) BEGIN
IF NOT EXISTS(SELECT *
FROM #GRatings X
INNER JOIN GTree GT
ON X.i_group_gid = GT.Child_gid
INNER JOIN Bi_Pa_As BPA
ON BPA.rs = 'A'
AND BPA.entity_gid = GT.Parent_gid) BEGIN
/*<DocGen_Nested_SP>prGetGlobalDefaultRateTable</DocGen_Nested_SP>*/
EXEC uspGetGDefaultRating @oRatingTableGID=@lDefaultRatingGID OUTPUT
UPDATE #GRatings
SET o_RTG = @lDefaultRatingGID,
RateLocation = 'Z'
WHERE o_RTG = -1
AND iFromElig = 'Y';
END
END
END
END
If you can identify the slow statements you can then add appropriate indexes.
November 12, 2019 at 3:54 pm
All indexes are tuned to their best (for the current statement)
I'd rather see the actual indexes and the actual query plan for myself.
For example, how many rows are in #GRatings and what index(es) exist on it?
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
November 12, 2019 at 5:07 pm
Really looks nice and makes sense. Thanks!
Will be testing it today. Does it make difference if I do 'Select 1 instead of Select * in each EXISTS(...... subquery?
or Select Top 1 ...
Likes to play Chess
November 12, 2019 at 9:20 pm
The partial execution plan picture you attached only shows that the entire table dbo.GroupTree is being read. We don't see GroupTree in any of the code you've shown us, only: #GRatings, GTree, Bi_Pa_As
If dbo.GroupTree is the real name of the GTree you've shown us in code, then Jonathan's recommendation may help, but without knowing the indexes on the tables involved it's difficult to tell. Is there a separate index on each of these columns: Child_gid, Super_gid, Parent_gid?
We also cannot tell which table it's trying to join to first since we can't see the execution plan. Can the execution plan be given to us as a .sqlplan or .pesession file?
November 14, 2019 at 3:20 pm
Just a thought, but instead of
IF NOT EXISTS(SELECT * ... or IF EXISTS(SELECT * ... , why not use Top 1? all you need to know is only 1 exists, you don't care if 5million exist.
Something like
IF NOT EXISTS(SELECT top 1 1 ... or IF EXISTS(SELECT top 1 1
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
November 14, 2019 at 3:27 pm
Just a thought, but instead of
IF NOT EXISTS(SELECT * ... or IF EXISTS(SELECT * ... , why not use Top 1? all you need to know is only 1 exists, you don't care if 5million exist.
Something like
IF NOT EXISTS(SELECT top 1 1 ... or IF EXISTS(SELECT top 1 1
I think the optimiser will do something like this already with the queries provided.
November 14, 2019 at 3:56 pm
If some conditions are met...
https://sqlperformance.com/2018/02/sql-plan/setting-and-identifying-row-goals
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
November 14, 2019 at 6:34 pm
An existence test and a TOP 1 both stop as soon as they find a row that satisfies the WHERE conditions. Run the following code. Although execution plans differ slightly, the logical reads against the test file will be the same whether using EXISTS or TOP(1). This is true whether or not an appropriate index exists, or if it is a simple table scan.
use Master
go
select top(100000)
N = row_number() over(order by (select null))
, Filler = 'Yadayadayadayadayadayadayadayadayadayadayadayadayadayadayadayada'
into test
from sys.columns s1
cross join sys.columns s2
create unique clustered index PK_Test on Test(N)
set statistics time,io on;
select 'Existence'
where exists (select 1 from test where N = 30000)
select top (1) 'Top 1'
from test where N = 30000
set statistics time,io off;
drop table test
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply