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)