none
Fastest way to push data to ACCESS RRS feed

  • Question

  • Hi

    All solutions I have seen revolve around a view and a grid like User Interface. In my case there is no UI. No Gridviews, no currencyManager, etc.    The program collects a lot of measurement data and writes out to ACCESS.  I want to migrate this from VB6 DAO - and that wrote very quickly.  I have seen the MERGE technique slammed as SLOW.

    To benchmark different appraoches I opened a table with pre-loaded data. I hava the same structure in an empty table in the database. The test data from the source dataTable is loaded into the target dataTable like this

       for ( int i = 0;i< numRows;i++)
                {
                      object[] foo = drs[i].ItemArray;  // row from source table...
                      ds.Tables[0].LoadDataRow(foo,true);  // add data to target table
                }

    but the ds.HasChanges is always false.

    The plan was to load the target DataTable and then use the adapter.Update method on t the dataset that has the targetTable.

    What are good ways to approach this ?   Thank you !


    andrew

    Thursday, January 17, 2013 5:29 PM

All replies

  • What is drs? Where is your data coming from?

    ~~Bonnie Berent DeWitt [C# MVP]

    geek-goddess-bonnie.blogspot.com

    Friday, January 18, 2013 3:19 PM
  • Hi Bonnie,

    The Data I collect is measurement data from many sensors very quickly.

    Here's some astounding comparisons

    Using DAO in VB6 I can write 300 records in 4 milliseconds.   ( using Declare Function timeGetTime Lib "winmm.dll" () As Long )

    When I save a dataset of equivalent size to access the process is taking nearly 2 seconds - over 400X slower.

    I considered writing a similar test using interop with DAO 3.6.   

    Here's my test code

    OleDbCommand command = new OleDbCommand("SELECT * from BuffTest", AccessConn);
    adapter.SelectCommand = command;
    OleDbCommandBuilder cb = new OleDbCommandBuilder(adapter);
    ds = new DataSet("Buff");
    adapter.Fill(ds, "Buff");
    LoadSampleDataTable(ds.Tables[0]); // 300 rows, 6 columns data type single
    DateTime startUpdateTime = DateTime.Now;
    if (ds.HasChanges())
     {
       int rowsChanged = adapter.Update(ds, "Buff");
     }
    DateTime EndUpdateTime = DateTime.Now;
    double msecToWrite  = EndUpdateTime.Subtract(startUpdateTime).TotalMilliseconds;
    Console.WriteLine("OledbAdapater Update ( write to Access ) " + msecToWrite);

    private static void LoadSampleDataTable(DataTable dt)
    {
               // model new interface
                Random _r = new Random();
                for (int i = 0; i < 300; i++)
                { 
                    DataRow dr = dt.NewRow();
                    dt.Rows.Add(dr);
                    for (int colnr = 0; colnr < 6; colnr++)
                    {
                       int n = _r.Next(100);
                        dr[colnr] = n/4.0f;
                    }
                }
    }


    andrew

    Sunday, January 20, 2013 3:36 AM
  • Andrew,

    Your original post said that ds.HasChanges() was always false. I guess you meant for the code that you posted in your OP, not for the code you just posted in your last post? In any case, you should probably stick an dr.EndEdit() after your second loop that fills your row with random numbers.

    Secondly, don't use the adapter.Update() method. It is slow (as you've found out) because it makes extra roundtrips to the database. Roll your own DataAccess by creating your own Update command, and then looping through each row, setting the parameters and calling command.ExecuteNonQuery(). This is off the top of my head, so beware possible typos:

    command.CommandText = "UPDATE BuffTest SET ColOne = ?, SET ColTwo = ? .... WHERE Key = ?"
    command.Connection.Open();
    foreach(DataRow row in ds.Tables[0].Rows)
    {
        command.Parameters.Clear();
        for (int colnr = 0; colnr < 6; colnr++)
        {
            command.Parameters.AddWithValue(row.Table.Columns[colnr].ColumnName, row[colnr]);
        }
        command.Parameters.Add("Key", row["Key"]);
        command.ExecuteNonQuery();		
    }
    command.Connection.Close();

    I have a 3-part DataAccess series of blog posts that you may or may not be interested in:

    http://geek-goddess-bonnie.blogspot.com/2009/09/dataaccess-part-i.html
    http://geek-goddess-bonnie.blogspot.com/2009/10/dataaccess-part-ii.html
    http://geek-goddess-bonnie.blogspot.com/2009/10/dataaccess-part-iii.html

    Each post adds extra complexity to the Data Access classes, but more flexiblity. The first post is enough to get you going in the right direction and give you a general idea of the concept, but the second post is more useful. The third post gets into using anonymous delegates..


    ~~Bonnie Berent DeWitt [C# MVP]

    geek-goddess-bonnie.blogspot.com


    • Edited by BonnieBMVP Sunday, January 20, 2013 4:42 PM
    Sunday, January 20, 2013 4:21 PM
  • Thanks Bonnie,  Correct. I originally tried to load some canned data from another table into a datatable and then push it into a dataset for the other table.  But that raised another issue with no changes. So I swithched to load the data explicitly via the function.

    I really appreciate you steering me in a better direction.  I look forward to benchmarking the way you suggested and reading the rest of your series !


    andrew

    Sunday, January 20, 2013 11:42 PM
  • Hi Bonnie,

    I am used to seeing UPDATE table_name
    SET column1=value, column2=value2,...
    WHERE some_column=some_value   

    But I have not seen syntax like command.CommandText = "UPDATE BuffTest SET ColOne = ?, SET ColTwo = ? .... WHERE Key = ?"
    Do the ? serve as a kind of wild card ?

    Thanks


    andrew

    Monday, January 21, 2013 5:56 AM
  • Hi Andrew,

    The ? are for your parameters. Access uses "positional parameters", whereas SQL Server uses "named parameters". I believe you could put the parameter names in that UPDATE command instead of the ?, but the bottom line is that they have to be in the same order. In other words, I think you could have used "UPDATE BuffTest SET ColOne = @ColOne, SET ColTwo = @ColTwo .... WHERE Key = @Key"  (I have only ever used SQL Server). Using the @parameters makes it more readable than using ? as long as it will work that way (give it a try and see). You can still add the parameters as I showed using the for loop through the columns.


    ~~Bonnie Berent DeWitt [C# MVP]

    geek-goddess-bonnie.blogspot.com

    Monday, January 21, 2013 6:13 AM
  • First of all, DAO is simply faster than ADO.Net

    In the same way as jumping from the Eiffel tower without a parachute goes faster than with one.

    Secondly, the LoadDataRow uses the Fill, that set all the datarow states to unchanged so the dataset has no changes. 

    Be aware DAO is build around connected datahandling (which fails when there are too many connections). Ado.Net is build around disconnected data and therefore the state of a row has to be recorded.

     

    Success
    Cor


    Tuesday, January 22, 2013 8:37 AM
  • Thank you to both replies. I have looked at several variations of using oledb - both with dataset and explicit update. ( loading from a data table & loading from an array ).  I'd like you to measure for yourself the slowness.   What do you think now about either approach ?

    Setup : Create a access mdb called sim.mdb, create a table called sTest with 6 fields, xducer1,xducer2...xdcuer6

    Drop the following 4 functions into a console app using System.Data.OleDb;

    call WriteStreamTableTestFour()  -> will call createRData 

    call WriteStreamTableTestOne() will call LoadSampleDataTable

    Here are the four functions.

    private static Single [,] createRData(int numReading, int numChannels)
            {
                Single[,] rdata = new float[numReading, numChannels];
                Random _r = new Random();
                for (int i = 0; i < numReading; i++)
                {
                    for (int j = 0; j < numChannels; j++)
                    {
                        int n = _r.Next(100);
                        rdata[i, j] = n / 2.0f;
                    }
                }
                return rdata;
            }
     private static void LoadSampleDataTable(DataTable dt)
            {
               // model new interface
                Random _r = new Random();
                for (int i = 0; i < 300; i++)
                { 
                    DataRow dr = dt.NewRow();
                    dt.Rows.Add(dr);
                    for (int colnr = 0; colnr < 6; colnr++)
                    {
                       int n = _r.Next(100);
                        dr[colnr] = n/4.0f;
                    }
                }
            }

            private static void WriteStreamTableTestFour()
            {
                /*********************
                 * call function to load 2x2 array ( 300 rows )
                 *
                 * This also takes about 2 seconds to write
                 * No difference than command.Parameters.AddWithValue
               
                 *  The overhead to walk the datatable or load from array is about 31 msecs.
                 * ******************************/
                OleDbDataAdapter adapter = new OleDbDataAdapter();

                DateTime startConnectTime = DateTime.Now;
                string AccessConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=Sim.mdb";

                var AccessConn = new OleDbConnection(AccessConnectionString);
                AccessConn.Open();

                OleDbCommand command = new OleDbCommand("SELECT * from sTest", AccessConn);
                adapter.SelectCommand = command;
                OleDbCommandBuilder cb = new OleDbCommandBuilder(adapter);

                DateTime endConnectReadyTime = DateTime.Now;

                DateTime startLoadDataTableTime = DateTime.Now;
                int numReadings = 300;
                int numChannels = 6;
                Single[,] rData = createRData(numReadings, numChannels);
                DateTime EndLoadDataTableTime = DateTime.Now;

                DateTime startUpdateTime = DateTime.Now;

                command.CommandText = "INSERT INTO sTest (xducer1,xducer2,xducer3,xducer4,xducer5,xducer6) values (@xducer1, @xducer2,@xducer3,@xducer4,@xducer5,@xducer6)";
                if (command.Connection.State == ConnectionState.Closed)
                { command.Connection.Open(); }


                command.Parameters.Add("@xducer1", OleDbType.Single);
                command.Parameters.Add("@xducer2", OleDbType.Single);
                command.Parameters.Add("@xducer3", OleDbType.Single);
                command.Parameters.Add("@xducer4", OleDbType.Single);
                command.Parameters.Add("@xducer5", OleDbType.Single);
                command.Parameters.Add("@xducer6", OleDbType.Single);
                // change this to not need to recreate the object...???
                for (int i = 0; i < numReadings; i++)
                {
                    command.Parameters["@xducer1"].Value = rData[i, 0];
                    command.Parameters["@xducer2"].Value = rData[i, 1];
                    command.Parameters["@xducer3"].Value = rData[i, 2];
                    command.Parameters["@xducer4"].Value = rData[i, 3];
                    command.Parameters["@xducer5"].Value = rData[i, 4];
                    command.Parameters["@xducer6"].Value = rData[i, 5];
                    int numRowAdded = command.ExecuteNonQuery();
                }

                command.Connection.Close();
                DateTime EndUpdateTime = DateTime.Now;
                double msecToWrite = EndUpdateTime.Subtract(startUpdateTime).TotalMilliseconds;
                Console.WriteLine("OledbAdapater Update ( write to Access ) " + msecToWrite);
                Console.ReadLine();
            }

      private static void WriteStreamTableTestOne()
            {
                /*********************
                 * Purpose document connection times
                 * document connection times
                 * document write dataTable times
                 *
                 * Assumes data table created.
                 *
                 * ******************************/
                OleDbDataAdapter adapter = new OleDbDataAdapter();
                DataSet ds;
               
                DateTime startConnectTime = DateTime.Now;
                string AccessConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=Sim.mdb";

                var AccessConn = new OleDbConnection(AccessConnectionString);
                AccessConn.Open();
               
                // missing is table creation time //

                OleDbCommand command = new OleDbCommand("SELECT * from sTest", AccessConn);
                adapter.SelectCommand = command;
                OleDbCommandBuilder cb = new OleDbCommandBuilder(adapter);

                ds = new DataSet("Buff");
                adapter.Fill(ds, "Buff");
                // DataTable newDT = LoadSampleDataTable(ds.Tables[0]);
                DateTime endConnectReadyTime = DateTime.Now;

                int rowCountStart = ds.Tables[0].Rows.Count;

                DateTime startLoadDataTableTime = DateTime.Now;
                LoadSampleDataTable(ds.Tables[0]);
                DateTime EndLoadDataTableTime = DateTime.Now;
            
                if (ds.HasChanges())
                {
                    Console.WriteLine("Table has changes");
                    double msecToOpenConnection = endConnectReadyTime.Subtract(startConnectTime).TotalMilliseconds;
                    double msecToLoadTableData = EndLoadDataTableTime.Subtract(startLoadDataTableTime).TotalMilliseconds;
                    Console.WriteLine(string.Format("Changes found prep time for table {0}, LoadTable with freshData {1}",msecToOpenConnection,msecToLoadTableData  ));
                }


                DateTime startUpdateTime = DateTime.Now;
                if (ds.HasChanges())
                {
                    int rowsChanged = adapter.Update(ds, "Buff");
                }

                DateTime EndUpdateTime = DateTime.Now;

                double msecToWrite  = EndUpdateTime.Subtract(startUpdateTime).TotalMilliseconds;
                Console.WriteLine("Oledb AdapaterUpdate ( write to Access ) " + msecToWrite);
                Console.ReadLine();

            }

     


    andrew

    Wednesday, January 23, 2013 5:53 PM
  • Andrew,

    I thought I wrote that DOA is faster than Ado.Net with OleDB, there is no need to proof that. 

    However, if you keep using it, you will be in trouble in newer OS systems. Therefore my sentence of jumping from the Eiffel tower.

    Secondly, the update can be slow especially in framework 1.0

    Moreover, it is extra slow with Access.

    You can try what SQL Express will do for you (and if it is for single user SQL Compact edition), they both are also freeware and you can use for both the dedicated providers. 

    SQLClient SQLCEClient


    Success
    Cor


    • Edited by Cor Ligthert Wednesday, January 23, 2013 6:08 PM
    Wednesday, January 23, 2013 6:06 PM
  • Andrew,

    I think I already mentioned not to use the DataAdapter.Update() ... it makes extra round-trips to the database, so it's definitely going to be slow. Use the command.ExecuteNonQuery(), just like you did in your first set of tests (and like I previously posted).

    I can't test your code (I don't have Access) ... what kind of numbers are you getting?


    ~~Bonnie Berent DeWitt [C# MVP]

    geek-goddess-bonnie.blogspot.com

    Wednesday, January 23, 2013 9:07 PM