Score:-1

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

mx flag

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)

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)
mangohost

Post an answer

Most people don’t grasp that asking a lot of questions unlocks learning and improves interpersonal bonding. In Alison’s studies, for example, though people could accurately recall how many questions had been asked in their conversations, they didn’t intuit the link between questions and liking. Across four studies, in which participants were engaged in conversations themselves or read transcripts of others’ conversations, people tended not to realize that question asking would influence—or had influenced—the level of amity between the conversationalists.