Hi,
It's the same in 2008... nodes() with text() is twice as faster as simply nodes() and OPENXML...
Thanks for the tip... Even for large XML nodes() is faster.
Regards,
Pedro
mister.magoo (9/27/2012)
I tried this simple test - on SQL 2012 Xquery wins - interesting to see if 2008 is the same.
For small XML there's no difference. The 2nd Execution time on the OPENXML is how long the sp_xml_removedocument takes?! :w00t:
Thanks,
Pedro
PiMané (9/28/2012)
mister.magoo (9/27/2012)
I tried this simple test - on SQL 2012 Xquery wins - interesting to see if 2008 is the same.For small XML there's no difference. The 2nd Execution time on the OPENXML is how long the sp_xml_removedocument takes?! :w00t:
Thanks,
Pedro
Well, yes - the overhead of OPENXML has always been a sore point for me...
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);
mister.magoo (9/28/2012)
Well, yes - the overhead of OPENXML has always been a sore point for me...
Damn... that's a lot of time... probably related to the fact that's a big XML and creates data on tempdb...
It's a big overhead...
I read a lot about the 1/8 memory used by sp_xml_preparedocument but the truth is that I don't notice that... probably only on a multi thread environment this actually shows!!
I made a SQL restart and executed the OPENXML... SQL Server memory usage went up 11M.. I made another restart an executed the nodes() with text() and memory went up 8M... not a big difference... In both cases I waited a little time to see if SQL Server freed up memory but... SQL Server never frees memory .. It's like the alien plan of "Little Shop of Horrors".. "Feed me more...."
So my actual question is: for little XML since time is almost the same and sp_xml_removedocument doesn't take that much, should OPENXML be used or the memory is going to be a problem on a multi threaded environment?!
Thanks,
Pedro
PiMané (9/28/2012)
So my actual question is: for little XML since time is almost the same and sp_xml_removedocument doesn't take that much, should OPENXML be used or the memory is going to be a problem on a multi threaded environment?!Thanks,
Pedro
Whether OPENXML should be used or not is beyond my knowledge...
I personally never use it because I find the XML datatype to be more suited to my way of thinking and I have yet to see a situation in my own work where OPENXML would give me a benefit....
I wait with interest to hear from others on this...
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);
mister.magoo (9/28/2012)
I wait with interest to hear from others on this...
That make two of us
But honestly I don't think we'll have much feedback on this issue...
PiMané (9/28/2012)
mister.magoo (9/28/2012)
I wait with interest to hear from others on this...That make two of us
But honestly I don't think we'll have much feedback on this issue...
Oh, I don't know - there are plenty of folks around here who like a bit of performance testing...:-D
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);
When I want whole XML convert to tabular form I found OpenXML is easy and fast
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply