Best Practice for Uploading to SQL Server?

[The Oracle]

Hi there,

I have an ArrayList containing approximately 20,000 database INSERT
command objects. Each of these commands needs to be executed. The commands
do not need to be executed in any particular order, nor do they need to be
part of a transaction.

I'm currently accomplishing this task by iterating through the ArrayList
via the foreach construct and Executing each command. This works fine but
I'm wondering if there's a way to get the upload to execute faster. Here's
why I ask: During the upload:

- The CPU useage on the uploader machine is very low.
- The CPU useage on the database machine is very low.
- The network bandwidth between machines isn't saturated.

Since neither computer is even breathing hard during this operation, it
leads me to believe that there is room for optimization. Here's one approach
that I'm considering:

Store the commands in a Queue instead of an ArrayList. Spawn multiple
threads. Each thread attempts to pop a command off the Queue and execute it
using its own database connection. The threads will keep working until the
Queue is empty. Am I on the right track? If so, how many threads should I
spawn?

I feel like there should be a way to speed up my upload but I'm not
sure how to proceed. I know that there is a program called BCC designed for
this sort of thing, but in my case, I want everything to be done in my C#
app instead of shelling out to another app.

Thanks,

The Oracle

[Val Mazur \(MVP\)]

Hi,

Yes, you could do it in a batch if you send your content into stored
procedure as XML string and process it there. Here are some examples how to
do this

http://support.microsoft.com/default...b;en-us;316244

http://support.microsoft.com/default...b;en-us;555266

--
Val Mazur
Microsoft MVP

http://xport.mvps.org

"The Oracle" <9t******@Building502.TheMatrix.com> wrote in message
news:8o********************@giganews.com...

Hi there,

I have an ArrayList containing approximately 20,000 database INSERT
command objects. Each of these commands needs to be executed. The commands
do not need to be executed in any particular order, nor do they need to be
part of a transaction.

I'm currently accomplishing this task by iterating through the
ArrayList via the foreach construct and Executing each command. This works
fine but I'm wondering if there's a way to get the upload to execute
faster. Here's why I ask: During the upload:

- The CPU useage on the uploader machine is very low.
- The CPU useage on the database machine is very low.
- The network bandwidth between machines isn't saturated.

Since neither computer is even breathing hard during this operation, it
leads me to believe that there is room for optimization. Here's one
approach that I'm considering:

Store the commands in a Queue instead of an ArrayList. Spawn multiple
threads. Each thread attempts to pop a command off the Queue and execute
it using its own database connection. The threads will keep working until
the Queue is empty. Am I on the right track? If so, how many threads
should I spawn?

I feel like there should be a way to speed up my upload but I'm not
sure how to proceed. I know that there is a program called BCC designed
for this sort of thing, but in my case, I want everything to be done in my
C# app instead of shelling out to another app.

Thanks,

The Oracle