From: Kimbo Slice on
Consider:

- My SQL Server has 8 processors (cores actually).
- I want to upload 8,000 records to a single table in my database.

Which of these two would get the job done faster?

(a) spawn 8 different threads in my client application and, using eight
different sql connections, upload 1,000 entries on each thread, or

(b) upload all 8,000 entries from the same thread.

Option (a) would seem to make more effective use of all eight processors on
the SQL Server. But in the final analysis, won't the SQL Server bottleneck
be the transaction log where it has to sequentially serialize all of this
stuff to disk? Seems like (a) wouldn't provide much of an advantage, would
it? In fact, wouldn't it actually be a DISadvantage because of the
additional connections required?

Thanks...


From: Jay on
(c) Use the BULK INSERT statement?
(d) bcp, which I suppose is out since your on a client machine, but still
....

"Kimbo Slice" <Jules.Winfield(a)newsgroup.nospam> wrote in message
news:7a-dnVmcZ6IkEuHWnZ2dnUVZ_gydnZ2d(a)giganews.com...
> Consider:
>
> - My SQL Server has 8 processors (cores actually).
> - I want to upload 8,000 records to a single table in my database.
>
> Which of these two would get the job done faster?
>
> (a) spawn 8 different threads in my client application and, using eight
> different sql connections, upload 1,000 entries on each thread, or
>
> (b) upload all 8,000 entries from the same thread.
>
> Option (a) would seem to make more effective use of all eight processors
> on the SQL Server. But in the final analysis, won't the SQL Server
> bottleneck be the transaction log where it has to sequentially serialize
> all of this stuff to disk? Seems like (a) wouldn't provide much of an
> advantage, would it? In fact, wouldn't it actually be a DISadvantage
> because of the additional connections required?
>
> Thanks...
>


From: Peter Duniho on
(C# newsgroup removed)

Kimbo Slice wrote:
> Consider:
>
> - My SQL Server has 8 processors (cores actually).
> - I want to upload 8,000 records to a single table in my database.
>
> Which of these two would get the job done faster?
>
> (a) spawn 8 different threads in my client application and, using eight
> different sql connections, upload 1,000 entries on each thread, or
>
> (b) upload all 8,000 entries from the same thread.

The only way to know for sure is to try both and measure performance.

It seems to me that option (a) could wind up being _slower_ because
you'd create contention that otherwise wouldn't exist (the actual
connections probably aren't so much an issue as the simultaneous
attempts to modify the database).

Of course, that assumes your client PC is powerful enough to swamp the
server, which one hopes wouldn't be the case (doesn't speak well of the
server if it is :) ).

But note Jay's reply. If you look at the question from a SQL
perspective (*), it's likely the best solution is "neither�work with the
model that SQL provides for best performance".

Pete

(*) (which IMHO you should have�frankly, I was hesitant to reply at all
to this essentially non-C# question posted to the C# newsgroup, with
only the opportunity to point out the only valid way to approach
performance tuning convincing me to do so)
From: Arne Vajhøj on
On 17-02-2010 19:54, Kimbo Slice wrote:
> Consider:
>
> - My SQL Server has 8 processors (cores actually).
> - I want to upload 8,000 records to a single table in my database.
>
> Which of these two would get the job done faster?
>
> (a) spawn 8 different threads in my client application and, using eight
> different sql connections, upload 1,000 entries on each thread, or
>
> (b) upload all 8,000 entries from the same thread.
>
> Option (a) would seem to make more effective use of all eight processors on
> the SQL Server. But in the final analysis, won't the SQL Server bottleneck
> be the transaction log where it has to sequentially serialize all of this
> stuff to disk? Seems like (a) wouldn't provide much of an advantage, would
> it? In fact, wouldn't it actually be a DISadvantage because of the
> additional connections required?

You should go for #a. Or maybe a modified #a with 16 or 32 threads.

If the transaction log is indeed the bottleneck, then you will not
gain much, but if you have a good disk system, then it will be faster.

The overhead of having that number of connections is insignificant.

Arne
From: Tibor Karaszi on
You should definitely go for utilizing the bulk loading code in SQL Server.
There are several tools available to use that code path, such as BCP, BULK
INSERT, SSIS and even as managed code though a BulkCopy object as of ADO.NET
2.0.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi



"Kimbo Slice" <Jules.Winfield(a)newsgroup.nospam> wrote in message
news:7a-dnVmcZ6IkEuHWnZ2dnUVZ_gydnZ2d(a)giganews.com...
> Consider:
>
> - My SQL Server has 8 processors (cores actually).
> - I want to upload 8,000 records to a single table in my database.
>
> Which of these two would get the job done faster?
>
> (a) spawn 8 different threads in my client application and, using eight
> different sql connections, upload 1,000 entries on each thread, or
>
> (b) upload all 8,000 entries from the same thread.
>
> Option (a) would seem to make more effective use of all eight processors
> on the SQL Server. But in the final analysis, won't the SQL Server
> bottleneck be the transaction log where it has to sequentially serialize
> all of this stuff to disk? Seems like (a) wouldn't provide much of an
> advantage, would it? In fact, wouldn't it actually be a DISadvantage
> because of the additional connections required?
>
> Thanks...
>
 |  Next  |  Last
Pages: 1 2 3 4
Prev: PHP Tutorials
Next: Reservered Words