Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
aiken
Aged Yak Warrior
525 Posts |
 Posted - 2003-06-07 : 01:07:27
|
| So now that the big upgrade is done, it's back to normal work.I've got a table which defines user preferences:[code]CREATE TABLE prefs (i_users int primary key, class varchar(12) NOT NULL, keyname varchar(12) NOT NULL, keyvalue varchar(250))[/coode]It's not at all unusual for one pageload to generate 10-12 user preference changes. Right now, I'm using a SP to update the preferences one at a time; if the user changes 10 settings, there are 10 calls to the SP. That SP does an update, and if @@ROWCOUNT=0, it does an insert (the poor man's UPSERT).How can I improve this, and hopefully end up with at most one round trip and as few transactions as possible? I've thought of encoding the class/keyname/keyvalue sets into a single varchar parameter to a stored procedure, but because the values can have any character, it means I have to get into escaping commas or vbars or something. And there'd still be mutltiple update/inserts.What about XML updategrams? They look promising, but my only experience with using XML and SQL server is SELET...FOR XML. Are updategrams even native, or do I need to use SQLXML and go through the web/ISAPI interface?Any other ideas for elegantly updating/inserting multiple records in one go?Cheers-b |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2003-06-07 : 08:40:29
|
| There's a number of techniques here:http://www.sqlteam.com/SearchResults.asp?SearchTerms=csvYou can always use tabs as a delimiter instead of commas, a tab is not going to be a preserved character so it makes a perfect delimiter. I would suggest structuring the TSV with a fixed number of elements; any unpassed values would show as two consecutive tabs, and when it's parsed they will come out as empty strings (''). That way you won't need to include the key names along with the values, unless you intend to make the preferences completely dynamic.I wouldn't bother with XML because it's not necessary and will only bloat and complicate the code needed to parse it. |
 |
|
|
aiken
Aged Yak Warrior
525 Posts |
Posted - 2003-06-07 : 13:46:07
|
Thanks for the idea, and I see how one could populate a temp table by parsing the TSV and then to an insert...select to get it down to one transaction.However, I decided to go the SQLXML approach, and performance seems fine. It's definitely much faster than the 10-12 round trip approach I had before! Here's sample code (adapted from http://msdn.microsoft.com/library/default.asp?url=/library/en-us/sqlxml3/htm/updategram_2icv.asp ): Dim oCmdPWBX,oDBPWBX,oStrmIn,oStrmOut,szSQLXML,iX Set oDBPWBX=GetDB() oDBPWBX.Properties("SQLXML Version") = "SQLXML.3.0" Set oCmdPWBX=Server.CreateObject("ADODB.Command") Set oCmdPWBX.ActiveConnection=oDBPWBX szSQLXML = "<ROOT xmlns:updg='urn:schemas-microsoft-com:xml-updategram' >" szSQLXML = szSQLXML & "<updg:sync >" szSQLXML = szSQLXML & " <updg:before>" szSQLXML = szSQLXML & "</updg:before>" szSQLXML = szSQLXML & "<updg:after>" For iX=0 to uBound(iBufPrefUserID) if bBufPrefModified(iX) Then szSQLXML=szSQLXML & "<user_preferences i_users=" & iBufPrefUserID(iX) & " class='" & szBufPrefCname(iX) & "' name='" & szBufPrefVname(iX) & "' value='" & szBufPrefValue(iX) & "' />" End If Next szSQLXML = szSQLXML & "</updg:after>" szSQLXML = szSQLXML & "</updg:sync>" szSQLXML = szSQLXML & "</ROOT>" oCmdPWBX.Dialect = "{5d531cb2-e6ed-11d2-b252-00c04f681b71}" Set oStrmIn=Server.CreateObject("ADODB.Stream") oStrmIn.Open oStrmIn.WriteText szSQLXML oStrmIn.Position=0 Set oCmdPWBX.CommandStream=oStrmIn Set oStrmOut=Server.CreateObject("ADODB.Stream") oStrmOut.Open oStrmOut.LineSeparator = adCRLF oCmdPWBX.Properties("Output Stream").Value = oStrmOut oCmdPWBX.Properties("Output Encoding").Value="UTF-8" oCmdPWBX.Execute , , adExecuteStream oStrmOut.Position = 0 'Response.Write(oStrmOut.ReadText) Set oStrmOut=nothing Set oStrmIn=nothing Set oCmdPWBX=nothing oDBPWBX.Close Set oDBPWBX=nothing...It's pretty self explanatory; GetDB() just gets an open ADODB.Connection, and you can see my array of preferences to be stored.Cheers-b |
|
|
|
byrmol
Shed Building SQL Farmer
1591 Posts |
Posted - 2003-06-07 : 17:37:01
|
| The words "fast" and "XML" never belong together...DavidM"SQL-3 is an abomination.." |
|
|
|
aiken
Aged Yak Warrior
525 Posts |
Posted - 2003-06-08 : 13:59:01
|
| Oh, I don't know about that. Historically, yes, but it's getting quite a bit better.For instance, one of our pages that displays a Yahoo-esque directory picked up an 80% speed improvement when we moved from typical ASP/ADO (with GetRows) to using FOR XML in the SP and then just doing an XSLT on the results. A big, dramatic improvement. And the ASP was well-written (no string concats, GetRows, etc).I'm not sure if I'll get the time to performance test this solution, but my guess is it's at least comprable to the "pass delimited varchar, extract parameters into table variable, do insert...select from table variable" approach.Cheers-b |
|
|
|
byrmol
Shed Building SQL Farmer
1591 Posts |
Posted - 2003-06-08 : 17:59:19
|
| It was a general observation.We're at the end of large .NET project where every tier is a web service. The bottle neck is now the "middle" tier. The database tier, traditional the slow link is now the fastest....DavidM"SQL-3 is an abomination.." |
|
|
|
aiken
Aged Yak Warrior
525 Posts |
Posted - 2003-06-08 : 22:30:42
|
Oh, I hear you. The problem with XML is that it lures people into that "every tier must communicate with elaborate messaging" mentality.If you keep it clean and simple, though, and use it as a data format and not a messaging format, it can be fast as all hell. In fact, I'm going to argue that it has the potential to be the fastest possible way to communicate with databases, because XML is inherently set-oriented.And I'm sure its performance as a messaging format will improve over time as people optimize the libraries that handle that. Or at least, as hardware continues to get faster  .Cheers-b |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2003-06-08 : 23:03:24
|
quote:
In fact, I'm going to argue that it has the potential to be the fastest possible way to communicate with databases, because XML is inherently set-oriented.
That's not a good argument to make, because neither point bears any scrutiny. XML isn't set oriented at all, nor was it ever intended to be. Simply because it is a naturally hierarchical structure, and can store even the most elaborate relationships, doesn't mean it's the same thing as a relational database structure. There are a number of similarities, but there are also crucial differences. The biggest problem with XML is that it is serialized; you cannot reach node 100 without hitting all 99 nodes before it. Even things like XPath and XQuery don't overcome this. It's not necessarily a shortcoming when importing data, but it is a serious downfall when querying XML data natively.And if you think XML is fast, you've obviously never used bcp. Not to mention that 60-70% of the content of a typical XML file is bloat and structure tags, not actual data, while a text file is nothing but data and the absolute minimum necessary structure. Load up a 1,000 node XML document and parse it, then compare that performance to a 10,000-20,000 row text file with bcp, I guarantee bcp will smoke XML every time. I recently had to dump and reload an entire database of approx. 1.5 million rows using bcp, and it took about 45 seconds for each operation. I can imagine a computer generating XML almost that fast, but no way can it parse and import it that quickly (and an XML version would be close to 10 times the size of the text files)Edited by - robvolk on 06/08/2003 23:53:22 |
|
|
|
aiken
Aged Yak Warrior
525 Posts |
Posted - 2003-06-09 : 00:17:49
|
| Sure, I totally agree. I should have been more specific.To rephrase my point: For small sets of data with simple or no relationships, XML has the potential to be the fastest way to communicate with a database because it is inherently set-oriented. That is, doing 8 inserts or updates with a single XML query will almost certainly be faster than doing 8 individual updates or inserts using ADO or other access methods. And it's likely to be faster than other hacks, such as encoding data into varchar CSV or TSV fields and then splitting them up on the SQL server side.And, for OLTP purposes, XML is clearly superior to BCP. Again, I didn't mean to imply that XML would be great for data loading, just for transactions.As for XML being set-based, well, I guess it depends on the implementation of the parser. I'm not the SQL guru that you or many folks around here are (give me a few years!), but I would be surprised if the folks working on SQLXML didn't put a lot of effort into doing set-based operations on the XML wherever possible.Cheers-b |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2003-06-09 : 08:17:12
|
quote:
Sure, I totally agree. I should have been more specific.To rephrase my point: For small sets of data with simple or no relationships, XML has the potential to be the fastest way to communicate with a database because it is inherently set-oriented.
Again, IT IS NOT SET ORIENTED AT ALL. Simply rephrasing the statement to say "for small sets..." doesn't change that fact. In fact, for small sets with no relationships, XML only bloats data with tags that are not needed.quote:
That is, doing 8 inserts or updates with a single XML query will almost certainly be faster than doing 8 individual updates or inserts using ADO or other access methods.
Normally I'd say that's true, but for such a small number of rows there will be no difference in performance, certainly not a noticeable one. You'd have to get up to 100 rows or more before you a difference.quote:
And it's likely to be faster than other hacks, such as encoding data into varchar CSV or TSV fields and then splitting them up on the SQL server side.
Excuse me, but what makes constructing and splitting a CSV a "hack"? You're constructing an XML string in your ASP code, and it's got a ton more bloat than a CSV would have. Not only that, you're using a loop to construct your XML, whereas you can use the following to build CSV's and pass them to SQL Server:Dim oCmdPWBX, oDBPWBXSet oCmdPWBX=Server.CreateObject("ADODB.Command")oCmdPWBX.CommandText="EXECUTE ParsePrefs"oCmdPWBX.Parameters.Append oCmdPWBX.CreateParameter("@user", adVarChar, adParamInput, 8000, join(iBufPrefUserID,","))oCmdPWBX.Parameters.Append oCmdPWBX.CreateParameter("@class", adVarChar, adParamInput, 8000, join(szBufPrefCname,","))oCmdPWBX.Parameters.Append oCmdPWBX.CreateParameter("@keyname", adVarChar, adParamInput, 8000, join(szBufPrefVname,","))oCmdPWBX.Parameters.Append oCmdPWBX.CreateParameter("@keyvalue", adVarChar, adParamInput, 8000, join(szBufPrefValue,","))Set oDBPWBX=GetDB()Set oCmdPWBX.ActiveConnection=oDBPWBXoCmdPWBX.ExecuteoDBPWBX.CloseSet oDBPWBX=NothingSet oCmdPWBX=NothingNo loops, no streams, no XML bloat required. You do need a ParsePrefs stored procedure (or some other name) to handle parsing each variable, but that's not hard:http://www.sqlteam.com/SearchResults.asp?SearchTerms=csvhttp://www.sqlteam.com/forums/topic.asp?TOPIC_ID=19565In fact, with a little work and judicious delimiter usage, you don't even need a temp table or table variable, and can insert all of the values in one operation. Take a look:http://www.sqlteam.com/item.asp?ItemID=2652 id=quote>quote:
And, for OLTP purposes, XML is clearly superior to BCP.
How? In what way is it superior?quote:
Again, I didn't mean to imply that XML would be great for data loading, just for transactions.
You said (twice) that it has the potential to be the fastest method available. In any event, XML has nothing to do with transactions, or vice versa.quote:
As for XML being set-based, well, I guess it depends on the implementation of the parser.
No it doesn't. It's not a set-based structure, process, anything. It's a stream of data delimited with tags, not much different from an HTML document, from a database perspective. Is a web page a set-oriented document?quote:
I'm not the SQL guru that you or many folks around here are (give me a few years!), but I would be surprised if the folks working on SQLXML didn't put a lot of effort into doing set-based operations on the XML wherever possible.
Regardless of their effort, XML is not set-based, you cannot do set-based operations on it until it is parsed. The HTML comparison is a lot more applicable than you might think. Does it make sense to convert your data into an HTML web page format, THEN transmit it to SQL Server, just so it can parse it and load it?You mentioned earlier a problem with XML, that it "lures" people into believing they need elaborate messaging layers. I think you've fallen under the additional lure of believing that XML is needed because it does so much more than a simple CSV can, when in fact, for what you're doing here, you don't need anything more than a CSV, and using anything more is only a waste of time and space. XML might not perform worse than CSV for what you have now, but it will if your needs expand to larger sets of data, and if they're not, again, I'd suggest leaving XML alone and using the simplest, lowest-weight method that gets the job done. |
|
|
|
|
|
|
|
|
Fastest way for multiple inserts?