C# and SQL Server: Parallel vs. Sequential Writes (?)
in [Programming SQL]
|
Prev: PHP Tutorials
Next: Reservered Words
From: Kimbo Slice on 17 Feb 2010 19:54 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 17 Feb 2010 20:00 (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 17 Feb 2010 20:19 (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 17 Feb 2010 20:30 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 18 Feb 2010 04:23
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... > |