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)