I need to improve performance of my stored procedure. Please find my stored procedure below which works fine, but it takes more than 5 minutes for some time not always to get the result due to that it's causing timeout from API.

Please note: @Include and @BeginDate are the stored procedure input parameters:

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 along with Index information for dbo.DailyDataInfo: I don't see any issue with this table, based on execution plan

CreatedDate – is the non-unique, non-clustered index. There are no other indexes on this table

BPID            Id  Number       Account    LineOfBusiness  Createddate
F886A11A6546199 1   9203919023  9203919023  HH              10/2/23 8:04 PM
1802063B1312516 2   9203919031  9203919031  KJ              10/2/23 8:04 PM
a4DEEF472650CB8 3   9203905782  9203905782  KJ              10/2/23 8:04 PM
05D23BE7D263582 4   9203908786  9203908786  HHH             10/2/23 8:04 PM

This is the sample table data along with Index information for dbo.OtherDailyData : I don't see any issue with this table, based on execution plan.

Did is the primary Key and it is a clustered index. There are no other indexes on this table.

BPId                DId         Number       CreatedDate
9FE25361398013BF    64  9340733736  10/2/23 8:05 PM
20C072C8596503A     68  9340732569  10/2/23 8:01 PM
6526588B6CFC49A     72  9340733502  10/2/23 8:02 PM

This is the sample table data along with Index information for dbo.TermData - I see issue with this table, based on execution plan

• TermId - is the primary key
• Reference - is the non-unique, non-clustered index

Data:

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>

Based on my analysis on execution plan, TermData table lookup is taking too much time, especially the XML lookup - 12% on each columns retrieval.

[![Execution plan][1]][1]

[![TermData Table][2]][2]