SQL Server Performance issue in stored procedure

mlnl4t2r  于 2023-10-15  发布在  其他
关注(0)|答案(2)|浏览(103)

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:

  1. DECLARE @BeginDate datetime ='2023-09-28'
  2. DECLARE @from datetime
  3. DECLARE @to datetime
  4. DECLARE @Include BIT = 1
  6. SET @from = DATEADD(dd, DATEDIFF(dd, 0, @BeginDate), 0)
  7. SET @to = DATEADD(dd, DATEDIFF(dd, -1, @BeginDate), 0)
  9. IF OBJECT_ID('tempdb..#results') IS NOT NULL 
  10.     DROP TABLE #results
  12. CREATE TABLE #results 
  13. (
  14.     [BPID] [nvarchar](32) NOT NULL,
  15.     [DId] [bigint] NULL,
  16.     [Number] [nvarchar](32) NULL,
  17.     [Account] [nvarchar](32) NULL,
  18.     [LineOfBusinessDesc] [nvarchar](4) NULL,
  19.     [LineOfBusiness] [varchar](35) NULL,
  20.     [Channel] [varchar](35) NULL,
  21.     [ProductLine] [varchar](35) NULL,
  22.     [BusinessSegment] [varchar](35) NULL,
  23.     [LineOfBusinessType] [varchar](35) NULL
  24. )
  26. INSERT INTO #results
  27.     SELECT 
  28.         [PID], [DId], [Number], [Account],
  29.         NULL, NULL, NULL, NULL, NULL
  30.     FROM 
  31.         dbo.DailyDataInfo
  32.     WHERE 
  33.         CreatedDate >= @from 
  34.         AND CreatedDate < @to
  36. IF @Include = 1
  37. BEGIN
  38.     INSERT INTO #results
  39.         SELECT 
  40.             a.[PID], [DId], a.[Number], a.[Account],
  41.             a.[LineOfBusiness], b.[LineOfBusiness],
  42.             b.[Channel], b.[ProductLine], b.[BusinessSegment]
  43.         FROM 
  44.             dbo.OtherDailyData a
  45.         OUTER APPLY 
  46.             (SELECT TOP 1 Id, 
  47.                  x.Data.value('(//Data/Company/LineOfBusiness)[1]', 'varchar(30)') AS LineOfBusiness, 
  48.                  x.Data.value('(//Data/Company/Channel)[1]', 'varchar(30)') AS Channel, 
  49.                  x.Data.value('(//Data/Company/ProductLine)[1]', 'varchar(25)') AS ProductLine, 
  50.                  x.Data.value('(//Data/Company/BusinessSegment)[1]', 'varchar(25)') AS BusinessSegment, 
  51.              FROM 
  52.                  dbo.TermData x
  53.              WHERE
  54.                  x.Reference = a.Number
  55.              ORDER BY
  56.                  x.Id DESC) b
  57.         WHERE 
  58.             CreatedDate >= @from AND CreatedDate < @to
  59.  END
  61.  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

  1. BPID            Id  Number       Account    LineOfBusiness  Createddate
  2. F886A11A6546199 1   9203919023  9203919023  HH              10/2/23 8:04 PM
  3. 1802063B1312516 2   9203919031  9203919031  KJ              10/2/23 8:04 PM
  4. a4DEEF472650CB8 3   9203905782  9203905782  KJ              10/2/23 8:04 PM
  5. 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.

  1. BPId                DId         Number       CreatedDate
  2. 9FE25361398013BF    64  9340733736  10/2/23 8:05 PM
  3. 20C072C8596503A     68  9340732569  10/2/23 8:01 PM
  4. 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:

  1. TermId  Reference  Data
  2. ------------------------------------------------------------------
  3. 321432  9340703401 This is the xml column please refer the below sample value

Data column value

  1. <Data>
  2.   <Allow>1</Allow>
  3.   <EligibilityFlag>0</EligibilityFlag>
  4.     <Company>
  5.     <LineOfBusiness>CCC</LineOfBusiness>
  6.     <LineOfBusinessType>
  7.       <LOBType>CommercialAuto</LOBType>
  8.     </LineOfBusinessType>
  9.     <Channel />
  10.     <ProductLine>Trad</ProductLine>
  11.     <BusinessSegment>E</BusinessSegment>
  12.   </Company>
  13.   <Info>
  14.     <Auditable>0</Auditable>
  15.   </Info>
  16. </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]

sql-server

2条答案

按热度按时间
fdbelqdn

fdbelqdn1#

First, don't use // in your XPATH. It searches the whole document. Instead path from the root / to exactly the node you need. Then consider an XML Index , or materializing those three columns .

And watch this classic video where Michael Rys walks you through all the SQLXML best practices.

赞(0）分享  回复(0）举报  2023-10-15
mcdcgff0

mcdcgff02#

This moves the WHERE logic ahead of the OUTER APPLY...

  1. DECLARE @BeginDate datetime ='2023-09-28'
  2. DECLARE @from datetime
  3. DECLARE @to datetime
  4. DECLARE @Include BIT = 1
  6. SET @from = DATEADD(dd, DATEDIFF(dd, 0, @BeginDate), 0)
  7. SET @to = DATEADD(dd, DATEDIFF(dd, -1, @BeginDate), 0)
  9. SELECT  @from, @to
  11. IF OBJECT_ID('tempdb..#results') IS NOT NULL 
  12.     DROP TABLE #results
  14. CREATE TABLE #results 
  15. (
  16.     [BPID] [nvarchar](32) NOT NULL,
  17.     [DId] [bigint] NULL,
  18.     [Number] [nvarchar](32) NULL,
  19.     [Account] [nvarchar](32) NULL,
  20.     [LineOfBusinessDesc] [nvarchar](4) NULL,
  21.     [LineOfBusiness] [varchar](35) NULL,
  22.     [Channel] [varchar](35) NULL,
  23.     [ProductLine] [varchar](35) NULL,
  24.     [BusinessSegment] [varchar](35) NULL,
  25.     [LineOfBusinessType] [varchar](35) NULL
  26. )
  28. INSERT INTO #results
  29.     SELECT 
  30.         [PID], [DId], [Number], [Account],
  31.         NULL, NULL, NULL, NULL, NULL, NULL
  32.     FROM 
  33.         dbo.DailyDataInfo
  34.     WHERE 
  35.         CreatedDate >= @from 
  36.         AND CreatedDate < @to
  38. IF @Include = 1
  39. BEGIN
  40.     INSERT INTO #results
  41.         SELECT 
  42.             a.[PID], [DId], a.[Number], a.[Account],
  43.             a.[LineOfBusiness], b.[LineOfBusiness],
  44.             b.[Channel], b.[ProductLine], b.[BusinessSegment], NULL
  45.         FROM 
  46.             (
  47.                 SELECT  n.[PID], n.[DId], n.[Number], 
  48.                         n.[Account], n.[LineOfBusiness]
  49.                 FROM    dbo.OtherDailyData a
  50.                 WHERE 
  51.                     CreatedDate >= @from AND CreatedDate < @to
  52.             )   a
  53.         OUTER APPLY 
  54.             (SELECT TOP 1 Id, 
  55.                  x.Data.value('(//Data/Company/LineOfBusiness)[1]', 'varchar(30)') AS LineOfBusiness, 
  56.                  x.Data.value('(//Data/Company/Channel)[1]', 'varchar(30)') AS Channel, 
  57.                  x.Data.value('(//Data/Company/ProductLine)[1]', 'varchar(25)') AS ProductLine, 
  58.                  x.Data.value('(//Data/Company/BusinessSegment)[1]', 'varchar(25)') AS BusinessSegment
  59.              FROM 
  60.                  dbo.TermData x
  61.              WHERE
  62.                  x.Reference = a.Number
  63.              ORDER BY
  64.                  x.Id DESC) b        
  65.  END
  67.  SELECT * FROM #results
展开查看全部
赞(0）分享  回复(0）举报  2023-10-15
我来回答

相关问题

    查看更多

    热门标签

    更多
    JavaquerypythonNode开发语言requestUtil数据库Table后端算法LoggerMessageElementParser

    最新问答

    更多
    • 蜀ICP备13028337号-1 大数据知识库 https://www.saoniuhuo.com © All rights reserved
    • 本站内容来源互联网,如果侵犯您的权益请联系我们删除, 联系方式：448109455@qq.com