I come across the pattern of using an XML document as the input parameters for stored procedures quite every once in a while. Each time I do I ask myself if the benefit is worth the cost.
When you pass xml as parameters to a stored procedure you must extract the data into SQL table variables or scalar variables in order to use the contents. This is done by calling the xmldom.dll to provide an adapter allowing you to use xml type queries to extract scalar data or table data from the xml.
One problem you will experience with this process is that you can generate a memory leak. If the instance of xmldom.dll is not closed due to an error in your query, it remains open for some period of time. My last testing of this phenomena is quite old, and may not be present in SQL Server 2008 R2 or later.
So, even if you don’t have a memory leak, you certainly have overhead of making an out of process call to open the DOM and interact with it, marshalling the data from the dll into SQL Server. This activity runs in a different memory cache in SQL Server, and may be more limited in one installation than another.
Since SQL Server 2008 supports user defined table types, there really is no requirement to use XML to pass data. Or, you can create temporary tables, and then populate them using Bulk Copy, as an alternative method for passing data to stored procedures or other query methods. This technique by far outperforms passing XML data as a parameter, and takes the load of translation off the limited resource, your database engine.
One final observation I have found is that the XML parameters technique is often associated with a process of deleting all existing database records, followed by inserting all new data. No effort is made to merge the changes being sent.
This is a really powerful way to introduce database sprawl. The database has so many instances of data over time that it can climb to massive size with little benefit. We didn’t keep a history of what changed. We kept a point in time snapshot of everything, even if it didn’t change.
If you needed to maintain history of who change what, and when, it would be extremely complicated, and require a lot of resources, to delve into this morass of meaningless data.
Just because you used an XML parameter technique to pass whole sets of data doesn’t mean you can’t perform a standardized merge process of deleting records not in you new set, updating changed records in you new set also in the database, and inserting new records not already in your database contained in you new set.
Personally I like to delete, update, insert in that order. Since updates work against everything, if you delete first there is less to compare. If you insert first, then when you update, you update what you just inserted. So, delete speeds up the update. Update then works with only the records requiring update. Insert adds the stuff not already in the database, and you’re done.
Is there a place for passing data in XML? Share your thoughts in comments here, or by Email to btaylor@sswug.org.
Cheers,
Ben