C# and SQL Server: Parallel vs. Sequential Writes (?)
in [Programming SQL]
|
Prev: PHP Tutorials
Next: Reservered Words
From: Andy O'Neill on 18 Feb 2010 05:29 "Tibor Karaszi" <tibor_please.no.email_karaszi(a)hotmail.nomail.com> wrote in message news:%23DXmLvHsKHA.3536(a)TK2MSFTNGP06.phx.gbl... > 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 Definitely. If at all possible ( and it usually is ) one should do batch processes on the server rather than the client. Writing something that spawns multiple threads just to upload a bit of data is bad on two points. 1) There's a way easier approach that'll be quicker - so you're wasting your time. 2) It's asking for trouble - multiple threads means tricky code which is error prone and a maintenance nightmare. Keep it simple. Make it home for tea time.
From: Jay on 18 Feb 2010 08:24 Can you install just bcp on a client? While I don't see why not, I've never seen a client without all the tools. Also, there is the matter of calling bcp from C#, which creates a prerequisite for bcp in the application - something that should not be done lightly. While I still favor BULK INSERT, your suggestion of SSIS made me ask a different question: Why is this load being done from C# on (what I assume to be) the application client? Give unto Caesar that which is Caesar's and data loads belong to the database server. "Tibor Karaszi" <tibor_please.no.email_karaszi(a)hotmail.nomail.com> wrote in message news:%23DXmLvHsKHA.3536(a)TK2MSFTNGP06.phx.gbl... > 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... >>
From: Tibor Karaszi on 18 Feb 2010 11:22 > Can you install just bcp on a client? While I don't see why not, I've > never seen a client without all the tools. There are various re-distributes, I don't know off the top of my head exactly that mix there is. My point, though, wasn't to recommend BCP.EXE specifically. I was just listing several options, in one specific order. Since C# was mentioned, using the BulkCopy class in ADO.NET might just be the right thing. Or, perhaps this, as you say, is something which shouldn't really be thought of as part of a client process in the first place. -- Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi "Jay" <spam(a)nospam.org> wrote in message news:uLbTl2JsKHA.1796(a)TK2MSFTNGP02.phx.gbl... > Can you install just bcp on a client? While I don't see why not, I've > never seen a client without all the tools. > > Also, there is the matter of calling bcp from C#, which creates a > prerequisite for bcp in the application - something that should not be > done lightly. > > While I still favor BULK INSERT, your suggestion of SSIS made me ask a > different question: Why is this load being done from C# on (what I assume > to be) the application client? Give unto Caesar that which is Caesar's and > data loads belong to the database server. > > "Tibor Karaszi" <tibor_please.no.email_karaszi(a)hotmail.nomail.com> wrote > in message news:%23DXmLvHsKHA.3536(a)TK2MSFTNGP06.phx.gbl... >> 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... >>> > >
From: Kimbo Slice on 18 Feb 2010 16:10 BULK INSERT seems to be a common suggestion. I don't know too much about bulk inserts but a quick glance at the docs seems to indicate that this is used to upload data from a pre-existing text or CSV file. I'm not convinced that this is the appropriate approach because then I'd have the additional overhead of trying to write everything out to a file, copying the file to SQL Server, telling SQL Server to read it back in, and then upload the data. I'm thinking it would be faster to simply upload the data from client to server. The data doesn't originate from a file. It's created by a C# application, sits around in the memory space of the C# app for a few hours, and then later is optionally uploaded to the database. What triggered my question was the fact that when uploading 8,000 records from the C# app to the database, one of the database's eight CPUs gets pegged at 100%. It occured to me that by splitting the upload up into eight batches of 1,000 records, and executing the INSERT commands on eight different threads, it might allow SQL Server to make more effectively use all eight of it's CPUs... or maybe not. Just a thought. Thanks again for the response. David "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: Jay on 18 Feb 2010 16:34
I don't know that inserts can be run in parallel to a non-partitioned table, nor am I sure that you would want to. I'll leave that to the MVP's. Can you run (or have a DBA run) the following and post the result? -- SQL Server 2008, but I think 2005 as well. exec sp_configure 'show advanced options', 1; reconfigure; exec sp_configure 'max degree of parallelism'; exec sp_configure 'show advanced options', 0; reconfigure; "Kimbo Slice" <Jules.Winfield(a)newsgroup.nospam> wrote in message news:Wb2dncR3FoAqMeDWnZ2dnUVZ_jidnZ2d(a)giganews.com... > > BULK INSERT seems to be a common suggestion. I don't know too much about > bulk inserts but a quick glance at the docs seems to indicate that this is > used to upload data from a pre-existing text or CSV file. I'm not > convinced that this is the appropriate approach because then I'd have the > additional overhead of trying to write everything out to a file, copying > the file to SQL Server, telling SQL Server to read it back in, and then > upload the data. I'm thinking it would be faster to simply upload the data > from client to server. > > The data doesn't originate from a file. It's created by a C# application, > sits around in the memory space of the C# app for a few hours, and then > later is optionally uploaded to the database. > > What triggered my question was the fact that when uploading 8,000 records > from the C# app to the database, one of the database's eight CPUs gets > pegged at 100%. It occured to me that by splitting the upload up into > eight batches of 1,000 records, and executing the INSERT commands on eight > different threads, it might allow SQL Server to make more effectively use > all eight of it's CPUs... or maybe not. > > Just a thought. > > Thanks again for the response. > > David > > "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... >> > > |