I am looking to optimize this T-SQL Statement (it generates very High Reads..)

  • VoldemarG

    Hall of Fame

    Points: 3514

     

    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

     

    Attachments:
    You must be logged in to view attached files.


    Voldemar
    likes to play chess

  • VoldemarG

    Hall of Fame

    Points: 3514

    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?


    Voldemar
    likes to play chess

  • DesNorton

    SSC-Insane

    Points: 22848

    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.

  • ChrisM@Work

    SSC Guru

    Points: 186054

    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.

     

    [font="Arial"]“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw[/font]


    For fast, accurate and documented assistance in answering your questions, please read this article[/url].
    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]
    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
    [url

  • VoldemarG

    Hall of Fame

    Points: 3514

    Should I update statistics first on all tables participating in the joins? including the #temp table?


    Voldemar
    likes to play chess

  • Jonathan AC Roberts

    SSCoach

    Points: 16994

    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.

  • ScottPletcher

    SSC Guru

    Points: 98206

    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) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them.

  • VoldemarG

    Hall of Fame

    Points: 3514

    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 ...


    Voldemar
    likes to play chess

  • Jonathan AC Roberts

    SSCoach

    Points: 16994

    VoldemarG wrote:

     Does it make difference if I do  'Select 1 instead of Select * in each EXISTS(......  subquery?

    or Select Top 1 ...

    It makes no difference.

  • Chris Harshman

    SSC-Forever

    Points: 41845

    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?

     

  • Mike01

    SSChampion

    Points: 11071

    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/

  • Jonathan AC Roberts

    SSCoach

    Points: 16994

    Mike01 wrote:

    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.

  • ChrisM@Work

    SSC Guru

    Points: 186054

    If some conditions are met...

    https://sqlperformance.com/2018/02/sql-plan/setting-and-identifying-row-goals

    [font="Arial"]“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw[/font]


    For fast, accurate and documented assistance in answering your questions, please read this article[/url].
    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]
    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
    [url

  • The Dixie Flatline

    SSC Guru

    Points: 53231

    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 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply