Kumar AU

Kumar AU

  • 1.4k
  • 309
  • 61.6k

How to avoid reading value from XML column due to performance issue

May 18 2022 11:19 PM

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

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)

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>',20)

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>',30)

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>',12)

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>',16)

 


Answers (4)