large dataset!

1 Nov 2005 11:19 AM

Francois Malgreve

Hi,

I am using .NET 1.1.
I have a windows application that imports large excel file (can be up to
60.000 rows) into a dataset, transforms the dataset and need to save that
dataset into an SQL Server 2000 DB.

I would like to know what is an efficient way to do this job because if i do
that with a regular datset / dataAdapter, my guess is that if there is
50.000 rows to insert, it will call the SQLCommand 50.000 times which must
be bad performance wise.
Note that the Dataset will always contain new rows and is used to perform an
import type of task. Simply put I need to know how i can import a large
amount of rows into a SQL DB from a windows .NET application.

I was starting to think of saving my dataset into an Excel file and then use
the DTS COM object (dtspck.dll) to transfer the data from the excel file
into the database through a DTS. But I was wondering if there were no way to
do that in pure .NET. I guess that .Net architects thought of people who
need to insert a large amount of rows at the same time... If not I will
continue with my COM DTS thing but I am not so keen to go that way. I looks
like i will use old APIs for a task for which new APIs may already exist.

Thanks for your help.
Best,

Francois Malgreve

1 Nov 2005 1:42 PM

David Browne

Show quote

"Francois Malgreve" <francois.malgreveADgmail.com> wrote in message
news:%23clQtYt3FHA.1276@TK2MSFTNGP09.phx.gbl...
> Hi,
>
> I am using .NET 1.1.
> I have a windows application that imports large excel file (can be up to
> 60.000 rows) into a dataset, transforms the dataset and need to save that
> dataset into an SQL Server 2000 DB.
>
> I would like to know what is an efficient way to do this job because if i
> do
> that with a regular datset / dataAdapter, my guess is that if there is
> 50.000 rows to insert, it will call the SQLCommand 50.000 times which must
> be bad performance wise.
> Note that the Dataset will always contain new rows and is used to perform
> an
> import type of task. Simply put I need to know how i can import a large
> amount of rows into a SQL DB from a windows .NET application.
>
> I was starting to think of saving my dataset into an Excel file and then
> use
> the DTS COM object (dtspck.dll) to transfer the data from the excel file
> into the database through a DTS. But I was wondering if there were no way
> to
> do that in pure .NET. I guess that .Net architects thought of people who
> need to insert a large amount of rows at the same time... If not I will
> continue with my COM DTS thing but I am not so keen to go that way. I
> looks
> like i will use old APIs for a task for which new APIs may already exist.
>

Short of exporting the data and using DTS or BCP you can use:

single-row inserts
-This indeed can be slow. Be sure to use an explicit transaction, and you
might get ~1000 rows/second.

ADO.NET 2.0 SQLBulkCopy
http://msdn2.microsoft.com/en-us/library/system.data.sqlclient.sqlbulkcopy.aspx
A managed library for performing bulk inserts from .NET code.

David

1 Nov 2005 1:47 PM

W.G. Ryan - MVP

Francois - let me start with the standard disclaimer that ADO.NET in it's
present state is not the ideal data transfer mechanism. If it were me, DTS
would be the answer or BulkCopy - with that said...

60,000 rows is a tremendous amount of data. The Select is not going to be
the heavy part of the operation in terms of firing commands and trips to the
db, although it is going to effectively cache all that data locally which
will be a bit intensive. In the 2.0 Framework, you can set the
UpdateBatchSize property in the DataAdapter to 60,000 for instance and have
it make one trip. You can also use the SqlBulkCopy (Sahil Malik's ADo.NEt
2.0 book covers both of these superbly). For the 1.x framework your options
are limited.

You can turn the EnforceConstraints to off which will give you some
performance benefit, but overall, I'd highly suggest using DTS for this one.

HTH,

Bill
Show quote

"Francois Malgreve" <francois.malgreveADgmail.com> wrote in message
news:%23clQtYt3FHA.1276@TK2MSFTNGP09.phx.gbl...
> Hi,
>
> I am using .NET 1.1.
> I have a windows application that imports large excel file (can be up to
> 60.000 rows) into a dataset, transforms the dataset and need to save that
> dataset into an SQL Server 2000 DB.
>
> I would like to know what is an efficient way to do this job because if i
> do
> that with a regular datset / dataAdapter, my guess is that if there is
> 50.000 rows to insert, it will call the SQLCommand 50.000 times which must
> be bad performance wise.
> Note that the Dataset will always contain new rows and is used to perform
> an
> import type of task. Simply put I need to know how i can import a large
> amount of rows into a SQL DB from a windows .NET application.
>
> I was starting to think of saving my dataset into an Excel file and then
> use
> the DTS COM object (dtspck.dll) to transfer the data from the excel file
> into the database through a DTS. But I was wondering if there were no way
> to
> do that in pure .NET. I guess that .Net architects thought of people who
> need to insert a large amount of rows at the same time... If not I will
> continue with my COM DTS thing but I am not so keen to go that way. I
> looks
> like i will use old APIs for a task for which new APIs may already exist.
>
> Thanks for your help.
> Best,
>
> Francois Malgreve
>
>

2 Nov 2005 7:59 AM

Francois Malgreve

I will then use DTS as I am stuck with .net 1.1 ...

Thanks to everybody for your input.

Francois.

Show quote

"Francois Malgreve" <francois.malgreveADgmail.com> wrote in message
news:#clQtYt3FHA.1276@TK2MSFTNGP09.phx.gbl...
> Hi,
>
> I am using .NET 1.1.
> I have a windows application that imports large excel file (can be up to
> 60.000 rows) into a dataset, transforms the dataset and need to save that
> dataset into an SQL Server 2000 DB.
>
> I would like to know what is an efficient way to do this job because if i
do
> that with a regular datset / dataAdapter, my guess is that if there is
> 50.000 rows to insert, it will call the SQLCommand 50.000 times which must
> be bad performance wise.
> Note that the Dataset will always contain new rows and is used to perform
an
> import type of task. Simply put I need to know how i can import a large
> amount of rows into a SQL DB from a windows .NET application.
>
> I was starting to think of saving my dataset into an Excel file and then
use
> the DTS COM object (dtspck.dll) to transfer the data from the excel file
> into the database through a DTS. But I was wondering if there were no way
to
> do that in pure .NET. I guess that .Net architects thought of people who
> need to insert a large amount of rows at the same time... If not I will
> continue with my COM DTS thing but I am not so keen to go that way. I
looks
> like i will use old APIs for a task for which new APIs may already exist.
>
> Thanks for your help.
> Best,
>
> Francois Malgreve
>
>