Is it possible to optimize this SQL to get rid of 2 highlighted subqueries?

  • Is it possible to optimize this statement to get rid of the case named [aging]?

    specifically, the two highlighted statements that cause 90% of all waits in the entire select statement.

    4 attachments: entire SP, the CULPRIT SQL, and the full Stored Proc where it is called +screenshot that shows fully confirmed problem statements that cause 90 % of all waits here.  That subquery is executed 300K times (number of resultset rows). both tables are over 100 000 000 rows and 300 columns.

    THANK YOU!

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

    Voldemar likes to play CHESS (and IS good at it!)

  • Here is the query plan.

    Voldemar likes to play CHESS (and IS good at it!)

  • This is what we came up with so far.

    But it is not confirmed that I will have a list of ClaimNumbers to pre-populate like this...

     

     

    /* --------------- D.A- Solution starts here ------------------*/

    declare @claim_number int,
    @max_paid_date datetime,
    @min_date_submitted datetime,
    @min_date_submitted_X datetime

    set
    @claim_number = 1123456 -- Claim number needs to be assigned from the previous statement

    SELECT
    @max_paid_date = MAX(MaxPdDate1.paid_date)
    FROM
    Application.dbo.Claims_Log_V2 MaxPdDate1 (NOLOCK)
    WHERE
    MaxPdDate1.claim_number = @claim_number

    SELECT
    @min_date_submitted_X = MIN(date_submitted) -- aging = difference between
    FROM
    Application.dbo.Claims_Log_V2 MinDtSub (NOLOCK) -- (1st adjustment after the last Check-Run date)
    WHERE
    MinDtSub.claim_number = @claim_number -- and (current date)
    AND MinDtSub.date_submitted >= @max_paid_date
    AND SUBSTRING(MinDtSub.support_codes, 8, 1) = 'X'

    SELECT
    @min_date_submitted = MIN(date_submitted) -- if claim has been through a check-run but has
    FROM
    Application.dbo.Claims_Log_V2 MinDtSub (NOLOCK) -- no adjustments, then aging = difference between
    WHERE
    MinDtSub.claim_number = @claim_number -- (1st activity of any kind after the last Check-Run date)
    AND MinDtSub.date_submitted >= @max_paid_date

    /* --------------- - Solution ends here ------------------*/


    SELECT
    DISTINCT C.user_id,
    C.date_submitted,
    gl.[group_name] AS client,
    C.claim_number,
    C.service_date,
    C.receive_date,
    SUBSTRING(C.support_codes, 8, 1),
    SUM(C.submitted_cost)
    /*-------------------------- Aging Logic ---------------------------------------------------------------------*/
    /* */
    /* WHEN claim has paid more than once */
    /* WHEN there was an adjustment (status = X) after the last check date */
    /* THEN aging = (reporting date) minus (1st adjustment date after the last paid date) */
    /* ELSE */
    /* claim was pended without an adjustment (this is procedurally wrong, but occasionally happens */
    /* aging = (reporting date) minus (1st date of any kind of activity after the last paid date) */
    /* ELSE (claim has never been through the Check-Run */
    /* aging = (reporting date) minus (received date) */
    /*----------------------------------------------------------------------------------------------------------------*/,
    CASE
    WHEN (
    SELECT
    COUNT (DISTINCT(countPdDt.paid_date)) -- if there are any paid dates indicating
    FROM
    Application.dbo.Claims_Log_V2 countPdDt (NOLOCK) -- that the claim has been through a
    WHERE
    countPdDt.claim_number = C.claim_number -- Check-Run
    AND countPdDt.paid_date > '1/1/1901'
    AND countPdDt.paid_date < '12/31/9999'
    ) > 0 THEN CASE
    WHEN (
    SELECT
    COUNT(SUBSTRING(stat.support_codes, 8, 1)) -- if the claim has any
    FROM
    Application.dbo.Claims_Log_V2 stat (NOLOCK) -- any adjustments
    WHERE
    stat.claim_number = C.claim_number
    AND SUBSTRING(stat.support_codes, 8, 1) = 'X'
    ) > 0 THEN (
    DATEDIFF(
    DD,
    @min_date_submitted_X, -- temp variable replaces the subquery
    GETDATE()
    )
    )
    ELSE (
    DATEDIFF(
    DD,
    @min_date_submitted, -- temp variable replaces the subquery
    GETDATE()
    )
    )
    END
    ELSE -- if claim has never been through a check-run
    (DATEDIFF(DD, C.receive_date, GETDATE())) -- aging = difference between (claim receive date)
    END [Aging] -- and (current date)
    ,CASE
    C.benefit_type
    WHEN 'V' THEN 'V'
    WHEN 'D' THEN CASE
    (SUBSTRING(C.submitted_procedure_id, 1, 2))
    WHEN 'D8' THEN 'O'
    ELSE 'D'
    END
    WHEN 'M' THEN 'M'
    ELSE '?'
    END [benefit_type],
    C.group_gid,
    G.group_id,
    G.group_name,
    W.Date_Originated,
    R.Role_ID,
    R.Role_Description,
    T.Task_Description,
    C.submitted_procedure_id
    FROM
    Application.dbo.Claims_Log_v2 C WITH (NOLOCK)
    INNER JOIN (
    SELECT
    claim_number,
    line_number,
    MAX(date_submitted) AS date_submitted
    FROM
    Application.dbo.Claims_Log_v2 WITH (NOLOCK)
    GROUP BY
    claim_number,
    line_number
    ) X ON C.claim_number = X.claim_number
    AND C.line_number = X.line_number
    AND C.date_submitted = X.date_submitted
    LEFT JOIN (
    SELECT
    DISTINCT [group_gid],
    [group_name] --FROM [BAUtilities].[dbo].[group_name_LU]) gl
    FROM
    [AVESIS_EDI].[dbo].[group_name_LU]
    ) gl ON gl.group_gid = c.group_gid
    LEFT JOIN [Application].[dbo].[Groups] G (NOLOCK) ON C.[group_gid] = G.[group_gid]
    AND G.[record_status] = 'A'
    /********** Added this section to bring in workflow info **********/
    LEFT JOIN Application.dbo.WorkFlow_Process W WITH (NOLOCK) ON C.claim_number = W.Supporting_Data
    AND W.record_status = 'A'
    LEFT JOIN Application.dbo.WorkFlow_Roles R WITH (NOLOCK) ON W.Role_GID = R.Role_GID
    AND R.record_status = 'A'
    LEFT JOIN Application.dbo.WorkFlow_Task_Definition T WITH (NOLOCK) ON W.Task_GID = T.Task_GID
    AND T.record_status = 'A'
    WHERE
    SUBSTRING(C.support_codes, 8, 1) = 'Z'
    AND SUBSTRING(C.support_codes, 4, 1) NOT IN ('3', '5')
    AND C.service_date <= GETDATE()
    AND C.claim_sid = (
    select
    max(maxSid.claim_sid)
    from
    application.dbo.claims_log_v2 maxSid (NOLOCK)
    where
    maxSid.claim_number = c.claim_number
    AND maxSid.line_number = c.line_number
    )
    AND LEN(C.Claim_number) = 15
    GROUP BY
    C.user_id,
    C.date_submitted,
    gl.group_name,
    C.claim_number,
    C.service_date,
    C.receive_date,
    SUBSTRING(C.support_codes, 8, 1),
    C.benefit_type,
    C.group_gid,
    G.group_id,
    G.group_name,
    W.Date_Originated,
    R.Role_ID,
    R.Role_Description,
    T.Task_Description,
    C.submitted_procedure_id;

    Voldemar likes to play CHESS (and IS good at it!)

  • Where is the query plan?

     

  • add .sqlplan extension..

    Voldemar likes to play CHESS (and IS good at it!)

  • how can i add it here?

    Voldemar likes to play CHESS (and IS good at it!)

  • rename it to .txt

    and please put the full original code so we can look at it.

    and get rid of those "with (nolock)" and distinct when using a group by

     

  • attached .sqlplan as .txt.

    Original code:

    SELECT DISTINCT
    C.user_id
    ,C.date_submitted
    ,gl.[group_name] AS client
    ,C.claim_number
    ,C.service_date
    ,C.receive_date
    ,SUBSTRING(C.support_codes,8,1)
    ,SUM(C.submitted_cost)
    /*-------------------------- Aging Logic ---------------------------------------------------------------------*/
    /* */
    /* WHEN claim has paid more than once */
    /* WHEN there was an adjustment (status = X) after the last check date */
    /* THEN aging = (reporting date) minus (1st adjustment date after the last paid date) */
    /* ELSE */
    /* claim was pended without an adjustment (this is procedurally wrong, but occasionally happens */
    /* aging = (reporting date) minus (1st date of any kind of activity after the last paid date) */
    /* ELSE (claim has never been through the Check-Run */
    /* aging = (reporting date) minus (received date) */
    /*----------------------------------------------------------------------------------------------------------------*/
    , CASE WHEN (SELECT COUNT (DISTINCT(countPdDt.paid_date)) -- if there are any paid dates indicating
    FROM Application.dbo.Claims_Log_V2 countPdDt (NOLOCK) -- that the claim has been through a
    WHERE countPdDt.claim_number = C.claim_number -- Check-Run
    AND countPdDt.paid_date > '1/1/1901'
    AND countPdDt.paid_date < '12/31/9999'
    )
    > 0
    THEN CASE WHEN (SELECT COUNT(SUBSTRING(stat.support_codes,8,1)) -- if the claim has any
    FROM Application.dbo.Claims_Log_V2 stat (NOLOCK) -- any adjustments
    WHERE stat.claim_number = C.claim_number
    AND SUBSTRING(stat.support_codes,8,1) = 'X'
    )
    > 0
    THEN (DATEDIFF(DD,(SELECT MIN(date_submitted) -- aging = difference between
    FROM Application.dbo.Claims_Log_V2 MinDtSub (NOLOCK) -- (1st adjustment after the last Check-Run date)
    WHERE MinDtSub.claim_number = C.claim_number -- and (current date)

    AND MinDtSub.date_submitted >= (SELECT MAX(MaxPdDate1.paid_date)
    FROM Application.dbo.Claims_Log_V2 MaxPdDate1 (NOLOCK)
    WHERE MaxPdDate1.claim_number = C.claim_number)
    AND SUBSTRING(MinDtSub.support_codes,8,1) = 'X')
    ,GETDATE()
    ))
    ELSE (DATEDIFF(DD,(SELECT MIN(date_submitted) -- if claim has been through a check-run but has

    FROM Application.dbo.Claims_Log_V2 MinDtSub (NOLOCK) -- no adjustments, then aging = difference between
    WHERE MinDtSub.claim_number = C.claim_number -- (1st activity of any kind after the last Check-Run date)
    AND MinDtSub.date_submitted >= (SELECT MAX(MaxPdDate1.paid_date) -- and (current date)
    FROM Application.dbo.Claims_Log_V2 MaxPdDate1 (NOLOCK)
    WHERE MaxPdDate1.claim_number = C.claim_number))
    ,GETDATE()
    ))
    END
    ELSE -- if claim has never been through a check-run
    (DATEDIFF(DD, C.receive_date, GETDATE())) -- aging = difference between (claim receive date
    END [Aging] -- and (current date)


    ,CASE C.benefit_type

    WHEN 'V' THEN 'V'

    WHEN 'D' THEN CASE (SUBSTRING(C.submitted_procedure_id,1,2))

    WHEN 'D8' THEN 'O'

    ELSE 'D'

    END

    WHEN 'M' THEN 'M'

    ELSE '?'

    END [benefit_type]

    ,C.group_gid

    ,G.group_id

    ,G.group_name

    ,W.Date_Originated

    ,R.Role_ID

    ,R.Role_Description

    ,T.Task_Description

    ,C.submitted_procedure_id

    FROM Application.dbo.Claims_Log_v2 C WITH (NOLOCK)

    INNER JOIN

    ( SELECT claim_number

    ,line_number

    ,MAX(date_submitted) AS date_submitted

    FROM Application.dbo.Claims_Log_v2 WITH (NOLOCK)

    GROUP BY claim_number,line_number ) X

    ON C.claim_number = X.claim_number

    AND C.line_number = X.line_number

    AND C.date_submitted = X.date_submitted

    LEFT JOIN (SELECT DISTINCT [group_gid]

    ,[group_name]

    --FROM [BAUtilities].[dbo].[group_name_LU]) gl

    FROM [AVESIS_EDI].[dbo].[group_name_LU]) gl

    ON gl.group_gid = c.group_gid

    LEFT JOIN [Application].[dbo].[Groups]G (NOLOCK)

    ON C.[group_gid] = G.[group_gid] AND G.[record_status] = 'A'

    /********** Added this section to bring in workflow info **********/

    LEFT JOIN Application.dbo.WorkFlow_Process W WITH (NOLOCK)

    ON C.claim_number = W.Supporting_Data AND W.record_status = 'A'

    LEFT JOIN Application.dbo.WorkFlow_Roles R WITH (NOLOCK)

    ON W.Role_GID = R.Role_GID AND R.record_status = 'A'

    LEFT JOIN Application.dbo.WorkFlow_Task_Definition T WITH (NOLOCK)

    ON W.Task_GID = T.Task_GID AND T.record_status = 'A'

    WHERE
    SUBSTRING(C.support_codes,8,1) = 'Z'
    AND SUBSTRING(C.support_codes,4,1) NOT IN ('3','5')
    AND C.service_date <= GETDATE()
    AND C.claim_sid = ( select max(maxSid.claim_sid)
    from application.dbo.claims_log_v2 maxSid (NOLOCK)
    where maxSid.claim_number = c.claim_number
    AND maxSid.line_number = c.line_number )
    AND LEN(C.Claim_number) = 15
    GROUP BY
    C.user_id
    ,C.date_submitted
    ,gl.group_name
    ,C.claim_number
    ,C.service_date
    ,C.receive_date
    ,SUBSTRING(C.support_codes,8,1)
    ,C.benefit_type
    ,C.group_gid
    ,G.group_id
    ,G.group_name
    ,W.Date_Originated
    ,R.Role_ID
    ,R.Role_Description
    ,T.Task_Description
    ,C.submitted_procedure_id;

    Voldemar likes to play CHESS (and IS good at it!)

  • still no plan

  • i tried to rename the sqlplan file to txt and other and no extension... but SSC keeps saying

    Upload Errors:

    execplanInsert-2.txt: Sorry, this file type is not permitted for security reasons.

    Voldemar likes to play CHESS (and IS good at it!)

  • running query with SET STATISTICSPROFILE ON, will attach as Excel.

    it now takes 3 hours to run and before was only 30 minutes. No other changes in anything, same data volumes, same everything..

    Voldemar likes to play CHESS (and IS good at it!)

  • you are hitting application.dbo.claims_log_v2 alot in this query.   Looks like everything is using a join on claim_number.  Can you put that hit into a cte, then join to it?  Something like below.  There was on DDL or sample data, so it might not compile and there might be typos, but you get the jist

    ;with cte as (SELECT Claim_Number,

    Count (DISTINCT( countPdDt.paid_date )) as countPdDt,

    - sum(case when Substring(stat.support_codes, 8, 1) = 'X' then 1 else 0 end) as stat,

    Min(Case when Substring(support_codes, 8, 1) = 'X' then date_submitted else null end) as MinDtSub1,

    Max(Case when Substring(support_codes, 8, 1) = 'X' then MaxPdDate1.paid_date else null end) as MaxPdDate1,

    Min(date_submitted) as MinDtSub,

    Max(MaxPdDate1.paid_date) as MaxPdDt

    from application.dbo.claims_log_v2

    group by Claim_Number)

    SELECT DISTINCT C.user_id,

    C.date_submitted,

    gl.[group_name] AS client,

    C.claim_number,

    C.service_date,

    C.receive_date,

    Substring(C.support_codes, 8, 1),

    Sum(C.submitted_cost)

    /*-------------------------- Aging Logic ---------------------------------------------------------------------*/

    /* */

    /* WHEN claim has paid more than once */

    /* WHEN there was an adjustment (status = X) after the last check date */

    /* THEN aging = (reporting date) minus (1st adjustment date after the last paid date) */

    /* ELSE */

    /* claim was pended without an adjustment (this is procedurally wrong, but occasionally happens */

    /* aging = (reporting date) minus (1st date of any kind of activity after the last paid date) */

    /* ELSE (claim has never been through the Check-Run */

    /* aging = (reporting date) minus (received date) */

    /*----------------------------------------------------------------------------------------------------------------*/

    ,

    case when countPdDt > 0 then

    case when stat > 0 then

    Datediff(dd, MinDtSub1, MaxPdDate1)

    else

    Datediff(dd, MinDtSub, MaxPdDt)

    end

    else

    Datediff(dd, C.receive_date, Getdate())

    end as [Aging]

    -- CASE

    -- WHEN (SELECT Count (DISTINCT( countPdDt.paid_date )) -- if there are any paid dates indicating

    -- FROM application.dbo.claims_log_v2 countPdDt (nolock) -- that the claim has been through a

    -- WHERE countPdDt.claim_number = C.claim_number -- Check-Run

    -- AND countPdDt.paid_date > '1/1/1901'

    -- AND countPdDt.paid_date < '12/31/9999') > 0 THEN

    -- CASE

    -- WHEN (SELECT Count(Substring(stat.support_codes, 8, 1)) -- if the claim has any

    -- FROM application.dbo.claims_log_v2 stat (nolock) -- any adjustments

    -- WHERE stat.claim_number = C.claim_number

    -- AND Substring(stat.support_codes, 8, 1) = 'X') > 0

    --THEN

    -- ( Datediff(dd, (SELECT Min(date_submitted) -- aging = difference between

    -- FROM application.dbo.claims_log_v2 MinDtSub (nolock)

    -- -- (1st adjustment after the last Check-Run date)

    -- WHERE MinDtSub.claim_number = C.claim_number -- and (current date)

    -- AND MinDtSub.date_submitted >= (SELECT Max(MaxPdDate1.paid_date)

    -- FROM application.dbo.claims_log_v2 MaxPdDate1 (nolock)

    -- WHERE MaxPdDate1.claim_number = C.claim_number)

    -- AND Substring(MinDtSub.support_codes, 8, 1) = 'X'), Getdate()) )

    -- ELSE ( Datediff(dd, (SELECT Min(date_submitted) -- if claim has been through a check-run but has

    -- FROM application.dbo.claims_log_v2 MinDtSub (nolock) -- no adjustments, then aging = difference between

    -- WHERE MinDtSub.claim_number = C.claim_number -- (1st activity of any kind after the last Check-Run date)

    -- AND MinDtSub.date_submitted >= (SELECT Max(MaxPdDate1.paid_date) -- and (current date)

    -- FROM application.dbo.claims_log_v2 MaxPdDate1 (nolock)

    -- WHERE MaxPdDate1.claim_number = C.claim_number)), Getdate()) )

    -- END

    -- ELSE -- if claim has never been through a check-run

    -- ( Datediff(dd, C.receive_date, Getdate()) ) -- aging = difference between (claim receive date

    -- END [Aging] -- and (current date)

    ,

    CASE C.benefit_type

    WHEN 'V' THEN 'V'

    WHEN 'D' THEN

    CASE ( Substring(C.submitted_procedure_id, 1, 2) )

    WHEN 'D8' THEN 'O'

    ELSE 'D'

    END

    WHEN 'M' THEN 'M'

    ELSE '?'

    END [benefit_type],

    C.group_gid,

    G.group_id,

    G.group_name,

    W.date_originated,

    R.role_id,

    R.role_description,

    T.task_description,

    C.submitted_procedure_id

    FROM application.dbo.claims_log_v2 C WITH (nolock)

    join cte ct

    on c.Claim_Number = ct.Claim_Number

    INNER JOIN (SELECT claim_number,

    line_number,

    Max(date_submitted) AS date_submitted

    FROM application.dbo.claims_log_v2 WITH (nolock)

    GROUP BY claim_number,

    line_number) X

    ON C.claim_number = X.claim_number

    AND C.line_number = X.line_number

    AND C.date_submitted = X.date_submitted

    LEFT JOIN (SELECT DISTINCT [group_gid],

    [group_name]

    --FROM [BAUtilities].[dbo].[group_name_LU]) gl

    FROM [AVESIS_EDI].[dbo].[group_name_lu]) gl

    ON gl.group_gid = c.group_gid

    LEFT JOIN [Application].[dbo].[groups]G (nolock)

    ON C.[group_gid] = G.[group_gid]

    AND G.[record_status] = 'A'

    /********** Added this section to bring in workflow info **********/

    LEFT JOIN application.dbo.workflow_process W WITH (nolock)

    ON C.claim_number = W.supporting_data

    AND W.record_status = 'A'

    LEFT JOIN application.dbo.workflow_roles R WITH (nolock)

    ON W.role_gid = R.role_gid

    AND R.record_status = 'A'

    LEFT JOIN application.dbo.workflow_task_definition T WITH (nolock)

    ON W.task_gid = T.task_gid

    AND T.record_status = 'A'

    WHERE Substring(C.support_codes, 8, 1) = 'Z'

    AND Substring(C.support_codes, 4, 1) NOT IN ( '3', '5' )

    AND C.service_date <= Getdate()

    AND C.claim_sid = (SELECT Max(maxSid.claim_sid)

    FROM application.dbo.claims_log_v2 maxSid (nolock)

    WHERE maxSid.claim_number = c.claim_number

    AND maxSid.line_number = c.line_number)

    AND Len(C.claim_number) = 15

    GROUP BY C.user_id,

    C.date_submitted,

    gl.group_name,

    C.claim_number,

    C.service_date,

    C.receive_date,

    Substring(C.support_codes, 8, 1),

    C.benefit_type,

    C.group_gid,

    G.group_id,

    G.group_name,

    W.date_originated,

    R.role_id,

    R.role_description,

    T.task_description,

    C.submitted_procedure_id;

    • This reply was modified 5 months, 4 weeks ago by  Mike01.

    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/

  • attaching plan in EXCEL FILE.

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

    Voldemar likes to play CHESS (and IS good at it!)

  • Thank you! Will give it a try tomorrow.

    Voldemar likes to play CHESS (and IS good at it!)

Viewing 14 posts - 1 through 14 (of 14 total)

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