Post
Ask Question
Kumar AU

Kumar AU

  • 1.3k
  • 295
  • 57.1k

SQL Using Outer Apply Join - Improve SQL performance

Oct 3 2023 2:22 AM

How to improve performance of my stored procedure.

This is my stored proc – it works fine , but it takes more than 5 minutes of time to get the result due to that its causing timeout from API

Note - @Include and @BeginDate these are the SP input parameter

DECLARE @BeginDate datetime ='2023-09-28'
DECLARE @from datetime
DECLARE @to datetime
DECLARE @Include BIT = 1

SET @from = DATEADD( dd, DATEDIFF( dd, 0, @BeginDate), 0)
SET @to = DATEADD( dd, DATEDIFF( dd, -1, @BeginDate), 0)

IF OBJECT_ID('tempdb..#results') IS NOT NULL DROP TABLE #results

CREATE TABLE #results 
(
  [BPID] [nvarchar](32) NOT NULL,
    [DId] [bigint] NULL,
    [Number] [nvarchar](32) NULL,
    [Account] [nvarchar](32) NULL,
    [LineOfBusinessDesc] [nvarchar](4) NULL,
    [LineOfBusiness] [varchar](35) NULL,
    [Channel] [varchar](35) NULL,
    [ProductLine] [varchar](35) NULL,
    [BusinessSegment] [varchar](35) NULL,
    [LineOfBusinessType] [varchar](35) NULL
)

INSERT INTO #results
SELECT [PID]
    ,[DId]
    ,[Number]
    ,[Account]
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
FROM dbo.DailyDataInfo
WHERE CreatedDate >= @from and CreatedDate < @to

IF @Include = 1
BEGIN
    INSERT INTO #results

    SELECT a.[PID]
    ,[DId]
    ,a.[Number]
    ,a.[Account]
    ,a.[LineOfBusiness]
    ,b.[LineOfBusiness]
    ,b.[Channel]
    ,b.[ProductLine]
    ,b.[BusinessSegment]

    FROM dbo.OtherDailyData a
    OUTER APPLY 
    (
        select top 1 Id, 
        x.Data.value('(//Data/Company/LineOfBusiness)[1]', 'varchar(30)') as LineOfBusiness, 
        x.Data.value('(//Data/Company/Channel)[1]', 'varchar(30)') as Channel, 
        x.Data.value('(//Data/Company/ProductLine)[1]', 'varchar(25)') as ProductLine, 
        x.Data.value('(//Data/Company/BusinessSegment)[1]', 'varchar(25)') as BusinessSegment, 

        from dbo.TermData x
        where x.Reference = a.Number
        order by x.Id desc
    ) b
    WHERE CreatedDate >= @from and CreatedDate < @to
END
SELECT * FROM #results

 

This is the sample Table data for dbo.DailyDataInfo

BPID         DId    Number      Account LineOfBusiness
F886A11A6546199 1   9203919023  9203919023  HH
1802063B1312516 2   9203919031  9203919031  KJ
a4DEEF472650CB8 3   9203905782  9203905782  KJ
05D23BE7D263582 4   9203908786  9203908786  HHH
97F1C0E2FEC2EF  5   9203906228  9203906228  AA
B7819FC63CE669D 7   9203911962  9203911962  LKK
4DA4D30906FFB7E 8   9202809746  9202809746  HH
0CD8EC07699D3E8 9   9203858411  9203858411  AA
6A16BBBCD295741 10  9203918587  9203918587  AA
12021D6CB90AD01 11  9203905006  9203905006  LL

This is the sample Table data for dbo.OtherDailyData

BPId            DId Number
9FE25361398013B 64  9340733736
20C072C8596503A 68  9340732569
6526588B6CFC49A 72  9340733502
2E42FBDD4B70C01 67  9340732569
40CC7CEB4465FE8 59  9340733831
20C072C8596503A 69  9340732569
19E8F18ABD2C49B 57  9340682728
DCA39F32A3A5170 66  9340733737

This is the sample Table data for dbo.TermData

TermId  Reference  Data
321432  9340703401 This is the xml column please refer the below sample value

Data Column Value

<Data>
  <Allow>1</Allow>
  <EligibilityFlag>0</EligibilityFlag>
    <Company>
    <LineOfBusiness>CCC</LineOfBusiness>
    <LineOfBusinessType>
      <LOBType>CommercialAuto</LOBType>
    </LineOfBusinessType>
    <Channel />
    <ProductLine>Trad</ProductLine>
    <BusinessSegment>E</BusinessSegment>
  </Company>
  <Info>
    <Auditable>0</Auditable>
  </Info>
</Data>

Answers (1)