StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POHow to improve performance by processing database results in parallel?
text
Body
copied!<p>I have a .net application which runs in the region of 20 to 30 SQL queries and processes the results 1 at a time. I have been trying to increase performance by doing some work in parallel.</p> <p>2 of the queries take 75% of the time, purely because of the amount of data they return. My initial experiments have been to try to split these queries into 4 buckets using ntile and process each datareader in parallel. If anything this takes a lot longer, I think because of the extra work involved using NTILE + querying the DB 4 times instead of 1.</p> <p>Can anyone suggest other techniques to try or am I just wasting my time here? The code below is part of a utility class which allows me to queue up the functions which process the reader. So using my NTILE experiment I queue up 4 tasks each processing 1/4 of the data (where ntile =1, 2, 3, 4) and call Execute to run them in parallel.</p> <pre><code>foreach (var keyValuePair in m_Tasks) { var sql = keyValuePair.Key; var task = keyValuePair.Value; var conn = new OracleConnection(ConnectionString); conn.BeginOpen(o=> { conn.EndOpen(o); var cmd = conn.CreateCommand(); cmd.CommandText = sql; cmd.BeginExecuteReader(a => { var reader = cmd.EndExecuteReader(a); DateTime endIO = DateTime.Now; Console.WriteLine(TaskName + " " + Thread.CurrentThread.ManagedThreadId + " IO took: " + (endIO - startTime) + " ended at " + endIO); DateTime taskStart = DateTime.Now; task(reader); DateTime endTAsk = DateTime.Now; Console.WriteLine(TaskName + " " + Thread.CurrentThread.ManagedThreadId + " TAsk took: " + (endTAsk - taskStart) + " ended at " + endTAsk); reader.Close(); conn.Close(); if (Interlocked.Decrement(ref numTasks) == 0) { finishedEvent.Set(); } }, null); }, null ); } finishedEvent.WaitOne(); DateTime endExecute = DateTime.Now; Console.WriteLine(TaskName + " " + Thread.CurrentThread.ManagedThreadId + " EXECUTE took: " + (endExecute - startTime) + " ended at " + endExecute); } </code></pre> <p>Thanks for any help.</p>

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