SQL Performance - How to avoid reading value from XML column due to performance issue

SQL Server Administration
1

I am new to SQL , could you please suggest me how do I improve below SQL code, I have observed using SQL profiler - XML reading operation is taking too much of time.

Kindly let me know how do I rewrite the below code, so that I can see improvement in the performance.

Here - edata is the XML Column And below code is part of my main stored procedure , only below code is having an issue with performance And erequest table is already indexed

   DECLARE @SFD TABLE 
  ( 
     etid       BIGINT, 
     eAmount    DECIMAL(12, 2), 
     eDate    DATE
  ) 

INSERT INTO @SFD
SELECT  tr.etid,
        tr.edata.value('(EData/Amount)[1]', 'DECIMAL(12, 2)') eAmount,
        tr.edata.value('(EData/DrawDate)[1]','date') eDate
FROM   dbo.erequest tr
WHERE  tr.accountid = @AccountId

IN the above query - following lines are taking too much of time,

    **tr.edata.value('(EData/Amount)[1]', 'DECIMAL(12, 2)') eAmount,
    tr.edata.value('(EData/DrawDate)[1]','date') eDate**

Kindly advise me , how do I rewrite the above lines in the main sql query so that I can see the performance improvement.

Kindly find the below query to populate the Table data:

Create table erequest
(
etid BIGINT,
edata XML,
accountid INT
)

INSERT INTO erequest (etid,edata,accountid) VALUES (2145124897,
'<edata>
  <CardHolderName>ABCFDE</CardHolderName>
  <CardNumber>K6011</CardNumber>
  <Amount>555.17</Amount>
  <DrawDate>2022-05-18</DrawDate>
  <CurrencyCode>USD</CurrencyCode>
</edata>',10)
2

Have you considered adding a calculated field to the table dbo.erequest where you convert both fields? In this article they give a great example of how to archieve that:

Performance tips of using XML data in SQL Server - Microsoft Tech Community

This has disadvantages as insert statements will take longer and your database will became larger but your select will be much better.

I hope you have an index on tr.accountid? Maybe you can paste your plan so we can take a deeper look into the table with rows involved etc.

Instructions - Brent Ozar Unlimited®

1 Like