mcs26
asked on
C# MutliThreading to Access Database slower than single thread
Hi,
I have a program in C# (please see all the code below) that needs to pull data from an Access database. I have recently started reading about multithreading.
So I thought I would try a quick experiment. In the same access database there are 3 tables with price data. I wanted to see how much quicker it would be using multithreading than a single thread. However my results were the opposite, multithreading took 155,395 milliseconds and the singlethread took 61,000 milliseconds.
Can anyone tell me if multithreading data from a database is wise or in my code if I am doing something wrong?
Thanks
Multithreading Code
Single Thread
I have a program in C# (please see all the code below) that needs to pull data from an Access database. I have recently started reading about multithreading.
So I thought I would try a quick experiment. In the same access database there are 3 tables with price data. I wanted to see how much quicker it would be using multithreading than a single thread. However my results were the opposite, multithreading took 155,395 milliseconds and the singlethread took 61,000 milliseconds.
Can anyone tell me if multithreading data from a database is wise or in my code if I am doing something wrong?
Thanks
Multithreading Code
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading;
using System.Diagnostics;
using System.Data;
using System.Data.OleDb;
namespace DatabaseThreading
{
class Program
{
static void Main(string[] args)
{
ManualResetEvent[] doneEvents = new ManualResetEvent[3];
string[] Pair = new string[3];
Pair[0] = "GBPUSD";
Pair[1] = "EURUSD";
Pair[2] = "EURGBP";
Stopwatch S1 = Stopwatch.StartNew();
for (int i=0;i<3;i++)
{
doneEvents[i] = new ManualResetEvent(false);
AccessDataBank AB = new AccessDataBank(doneEvents[i]);
ThreadPool.QueueUserWorkItem(AB.AccessData, Pair[i]);
}
WaitHandle.WaitAll(doneEvents);
S1.Stop();
Console.WriteLine("Time taken: {0}", S1.ElapsedMilliseconds);
Console.ReadLine();
}
}
public class AccessDataBank
{
private ManualResetEvent _doneEvents;
public AccessDataBank(ManualResetEvent doneEvents)
{
_doneEvents = doneEvents;
}
string dbName = "dbFX_1Min_db.mdb";
string cn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=";
string dbPath = @"C:\Users\user\Documents\Markets\VBA Files\dbFX_DBs\";
DataSet PriceSet;
DataTable PriceTable;
OleDbDataAdapter dbAdapter;
public void AccessData(Object PairData)
{
string Pair = (string)PairData;
PriceSet = new DataSet();
Console.WriteLine("Thread {0} has started", Pair);
string Query = @"SELECT [" + Pair + @"_1Min].[DTime],
[" + Pair + @"_1Min].[Open_B],
[" + Pair + @"_1Min].[Open_A],
[" + Pair + @"_1Min].[High_B],
[" + Pair + @"_1Min].[High_A],
[" + Pair + @"_1Min].[Low_B],
[" + Pair + @"_1Min].[Low_A],
[" + Pair + @"_1Min].[Close_B],
[" + Pair + @"_1Min].[Close_A]
FROM [" + Pair + @"_1Min]
WHERE ((([" + Pair + @"_1Min].[DTime])>=#2/1/2009#
And ([" + Pair + @"_1Min].DTime)<=#2/14/2009#))
ORDER BY [" + Pair + @"_1Min].[DTime];";
dbAdapter = new OleDbDataAdapter(Query, cn + dbPath + dbName);
dbAdapter.Fill(PriceSet, "Price_" + Pair);
PriceTable = PriceSet.Tables["Price_" + Pair];
Console.WriteLine("Thread {0} has finished", Pair);
_doneEvents.Set();
}
}
}
Single Thread
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.OleDb;
using System.Diagnostics;
namespace DBThread2
{
class Program
{
static void Main(string[] args)
{
string[] Pair = new string[3];
AccessDataBank AD = new AccessDataBank();
Pair[0] = "GBPUSD";
Pair[1] = "EURUSD";
Pair[2] = "EURGBP";
Stopwatch S1 = Stopwatch.StartNew();
for (int i = 0; i < 3; i++)
{
AD.AccessData(Pair[i]);
}
S1.Stop();
Console.WriteLine("Time taken: {0}", S1.ElapsedMilliseconds);
Console.ReadLine();
}
}
public class AccessDataBank
{
string dbName = "dbFX_1Min_db.mdb";
string cn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=";
string dbPath = @"C:\Users\user\Documents\Markets\VBA Files\dbFX_DBs\";
DataSet PriceSet;
DataTable PriceTable;
OleDbDataAdapter dbAdapter;
public void AccessData(Object PairData)
{
string Pair = (string)PairData;
PriceSet = new DataSet();
Console.WriteLine("Thread {0} has started", Pair);
string Query = @"SELECT [" + Pair + @"_1Min].[DTime],
[" + Pair + @"_1Min].[Open_B],
[" + Pair + @"_1Min].[Open_A],
[" + Pair + @"_1Min].[High_B],
[" + Pair + @"_1Min].[High_A],
[" + Pair + @"_1Min].[Low_B],
[" + Pair + @"_1Min].[Low_A],
[" + Pair + @"_1Min].[Close_B],
[" + Pair + @"_1Min].[Close_A]
FROM [" + Pair + @"_1Min]
WHERE ((([" + Pair + @"_1Min].[DTime])>=#2/1/2009#
And ([" + Pair + @"_1Min].DTime)<=#2/14/2009#))
ORDER BY [" + Pair + @"_1Min].[DTime];";
dbAdapter = new OleDbDataAdapter(Query, cn + dbPath + dbName);
dbAdapter.Fill(PriceSet, "Price_" + Pair);
PriceTable = PriceSet.Tables["Price_" + Pair];
Console.WriteLine("Thread {0} has finished", Pair);
}
}
}
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi rambovn,
Thanks for your reply as well. Would you say then that I should not use multithreading when it comes to pulling data from a database?
Cheers
Thanks for your reply as well. Would you say then that I should not use multithreading when it comes to pulling data from a database?
Cheers
It's always depending of more than 1 factor. For example, the database version, the server the database is holding, the network, ...
So immediatly telling that multithreading reading data from a database is not good, is not that easy to tell...
In most cases that we perform, we use multithreading to perform calculations, items that can be split (reading a part and performing whatever needs to be done on it).
What is already a good alternative for slow database access: reading in chunks (paging)
So immediatly telling that multithreading reading data from a database is not good, is not that easy to tell...
In most cases that we perform, we use multithreading to perform calculations, items that can be split (reading a part and performing whatever needs to be done on it).
What is already a good alternative for slow database access: reading in chunks (paging)
ASKER
Thanks for the reply. No the single thread calls a function 3 seperate times pulling data from the same database but different table each time, excalty like the multithread code. Was hoping this should be quicker?
Is there a better way to use multithreading to get data from a database?
Cheers