Most efficient way to insert 1,000,000 records?

[chrisben]

Hi,

I need to insert more than 500,000 records at the end of the day in a C#
application. I need to finish it as soon as possible.

I created a stored procedure and called it from ADO to insert one by one. It
is kind of slow (seems slower than using a DTS package to import from a file).

Just a general question, in ADO, what will be the MOST efficient way to do
this work. I normally do it as I described. I am using .NET framework 1.1
and SQL Server 2000.

Thanks a lot

Chris

[Nicholas Paldino [.NET/C# MVP]]

Chris,

I would send the parameters over in batches... Unfortunately, ADO.NET
doesn't get command batching for SQL Server until .NET 2.0.

Because of this, I would try and use DTS. What is the format of the
file to begin with? You might have much faster results doing that...

Hope this helps.
--
- Nicholas Paldino [.NET/C# MVP]
- mv*@spam.guard. caspershouse.co m

"chrisben" <ch******@discu ssions.microsof t.com> wrote in message
news:20******** *************** ***********@mic rosoft.com...

Hi,

I need to insert more than 500,000 records at the end of the day in a C#
application. I need to finish it as soon as possible.

I created a stored procedure and called it from ADO to insert one by one.
It
is kind of slow (seems slower than using a DTS package to import from a
file).

Just a general question, in ADO, what will be the MOST efficient way to do
this work. I normally do it as I described. I am using .NET framework 1.1
and SQL Server 2000.

Thanks a lot

Chris

[Willy Denoyette [MVP]]

"chrisben" <ch******@discu ssions.microsof t.com> wrote in message
news:20******** *************** ***********@mic rosoft.com...

Hi,

I need to insert more than 500,000 records at the end of the day in a C#
application. I need to finish it as soon as possible.

I created a stored procedure and called it from ADO to insert one by one.
It
is kind of slow (seems slower than using a DTS package to import from a
file).

Just a general question, in ADO, what will be the MOST efficient way to do
this work. I normally do it as I described. I am using .NET framework 1.1
and SQL Server 2000.

Thanks a lot

Chris

The most efficient and fastest way is using Sqlserver's bcp utility, you can
also use T-SQL BULK INSERT, but anything else will be much slower.

Willy.

[Ignacio Machin \( .NET/ C# MVP \)]

Hi,
"chrisben" <ch******@discu ssions.microsof t.com> wrote in message
news:20******** *************** ***********@mic rosoft.com...

Hi,

I need to insert more than 500,000 records at the end of the day in a C#
application. I need to finish it as soon as possible.
In a C# app or in a DB used by a C# app?
I created a stored procedure and called it from ADO to insert one by one.
It
is kind of slow (seems slower than using a DTS package to import from a
file).

Just a general question, in ADO, what will be the MOST efficient way to do
this work. I normally do it as I described. I am using .NET framework 1.1
and SQL Server 2000.

In short, none, ADO.NET is not intented for this. You have two options, use
bulk copy (bcp.exe ) that comes with SQL server or as Paldino suggested
(that is also my recommended solution) using DTS, DTS can be scripted from
C# so you will have flexibility to change it as needed.
Just create a DTS from enterprise manager, select write package to disk and
later you can load and execute it.

Let me know if you need code, I do this in a couple of deployments.

--
Ignacio Machin,
ignacio.machin AT dot.state.fl.us
Florida Department Of Transportation

[chrisben]

Thanks. I do not know that I can create DTS script from C#. Can you tell me
know how to do that?
One common thing I often need is to import data file with customized name,
like mm_dd_yy_file.c sv. I cannot figure out how to do that in Enerprise
Manager. If I write a C# app to do this job, do you think that I can convert
the app to a DTS script?

Chris

"Ignacio Machin ( .NET/ C# MVP )" wrote:

Hi,
"chrisben" <ch******@discu ssions.microsof t.com> wrote in message
news:20******** *************** ***********@mic rosoft.com...

Hi,

I need to insert more than 500,000 records at the end of the day in a C#
application. I need to finish it as soon as possible.

In a C# app or in a DB used by a C# app?

I created a stored procedure and called it from ADO to insert one by one.
It
is kind of slow (seems slower than using a DTS package to import from a
file).

Just a general question, in ADO, what will be the MOST efficient way to do
this work. I normally do it as I described. I am using .NET framework 1.1
and SQL Server 2000.

In short, none, ADO.NET is not intented for this. You have two options, use
bulk copy (bcp.exe ) that comes with SQL server or as Paldino suggested
(that is also my recommended solution) using DTS, DTS can be scripted from
C# so you will have flexibility to change it as needed.
Just create a DTS from enterprise manager, select write package to disk and
later you can load and execute it.

Let me know if you need code, I do this in a couple of deployments.

--
Ignacio Machin,
ignacio.machin AT dot.state.fl.us
Florida Department Of Transportation

[chrisben]

I am still wondering whether I should ask the firm to convert our
development system from .NET 1.1 to .NET 2.0. It is costly considering the
licences related to studio and third party packages.

In .NET 2.0 ADO, can I gain much better performance using "command
batching"? WHat does it exactly mean of command batching in .NET 2? COuld
you give me an example?

Thanks

Chris
"Nicholas Paldino [.NET/C# MVP]" wrote:

Chris,

I would send the parameters over in batches... Unfortunately, ADO.NET
doesn't get command batching for SQL Server until .NET 2.0.

Because of this, I would try and use DTS. What is the format of the
file to begin with? You might have much faster results doing that...

Hope this helps.
--
- Nicholas Paldino [.NET/C# MVP]
- mv*@spam.guard. caspershouse.co m

"chrisben" <ch******@discu ssions.microsof t.com> wrote in message
news:20******** *************** ***********@mic rosoft.com...

Hi,

I need to insert more than 500,000 records at the end of the day in a C#
application. I need to finish it as soon as possible.

I created a stored procedure and called it from ADO to insert one by one.
It
is kind of slow (seems slower than using a DTS package to import from a
file).

Just a general question, in ADO, what will be the MOST efficient way to do
this work. I normally do it as I described. I am using .NET framework 1.1
and SQL Server 2000.

Thanks a lot

Chris

[Nicholas Paldino [.NET/C# MVP]]

Chris,

If you use command batching, then internally, the SqlCommand will create
a multiple statement command, with the appropriate parameters, and then pass
that to SQL Server. I think that the maximum number of parameters that can
be handled in this way is 2100.

The benefit of this is that it will cause less round trips between the
server and your code, which always improves performance.

However, if you have your data in a file already that is
computer-readable, chances are the suggestion by Willy to use the BULK
INSERT command would work. Either that, or use DTS.
--
- Nicholas Paldino [.NET/C# MVP]
- mv*@spam.guard. caspershouse.co m

"chrisben" <ch******@discu ssions.microsof t.com> wrote in message
news:E8******** *************** ***********@mic rosoft.com...

I am still wondering whether I should ask the firm to convert our
development system from .NET 1.1 to .NET 2.0. It is costly considering the
licences related to studio and third party packages.

In .NET 2.0 ADO, can I gain much better performance using "command
batching"? WHat does it exactly mean of command batching in .NET 2? COuld
you give me an example?

Thanks

Chris
"Nicholas Paldino [.NET/C# MVP]" wrote:

Chris,

I would send the parameters over in batches... Unfortunately,
ADO.NET
doesn't get command batching for SQL Server until .NET 2.0.

Because of this, I would try and use DTS. What is the format of the
file to begin with? You might have much faster results doing that...

Hope this helps.
--
- Nicholas Paldino [.NET/C# MVP]
- mv*@spam.guard. caspershouse.co m

"chrisben" <ch******@discu ssions.microsof t.com> wrote in message
news:20******** *************** ***********@mic rosoft.com...

> Hi,
>
> I need to insert more than 500,000 records at the end of the day in a
> C#
> application. I need to finish it as soon as possible.
>
> I created a stored procedure and called it from ADO to insert one by
> one.
> It
> is kind of slow (seems slower than using a DTS package to import from a
> file).
>
> Just a general question, in ADO, what will be the MOST efficient way to
> do
> this work. I normally do it as I described. I am using .NET framework
> 1.1
> and SQL Server 2000.
>
> Thanks a lot
>
> Chris

[Willy Denoyette [MVP]]

If your data file is a simple csv file, it takes two minutes to build a
commandline script that executes the bcp command using the datafile as is as
input file. Really, there is nothing in the world of SQL server that is
faster than bcp, it's specially designed for bulk inserts.
Willy.

"chrisben" <ch******@discu ssions.microsof t.com> wrote in message
news:11******** *************** ***********@mic rosoft.com...

Thanks. I do not know that I can create DTS script from C#. Can you tell
me
know how to do that?
One common thing I often need is to import data file with customized name,
like mm_dd_yy_file.c sv. I cannot figure out how to do that in Enerprise
Manager. If I write a C# app to do this job, do you think that I can
convert
the app to a DTS script?

Chris

"Ignacio Machin ( .NET/ C# MVP )" wrote:

Hi,
"chrisben" <ch******@discu ssions.microsof t.com> wrote in message
news:20******** *************** ***********@mic rosoft.com...

> Hi,
>
> I need to insert more than 500,000 records at the end of the day in a
> C#
> application. I need to finish it as soon as possible.

In a C# app or in a DB used by a C# app?

> I created a stored procedure and called it from ADO to insert one by
> one.
> It
> is kind of slow (seems slower than using a DTS package to import from a
> file).
>
> Just a general question, in ADO, what will be the MOST efficient way to
> do
> this work. I normally do it as I described. I am using .NET framework
> 1.1
> and SQL Server 2000.

In short, none, ADO.NET is not intented for this. You have two options,
use
bulk copy (bcp.exe ) that comes with SQL server or as Paldino suggested
(that is also my recommended solution) using DTS, DTS can be scripted
from
C# so you will have flexibility to change it as needed.
Just create a DTS from enterprise manager, select write package to disk
and
later you can load and execute it.

Let me know if you need code, I do this in a couple of deployments.

--
Ignacio Machin,
ignacio.machin AT dot.state.fl.us
Florida Department Of Transportation

[chrisben]

Thanks for the advice. I rarely use bcp. If I have a file with name
unchnaged, I usually use DTS. However, if I have file name changed daily, for
example, for today, as 01_04_2006_Data .csv, and 01_05_2006_data .csv for
tomorrow, it is hard for me to handle in either way.

I do not want to rename the file to a fixed name using a script then import,
since it could import stale data. However, I do not know how to script a
changing name like this for a bcp script. That is why I am wondering whether
I can translate a C# app to a DTS or script, if possible. At least, I can
code it in C#.

Any suggestions?
Thanks

Chris

"Willy Denoyette [MVP]" wrote:

If your data file is a simple csv file, it takes two minutes to build a
commandline script that executes the bcp command using the datafile as is as
input file. Really, there is nothing in the world of SQL server that is
faster than bcp, it's specially designed for bulk inserts.
Willy.

"chrisben" <ch******@discu ssions.microsof t.com> wrote in message
news:11******** *************** ***********@mic rosoft.com...

Thanks. I do not know that I can create DTS script from C#. Can you tell
me
know how to do that?
One common thing I often need is to import data file with customized name,
like mm_dd_yy_file.c sv. I cannot figure out how to do that in Enerprise
Manager. If I write a C# app to do this job, do you think that I can
convert
the app to a DTS script?

Chris

"Ignacio Machin ( .NET/ C# MVP )" wrote:

Hi,
"chrisben" <ch******@discu ssions.microsof t.com> wrote in message
news:20******** *************** ***********@mic rosoft.com...
> Hi,
>
> I need to insert more than 500,000 records at the end of the day in a
> C#
> application. I need to finish it as soon as possible.

In a C# app or in a DB used by a C# app?

> I created a stored procedure and called it from ADO to insert one by
> one.
> It
> is kind of slow (seems slower than using a DTS package to import from a
> file).
>
> Just a general question, in ADO, what will be the MOST efficient way to
> do
> this work. I normally do it as I described. I am using .NET framework
> 1.1
> and SQL Server 2000.

In short, none, ADO.NET is not intented for this. You have two options,
use
bulk copy (bcp.exe ) that comes with SQL server or as Paldino suggested
(that is also my recommended solution) using DTS, DTS can be scripted
from
C# so you will have flexibility to change it as needed.
Just create a DTS from enterprise manager, select write package to disk
and
later you can load and execute it.

Let me know if you need code, I do this in a couple of deployments.

--
Ignacio Machin,
ignacio.machin AT dot.state.fl.us
Florida Department Of Transportation

[Willy Denoyette [MVP]]

Nothing stops you from launching the bcp.exe command from C# using
Process.Start.
That way you could supply all command line arguments you need like the in
file name.

Willy.

"chrisben" <ch******@discu ssions.microsof t.com> wrote in message
news:CA******** *************** ***********@mic rosoft.com...

Thanks for the advice. I rarely use bcp. If I have a file with name
unchnaged, I usually use DTS. However, if I have file name changed daily,
for
example, for today, as 01_04_2006_Data .csv, and 01_05_2006_data .csv for
tomorrow, it is hard for me to handle in either way.

I do not want to rename the file to a fixed name using a script then
import,
since it could import stale data. However, I do not know how to script a
changing name like this for a bcp script. That is why I am wondering
whether
I can translate a C# app to a DTS or script, if possible. At least, I can
code it in C#.

Any suggestions?
Thanks

Chris

"Willy Denoyette [MVP]" wrote:

If your data file is a simple csv file, it takes two minutes to build a
commandline script that executes the bcp command using the datafile as is
as
input file. Really, there is nothing in the world of SQL server that is
faster than bcp, it's specially designed for bulk inserts.
Willy.

"chrisben" <ch******@discu ssions.microsof t.com> wrote in message
news:11******** *************** ***********@mic rosoft.com...

> Thanks. I do not know that I can create DTS script from C#. Can you
> tell
> me
> know how to do that?
> One common thing I often need is to import data file with customized
> name,
> like mm_dd_yy_file.c sv. I cannot figure out how to do that in Enerprise
> Manager. If I write a C# app to do this job, do you think that I can
> convert
> the app to a DTS script?
>
> Chris
>
> "Ignacio Machin ( .NET/ C# MVP )" wrote:
>
>> Hi,
>>
>>
>> "chrisben" <ch******@discu ssions.microsof t.com> wrote in message
>> news:20******** *************** ***********@mic rosoft.com...
>> > Hi,
>> >
>> > I need to insert more than 500,000 records at the end of the day in
>> > a
>> > C#
>> > application. I need to finish it as soon as possible.
>>
>> In a C# app or in a DB used by a C# app?
>>
>> > I created a stored procedure and called it from ADO to insert one by
>> > one.
>> > It
>> > is kind of slow (seems slower than using a DTS package to import
>> > from a
>> > file).
>> >
>> > Just a general question, in ADO, what will be the MOST efficient way
>> > to
>> > do
>> > this work. I normally do it as I described. I am using .NET
>> > framework
>> > 1.1
>> > and SQL Server 2000.
>>
>> In short, none, ADO.NET is not intented for this. You have two
>> options,
>> use
>> bulk copy (bcp.exe ) that comes with SQL server or as Paldino
>> suggested
>> (that is also my recommended solution) using DTS, DTS can be scripted
>> from
>> C# so you will have flexibility to change it as needed.
>>
>>
>> Just create a DTS from enterprise manager, select write package to
>> disk
>> and
>> later you can load and execute it.
>>
>> Let me know if you need code, I do this in a couple of deployments.
>>
>>
>>
>> --
>> Ignacio Machin,
>> ignacio.machin AT dot.state.fl.us
>> Florida Department Of Transportation
>>
>>
>>