SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Sending multiple rows to the Database from an Application: Part II


Sending multiple rows to the Database from an Application: Part II

Author
Message
johnt 57530
johnt 57530
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 36
We take a different approach not mentioned in these articles, and it seems to work very well for our always-connected client/server apps.

We do a lot of large/wide data set processing in stored procedures, and passing huge text or XML files from the UI did not seem to be the best approach.

Rather than using XML or table functions, we use temp tables. From within the application we create/append data to a temp table with a unique identifier for that "batch". Then we simply call a stored procedure, passing the batch_id, and the multi-row data set is available for use by the proc for whatever processing needs to be performed.

The temp table is SPID specific, so there is no risk of data collision between users, nor between multiple processes for the same user if multi-threading (because of the batch_id). Once the proc is done with the data (which is almost always the case), it simply purges that data from the temp table. If the temp table become empty, it is dropped all together, so the overhead on the server is very low.

Yes, the initial loading of the temp tables is performed row-by-row behind the scenes, but that is achieved with a single line of code from the app.
brent.kremer
brent.kremer
Grasshopper
Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)

Group: General Forum Members
Points: 16 Visits: 181
I am also using this same method in several places. This is great for when you want all the records to succeed or none. I would rather control the transaction rollback in a procedure than let the UI.

This is a great article. Thanks for posting.
JimO-487906
JimO-487906
SSC Rookie
SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)

Group: General Forum Members
Points: 30 Visits: 57
Disregarding that send multiple elements at once is questionable design, you con statements regard XML show your lack of comfort with XML.

Using OpenXML or XQuery is just as obvious and natural as T-Sql once one spends equivalent the time using.

Creating XML is superbly easy with .NET languages which have built in methods for dataset. writeXML.

Your article shows a lack of time spent working with XML.
dmoldovan
dmoldovan
Say Hey Kid
Say Hey Kid (674 reputation)Say Hey Kid (674 reputation)Say Hey Kid (674 reputation)Say Hey Kid (674 reputation)Say Hey Kid (674 reputation)Say Hey Kid (674 reputation)Say Hey Kid (674 reputation)Say Hey Kid (674 reputation)

Group: General Forum Members
Points: 674 Visits: 1469
In fat that's how I worked - I built the XML in .NET, validated against a schema collection and passing it to a stored procedure for insert / update.
Charles Wannall
Charles Wannall
SSC-Enthusiastic
SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)

Group: General Forum Members
Points: 116 Visits: 31
I can see immediate usefulness in a project where I intend to use it today: I have large tables in Access database spread all over the country. Ancient app, many users. Not going away. But...must centralize the data for control and for management review and reporting. There's more involved obviously, but this little gem of an article shows me how I can organize my transmission into 5K chunks and send them to a stored procedure in SS that dumps them in a holding table - no processing except landing in the dump table. Later, I can follow up on SS with scheduled job to process rows into proper tables.

User in Access has minimum transmission time (no waiting for inserts on server end). Data gets centralized (yes, GUIDs are added at transmission source and used throughout from then on).

Fantastic article. Exactly what I needed at exactly the right moment.

The collection into XML cost about 20 lines, including the routine to convert a recordset data row into an xml node.
Charles Wannall
Charles Wannall
SSC-Enthusiastic
SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)

Group: General Forum Members
Points: 116 Visits: 31
Extremely helpful article. Exactly what I needed at exactly the moment I needed it. See my post in reply above for more detail, but in summary I am shipping data from all over the country from Access mdb's to SQL Server, dropping XML in a holding table, without even processing inserts during the session. Scheduled job follows up later and inserts as appropriate.

Very helpful.

Very low cost in code to implement. Maybe 20 rows or so, and saved a ton of coding for sending values row at a time via parameters on stored procedure. Thank God I hadn't started writing that mess when I found this. Solid gold!!
sholliday
sholliday
SSC-Enthusiastic
SSC-Enthusiastic (199 reputation)SSC-Enthusiastic (199 reputation)SSC-Enthusiastic (199 reputation)SSC-Enthusiastic (199 reputation)SSC-Enthusiastic (199 reputation)SSC-Enthusiastic (199 reputation)SSC-Enthusiastic (199 reputation)SSC-Enthusiastic (199 reputation)

Group: General Forum Members
Points: 199 Visits: 182
Charles Wannall (10/22/2010)
I can see immediate usefulness in a project where I intend to use it today: I have large tables in Access database spread all over the country. Ancient app, many users. Not going away. But...must centralize the data for control and for management review and reporting. There's more involved obviously, but this little gem of an article shows me how I can organize my transmission into 5K chunks and send them to a stored procedure in SS that dumps them in a holding table - no processing except landing in the dump table. Later, I can follow up on SS with scheduled job to process rows into proper tables.

User in Access has minimum transmission time (no waiting for inserts on server end). Data gets centralized (yes, GUIDs are added at transmission source and used throughout from then on).

Fantastic article. Exactly what I needed at exactly the right moment.

The collection into XML cost about 20 lines, including the routine to convert a recordset data row into an xml node.




I would suggest two ideas, both similar.

1. Put a IDataReader on your JetDatabase (Access .mdb file). As your fire-hose read the JetDatabase data, throw the values into a strong dataset.
2. Every X number of rows (make it configurable and experiment).....push the strong dataset.GetXml() to a stored procedure.

Deviation A
1. If you care about bandwidth, zip the dataset xml and send it to a service. Or check out binary methods for serializing datasets.
2. You could have a service which can unzip (or use raw string-xml) to process rows via bulk.

OR

Put your data into simple POCO objects. Add [Serializable] attribute to the class(es).
Binary serialize and ship to Service.

...
I have an older blog entry which describes the first one:
http://sholliday.spaces.live.com/Blog/cns!A68482B9628A842A!527.entry

...



dbuckley-maidt
dbuckley-maidt
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 41
Response to con for XML item, "Difficult to create XML data at the application layer":

Using the ASP.Net C# XmlTextWriter class , I found creating my data in xml format to send to the database fairly simple. XmlTextWriter did all the formatting for me. I could then check the string created and tweak the xmlTextWriter code if need be.

(http://msdn.microsoft.com/en-us/library/system.xml.xmltextwriter.aspx)

I have listed below a sample of the commands I used:

using System.Xml;
using System.IO;

StringWriter writer = new StringWriter();
XmlTextWriter xtWriter = new XmlTextWriter(writer);

xtWriter.WriteStartElement("XML");
xtWriter.WriteStartElement("CutParms");
xtWriter.WriteStartElement("Parm");

//Begin loop

xtWriter.WriteAttributeString("DataYear", ddEventYear.SelectedText);
xtWriter.WriteAttributeString("DataType", ddCertificateType.SelectedValue);
xtWriter.WriteAttributeString("CutType", ddCutTypes.SelectedValue);
xtWriter.WriteAttributeString("RevisedStateList", sJurisdictionList);

//End loop

xtWriter.WriteEndElement();
xtWriter.WriteEndElement();
xtWriter.WriteEndElement();
xtWriter.Close();

sXML = writer.ToString(); // string to send to the database
R.P.Rozema
R.P.Rozema
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1087 Visits: 1685
bruce lee-206043 (8/25/2009)
there is another choice:

Update SQL Server Data by Using XML Updategrams

An updategram is a data structure that you can use to express a change in the data. INSERT, UPDATE, and DELETE commands are represented in an updategram by the difference of the image of the data before and the image of the data after a change.

http://support.microsoft.com/kb/316018

Have you ever tried using it? I did and it performed badly on even a simple xml format(1 level elements with no more than 10 elements per row, closely resembling the underlying table's layout). For more complicated xml formats it is seriously slow (up to taking minutes to update a single record). We found that this is mostly due to the very complex (and huge) T-SQL statements it generates. The idea of being able to select, insert, delete and update all using the same 'meta model' defined in a single xsd, hiding the actual database layout from the application is very nice. But the implementation is far from usable as it is right now.



Posting Data Etiquette - Jeff Moden
Posting Performance Based Questions - Gail Shaw
Hidden RBAR - Jeff Moden
Cross Tabs and Pivots - Jeff Moden
Catch-all queries - Gail Shaw


If you don't have time to do it right, when will you have time to do it over?
Charles Wannall
Charles Wannall
SSC-Enthusiastic
SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)

Group: General Forum Members
Points: 116 Visits: 31
Hmmmmm...OK. I gotta learn some more stuff.

I will investigate this and follow up with whatever I can master, as improvements, when I understand it well enough to apply it.

Awfully nice of you to send it along. It all sounds like improvements worth making.

Thanks ever so much.

Rick
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search