June 10, 2008 at 9:17 pm
I am trying to determine whether there is a performance hit or not using openxml in a stored procedure, I have used this in the past and never had any compliants. However, the person I work with read something recently that the approach I am using is the one that some book didn't recommend using.
I have used where if I have to do multiple inserts or updates across multiple tables, then ratherthan passing lots of items in on the parameter list to the stored procedure or calling a stored procedure multiple times, building an xml string and passing it into a stored procedure seemed like the logical approach.
I came across the OpenXML article on this site and the steps I am doing are very much the same as this article, so I figured i would check to see what others think about using OpenXML to do such inserts. Will be interesting to see what everyone think.s
June 11, 2008 at 6:23 am
I did a series of tests because I wanted to write an article comparing OPENXML to XQUERY. I couldn't find a fundamental performance difference in query execution time, scans or reads. Maybe there's a difference in memory allocation or something on the server that I didn't catch. Others might have better information here.
However, I'd still recommend moving away from OPENXML to using XQUERY because the syntax is so much cleaner and easier to maintain. There's a great deal of flexibility in the whole XQUERY structure that just isn't there in OPENXML.
That's my 1.5 cents anyway.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply