troubleshooting Question

C# MutliThreading to Access Database slower than single thread

Avatar of mcs26
mcs26 asked on
.NET ProgrammingC#Microsoft Development
5 Comments2 Solutions1111 ViewsLast Modified:
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

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);

        }

    }

}
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 2 Answers and 5 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 5 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros