| Author |
Topic |
|
mfemenel
Professor Frink
1421 Posts |
 Posted - 2004-03-23 : 14:56:46
|
| I have a scenario I wanted to put out for discussion. I'm at a point where I can go 1 of 2 ways with an application we have. We're writing API's that talk back and forth to the database. The input into the API's will be xml. Our original concept was to parse the xml into a delimited list of values and pass that list as an argument to a stored procedure. Now, however, I'm reading a lot about xml and updategrams and it seems that it would be cleaner/faster to do it that way instead of parsing the list, passing it to an sp, parsing it in the sp and then executing. My question or item for discussion, is what does everyone think would be faster. I'm not sure, so I'm open to opinions. This is a highly transactional database with 60% reads, 40% writes.Mike"oh, that monkey is going to pay" |
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-03-23 : 20:03:36
|
| How big will the list be?MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
 |
|
|
mfemenel
Professor Frink
1421 Posts |
Posted - 2004-03-25 : 08:50:43
|
| On average 10 or less rows. Could be higher for some customers based on the number of widgets they own.Mike"oh, that monkey is going to pay" |
|
|
|
ehorn
Master Smack Fu Yak Hacker
1632 Posts |
Posted - 2004-03-25 : 19:42:16
|
| Have you considered OPENXML as a third option? Passing xml natively, via updategrams or OPENXML, will most likely outperform the alternative method. |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-03-25 : 19:52:42
|
| It kind of depends what you are doing with the list though. Generally, XML is parsed more efficiently at the application layer. If you are only going to have small recordsets, it could easily be faster to have a table function that makes a "table" of a comma-delimited list. You could then join or insert those values into another table.For larger sets, I would look at OPENXML and use that.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
ehorn
Master Smack Fu Yak Hacker
1632 Posts |
Posted - 2004-03-25 : 20:07:21
|
quote:
Generally, XML is parsed more efficiently at the application layer
I would disagree in that both techniques use the same parsing engine, and SQL SP1 uses the most current, optimized MSXML 3.0 parser. |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-03-25 : 20:22:49
|
| Our databases are highly transactional at a rough 70/30 ratio. I've seen too many times where extensive use of the OPENXML feature can push the server over the edge very quickly. If you are dealing with small recordsets and a lot of transactions on the recordsets, I would still parse it at the application layer and only send back what you need to. It's going to be more efficient because you're not using up SQL Server resources to parse it and you're sending a smaller recordset to the server. Weigh were you want the processing to take place though. I choose to do it at the application level because my experience has shown in our environment it's more efficient.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
ehorn
Master Smack Fu Yak Hacker
1632 Posts |
Posted - 2004-03-25 : 21:05:51
|
| Which brings up another very valid consideration. Architecture in the enterprise plays a very important role in determining what can handle the load and prevent or minimize the bottlenecks. This makes a strong case for load testing in development and staging environments and typically reveals the best performing technique for a given environment. |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-03-25 : 21:51:03
|
| Be pretty hard to argue with that. :) There are some things that should always be avoided when possible (cursors, dynamic sql, dynamic security grants, etc). Others need to be tested and can vary per environment or situation. That's why people are always asking for more information on the forum. It's not to give anyone a hard time. It's to try and get a better understanding of the situation before giving an answer.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
mfemenel
Professor Frink
1421 Posts |
Posted - 2004-03-30 : 17:30:55
|
| Sorry to get back to this one late. Thanks for your input guys. Good points all around. I'm holding off on my decision to see what our developers final architecture is going to be. At one point they were feeding us records as they changed, now they're talking about batches instead. What they want to do makes a big difference in how I write it. Thanks very much for your thoughts.Mike"oh, that monkey is going to pay" |
|
|
|
|
Update Grams vs Delimited List