Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««12345»»»

Sending multiple rows to the Database from an Application: Part I Expand / Collapse
Author
Message
Posted Sunday, August 23, 2009 10:34 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, July 29, 2011 4:14 AM
Points: 318, 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.)
Post #775765
Posted Tuesday, August 25, 2009 6:42 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, October 18, 2010 7:59 AM
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.



Post #776608
Posted Tuesday, August 25, 2009 7:43 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, October 21, 2010 9:50 AM
Points: 24, 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/)
Post #776680
Posted Tuesday, August 25, 2009 10:29 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, July 29, 2011 4:14 AM
Points: 318, 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.
Post #777248
Posted Friday, October 15, 2010 4:04 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, April 18, 2014 6:12 AM
Points: 2, Visits: 67
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.
Post #1005008
Posted Friday, October 15, 2010 4:23 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, July 29, 2011 4:14 AM
Points: 318, 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
Post #1005022
Posted Friday, October 15, 2010 5:25 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, June 17, 2011 9:01 AM
Points: 40, 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.
Post #1005058
Posted Friday, October 15, 2010 6:31 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, April 18, 2014 8:45 AM
Points: 13, Visits: 469
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?
Post #1005095
Posted Friday, October 15, 2010 6:57 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Monday, March 03, 2014 7:07 AM
Points: 3,622, Visits: 330
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...


Post #1005123
Posted Friday, October 15, 2010 7:08 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 5:07 PM
Points: 4,128, Visits: 5,840
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
Post #1005136
« Prev Topic | Next Topic »

Add to briefcase «««12345»»»

Permissions Expand / Collapse