Error while updating data through dataset

Hi,
I've a requirement where in I need to read the data from a csv file and
load the data into the sql database. The file has around 1 lakh records. I'm
reading 300 records at a time from the file, loading into a dataset.
From the dataset, I'm reading the rows sequentially,and updating one by one
in to the database. This happens with a sql trnasaction.

The problem is here with the performance.. The file to be loaded exists on
one server and the database exists on another server. The loading process is
too slow
and it breaks with the below error
'The SqlTransaction is closed;it is no longer usable'.
I've searched on the google..but couldn't get appropriate resolution. Can
someone help me out?

Alvin
Mon Jan 28 09:36:54 CST 2008

Why don't u just load the files into the database using a sql script rather
than thru the application? It's a lot more efficient.

--

Regards,
Alvin Bruney [MVP ASP.NET]

[Shameless Author plug]
The O.W.C. Black Book, 2nd Edition
Exclusively on www.lulu.com/owc $19.99
-------------------------------------------------------



"AVL" <AVL@discussions.microsoft.com> wrote in message
news:15B69E60-51AB-4212-AFFE-255E44E42478@microsoft.com...
> Hi,
> I've a requirement where in I need to read the data from a csv file and
> load the data into the sql database. The file has around 1 lakh records.
> I'm
> reading 300 records at a time from the file, loading into a dataset.
> From the dataset, I'm reading the rows sequentially,and updating one by
> one
> in to the database. This happens with a sql trnasaction.
>
> The problem is here with the performance.. The file to be loaded exists on
> one server and the database exists on another server. The loading process
> is
> too slow
> and it breaks with the below error
> 'The SqlTransaction is closed;it is no longer usable'.
> I've searched on the google..but couldn't get appropriate resolution. Can
> someone help me out?

sloan
Mon Jan 28 14:19:22 CST 2008


To help performance, you have two options.

1. Push the entire XML into sql server. Large overhead, and maybe not what
you want.

2. (My suggestion). Instead of pushing the records one by one into the db,
make the number configurable.
For example, lets make it 1000.

Use the IDataReader to loop over the CSV file.
Put 1000 records into the DataSet.
Write a usp (user stored procedure) which accepts @xml_doc text. (which will
be xml).
Do a bulk insert/update using the xml.

You will save alot of time doing this.

There are other options, but based on what you put in your OP, I'd go for
#2.

how do you do a dataset bulk insert/update, you ask?


Go here:
http://groups.google.com/group/microsoft.public.sqlserver.programming/msg/268be0e152d8b876
read the MS links I provide.

The key is to use the ds.GetXml ....to get 1000 records worth records.
Then push those 1000 records into TSQL as xml.
Parse the xml into a @variable or #temp table.
Do your updates/inserts from there.

my uspProductUpdate is a fine tuned version......


Because index rebuilding happens after the 1000 records get
updated/inserted, you save time that way as well.

..

Then experiment with the 1000 number. Maybe 5000, maybe 500 is your sweet
spot number.

..


"AVL" <AVL@discussions.microsoft.com> wrote in message
news:15B69E60-51AB-4212-AFFE-255E44E42478@microsoft.com...
> Hi,
> I've a requirement where in I need to read the data from a csv file and
> load the data into the sql database. The file has around 1 lakh records.
> I'm
> reading 300 records at a time from the file, loading into a dataset.
> From the dataset, I'm reading the rows sequentially,and updating one by
> one
> in to the database. This happens with a sql trnasaction.
>
> The problem is here with the performance.. The file to be loaded exists on
> one server and the database exists on another server. The loading process
> is
> too slow
> and it breaks with the below error
> 'The SqlTransaction is closed;it is no longer usable'.
> I've searched on the google..but couldn't get appropriate resolution. Can
> someone help me out?