Performance Issue With Cross Apply While Reading Xml Nodes For Large Dataset

Performance issue with XML cross apply: DataTable has 1300 entries and the field xmldata has 250 nodes, so the query is running 1300 * 250 times to brings the output and the execu

Solution 1:

Your approach seems to be pretty straight forward. Not much space for enhancements...

The following is just a tiny change, but might speed up things:

declare @tbl TABLE(ID INT IDENTITY, xmldata XML);
INSERT INTO @tbl VALUES
(N'<dataModel><Colum1><value /><displayText /><controltype>textbox</controltype><label>Field1</label><controlid>4458575-b0d3-ff4d-01ac-5447e21234dd</controlid></Colum1><Colum2><value /><displayText /><controltype>textbox</controltype><label>Field2</label><controlid>5a5b7b7e-7b66-1f0d-a562-9d0660a74e11</controlid></Colum2></dataModel>');

select  t.c.value('(local-name(.))[1]', 'nvarchar(100)') as keyname ,
        t.c.value('(controlid/text())[1]', 'nvarchar(200)') as controlid,
        t.c.value('(label/text())[1]', 'nvarchar(500)') as label
from @tbl xmldata 
CROSS APPLY xmldata .nodes('/dataModel/*') T(c);

I added /text() to your XPaths (find details here).

It would be kind to tell us, how much difference you've encountered using /text(), thx.

And important to know: One very expensive part with XML is the initial parsing. Make sure, that the table's column is natively xml typed and that your run-time measurement is not biased by any loading / reading / parsing action (find details here).