Hi all,
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.
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.
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.
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);
}
Thanks for any help.