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

Question

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

Viewing 15 posts - 16 through 30 (of 43 total)

You must be logged in to reply to this topic. Login to reply

Galina Likhovetsky SSC-Addicted Points: 422 More actions · Answer 1

Hello,

I read your article with interest, because I have to solve similar problem recently. Both solutions are interesting, and both were considered when I was going to write this part of my code.

I found some problems with both of them. First: I have potentially very long strings with rich text tags, so I cound not think about a delimiter, which could be used safely. Also, I know that T-SQL is not very efifcient with string processing, and, as I can possibly have handreds of records to save, it would be slow.

Second: XML is great and easy when you have a dataset to save. In my case, I have objects and it means I have to build XML myself. I tried, by the way, and found it is not very quick as well.

So, our team came up with the third solution. Inside my .NET code we build and SQL string to create and populate TEMP tables, based on properties of my objects, using reflection. Then we execute that in SQL Server, and than call stored procedure to process data from temp table (validate, do some calculations and save to real tables)

mohd.nizamuddin Ten Centuries Points: 1328 More actions · Answer 2

First of all thanks for your valuable comment with different approach.

R.P.Rozema, sql :

Actually in my scenario we can access database only by SP. So we must not use any inline query, so we cannot create a SELECT query using UNION ALL.

timothyawiseman, Scott Roberts:

As far as bulk insert is concern, I think that it is very efficient for very very large dataset. Here we are dealing with some 100 / 500 odd rows.

Galina Likhovetsky:

Actually our application is using JAVA as a language. Yes agree, if it would be at Microsoft platform, definitely I would have suggested and think about your approach.

Darrell Bauer:

The table-valued function [dbo].[TableFormDelimetedStringWithoutNumberList] will choke on lists longer than 100 elements. Here's the error:

Oops I missed it;-). Good catch.

;WITH CTETable (start, stop)

........

WHERE stop > 0

OPTION(MAXRECURSION 0)

you can add this hint to the sql in specified function.

Stephen Byrne:

I think it is not inserting one record at a time rather it is inserting in a set. So you will get a generic error like (data would be truncated, FK or unique constraints voilated etc.)

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.

paulhumphris Hall of Fame Points: 3496 More actions · Answer 3

The code I use to split out delimited list is:

create function [dbo].[Split](@value nvarchar(max), @delimiter varchar(20))

returns @results table (id int identity(1, 1) primary key, value nvarchar(max))

as

begin

declare @p1 int,

@p2 int

-- ensure value ends with the delimiter character

if right(@value, len(@delimiter)) != @delimiter

set @value = @value + @delimiter

set @p1 = 1

set @p2 = charindex(@delimiter, @value)

while @p2 > @p1

begin

insert into @results (value)

select substring(@value, @p1, @p2 - @p1)

set @p1 = @p2 + len(@delimiter)

set @p2 = charindex(@delimiter, @value, @p1)

end

return

end

go

My initial tests show it to perform faster than your CTE based solution, but I would welcome other peoples results.

venablc SSC Enthusiast Points: 110 More actions · Answer 4

Hi,

We have an application which requires anywhere between 10,000 to 500,000 new records to be uploaded into our database at anyone time. For each record we have to go through the process of checking wether it already exists and if so get and ID for it but if not create a new record before retrieving it's ID.

After along time trying all sorts of different methods (including the delimited string method) we now use the approach of creating a "clean" formatted text file on the local box, this is then uploaded to a predefined location by the application. The application then calls an SP which triggers a BCP from the uploaded file straight into a temporary table on our database. Now the data is in the database we call a second SP which loops through the records in the temporary table doing the neccesary work.

Hope this inspires one or two of you 😀

TimothyAWiseman SSCrazy Eights Points: 8819 More actions · Answer 5

venablc, thanks for the post. I use a similar technique for certain large imports. It does not make a lot of sense for small inserts, but it is one of the best ways to deal with very large ones.

---
Timothy A Wiseman
SQL Blog: http://timothyawiseman.wordpress.com/

mohd.nizamuddin Ten Centuries Points: 1328 More actions · Answer 6

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 Newbie Points: 9 More actions · Answer 7

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 SSC Veteran Points: 276 More actions · Answer 8

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/[/url])

mohd.nizamuddin Ten Centuries Points: 1328 More actions · Answer 9

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 SSC Enthusiast Points: 109 More actions · Answer 10

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 Ten Centuries Points: 1328 More actions · Answer 11

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 Valued Member Points: 54 More actions · Answer 12

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 SSC Veteran Points: 243 More actions · Answer 13

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 SSCrazy Eights Points: 8794 More actions · Answer 14

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 SSC Guru Points: 134017 More actions · Answer 15

I consider Erland's guideance on this topic to be an excellent resource: http://www.sommarskog.se/arrays-in-sql.html

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service