Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
mohd.nizamuddin
mohd.nizamuddin
Old Hand
Old Hand (336 reputation)Old Hand (336 reputation)Old Hand (336 reputation)Old Hand (336 reputation)Old Hand (336 reputation)Old Hand (336 reputation)Old Hand (336 reputation)Old Hand (336 reputation)

Group: General Forum Members
Points: 336 Visits: 198
mohd.nizamuddin (8/18/2009)
Lee Forst:
I haven't tested for it that much records, but our application is working fine dealing with upto 400 - 500 records in an XML.
Will test for more records and post the result.

Hi Lee,
I tested for some 10000 records in XML approach, though my test table has only 3 columns, and it took just 1 sec to complete the insertion.

(Testing Environment: SQL Server Express 2005, P4 2.8GHz, 2GB RAM.)
Balaji_92
Balaji_92
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 8
We already know how to return a set of rows from the database to an application, but we did not have any features provided until SQL Server 2005 that would allow us to send multiple rows to the database.

Actually you could use XML to send multiple rows in SQL Server 2000 too. The only catch is if the database is run on Fibers instead of threads OpenXML would not work.



marcosc
marcosc
SSC Rookie
SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)

Group: General Forum Members
Points: 26 Visits: 41
Hi, good article.
I'm not sure that passing a long string is the best way of doing this. There are some limitations such as max length, sql escaping, performance, delimiter escaping, and the effort to program all this !

I would prefer the combination of BCP + bulk updates:



bcp myfile.txt aux_table
update realtable with_values_in aux_table




even in 3-tier applications you can always put the file "near" the database server.

Bye, Marcos
(author of Nautilus http://sourceforge.net/projects/nautilus/)
mohd.nizamuddin
mohd.nizamuddin
Old Hand
Old Hand (336 reputation)Old Hand (336 reputation)Old Hand (336 reputation)Old Hand (336 reputation)Old Hand (336 reputation)Old Hand (336 reputation)Old Hand (336 reputation)Old Hand (336 reputation)

Group: General Forum Members
Points: 336 Visits: 198
balaji_92
Actually you could use XML to send multiple rows in SQL Server 2000 too. The only catch is if the database is run on Fibers instead of threads OpenXML would not work.


Thanks for your update. Actually it is based upon SQL 2005.


marcosc (8/25/2009)
Hi, good article.
I'm not sure that passing a long string is the best way of doing this. There are some limitations such as max length, sql escaping, performance, delimiter escaping, and the effort to program all this !

Yes, we have limitation of string length, which I mentioned at the end in Pros and Con


I would prefer the combination of BCP + bulk updates:



bcp myfile.txt aux_table
update realtable with_values_in aux_table




even in 3-tier applications you can always put the file "near" the database server.


In the scenario, where this code has been implemented, we cannot use external resource like Disk etc. from the application. Our client has well defined code security policy.
Moreover, we are dealing around 500 records at max at once.
Alexey Voronin
Alexey Voronin
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 227
I was making my own experiments with this on a very busy server in fact. Neither XML nor delimited string of data produce optimal results. The best was the long list of INSERT statements limited only by the query text length limit. The better than that is only a bulk insert from a text file.
mohd.nizamuddin
mohd.nizamuddin
Old Hand
Old Hand (336 reputation)Old Hand (336 reputation)Old Hand (336 reputation)Old Hand (336 reputation)Old Hand (336 reputation)Old Hand (336 reputation)Old Hand (336 reputation)Old Hand (336 reputation)

Group: General Forum Members
Points: 336 Visits: 198
Alexey Voronin (10/15/2010)
I was making my own experiments with this on a very busy server in fact. Neither XML nor delimited string of data produce optimal results. The best was the long list of INSERT statements limited only by the query text length limit. The better than that is only a bulk insert from a text file.


Yes, you are correct.

But in my scenario, where we are updating data offline using an application developed in JAVA. When user gets connected to the database. The application will update those data to the database using either delimited string or XML.

Thanks,
Nizam
olimp23
olimp23
SSC Rookie
SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)

Group: General Forum Members
Points: 42 Visits: 41
Nice article with interesting approach. I would use it in SqlServer 2000, but since SqlSever 2005 has a good XML support I would choose XML. In fact I have used XML parameter function to pass 50k+ rows from my Java application to SqlServer. I wonder witch approach is faster.
Alphonse
Alphonse
Grasshopper
Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)

Group: General Forum Members
Points: 14 Visits: 583
Question for Lee Forst:

You say the XML approach was slow over 1000 rows and you rewrite. What approach did you end up using, and how was the performance?
lionfan91
lionfan91
SSCarpal Tunnel
SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)

Group: General Forum Members
Points: 4604 Visits: 429
I'm a bit surprised that an Aug 2009 article updated Oct 2010 wasn't expanded to include SQL 2008 and the user-defined table type option. Anyway, if you're on 2008 don't forget to check into that as a solution as well. It's way cool...



TheSQLGuru
TheSQLGuru
SSCertifiable
SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)

Group: General Forum Members
Points: 5970 Visits: 8313
I consider Erland's guideance on this topic to be an excellent resource: www.sommarskog.se/arrays-in-sql.html

Best,

Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru at GMail
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