StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POMultiple threads for optimal performance problem
text
Body
copied!<p>I've got a huge batch operation that's run every few months parsing and importing from text files into a Sql Server database. The process takes several days to complete and I'm looking at ways to speed it up a bit. Approximately 1/3 of the time is parsing the text, and 2/3 of the time in the database I/O.</p> <p>I thought a simple solution would be to split these out into separate threads. So while one thread is writing to the database the other can be parsing the text. I changed the code to build up a list of SqlCommand objects that need to be executed, these are then passed to a new thread for execution once parsing is complete.</p> <p>On a small sample executing the batch of SqlCommand objects in a single thread takes 37 seconds, I was then surprised when I switched to execute these in a separate thread that the process slowed down massively, taking a total of 63.34 seconds. I did some poking around and eventually decided to run some performance analysis in Visual Studio. I ran Instrumentation to measure timing on the multi threaded version and was amazed when it ran in 31.04 seconds. I reran all tests several times with more or less the same results. So it appears than when running performance analysis the splitting on the workload improves performance, but when not running performance analysis it slows down.</p> <p>If anyone can help indicate what might be causing this and where I should be looking to fix it that would be fantastic!</p> <p>The tests are being run in a quad core VMware virtual machine running on a 6 core host.</p> <p>edit: Having looked into this further the offending lines appears to be the ones related to the parsing and nothing to do with the DB, mainly fileText.Trim(). Why these should run much slower with the debugger attached I have no idea.</p> <p><strong>Code the launches the new thread</strong></p> <pre><code> while (sqlWriterThread != null && sqlWriterThread.ThreadState == ThreadState.Running) Thread.Sleep(0); if (sqlWriterThread == null || sqlWriterThread.ThreadState == ThreadState.Stopped) { sqlWriterThread = new Thread(new ParameterizedThreadStart(SqlWriterThread)); sqlWriterThread.Name = "SqlWriterThread"; sqlWriterThread.Priority = ThreadPriority.Highest; } sqlWriterThread.Start(commandBatch); Thread.Sleep(0); </code></pre> <p><strong>Query Execution Code</strong></p> <pre><code> public void SqlWriterThread(object commandBatch) { List<SqlCommand> batch = (commandBatch as List<SqlCommand>); using (SqlConnection connection = new SqlConnection(HelperDatabase.ConnectionString)) { connection.Open(); SqlTransaction transaction = connection.BeginTransaction(); try { foreach (SqlCommand cmd in batch) { cmd.Connection = connection; cmd.Transaction = transaction; cmd.ExecuteNonQuery(); cmd.Dispose(); } transaction.Commit(); } catch { transaction.Rollback(); } } } </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload