1 GB of data from MySQL to MS Access

Question

Situation: I am creating an automatic task that queries MySQL (via ODBC) and inserts the result into the MS Access database (.mdb) using OLEDB.

The code:

OleDbConnection accCon = new OleDbConnection(); OdbcCommand mySQLCon = new OdbcCommand(); try { //connect to mysql Connect(); mySQLCon.Connection = connection; //connect to access accCon.ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;" + @"Data source= " + pathToAccess; accCon.Open(); var cnt = 0; while (cnt < 5) { if (accCon.State == ConnectionState.Open) break; cnt++; System.Threading.Thread.Sleep(50); } if (cnt == 5) { ToolBox.logThis("Connection to Access DB did not open. Exit Process"); return; } } catch (Exception e) { ToolBox.logThis("Faild to Open connections. msg -> " + e.Message + "\\n" + e.StackTrace); } OleDbCommand accCmn = new OleDbCommand(); accCmn.Connection = accCon; //access insert query structure var insertAccessQuery = "INSERT INTO {0} values({1});"; // key = > tbl name in access, value = > mysql query to b executed foreach (var table in tblNQuery) { try { mySQLCon.CommandText = table.Value; //executed mysql query using (var dataReader = mySQLCon.ExecuteReader()) { //variable to hold row data var rowData = new object[dataReader.FieldCount]; var parameters = ""; //read the result set from mysql query while (dataReader.Read()) { //fill rowData with the row values dataReader.GetValues(rowData); //build the parameters for insert query for (var i = 0; i < dataReader.FieldCount; i++) parameters += "'" + rowData[i] + "',"; parameters = parameters.TrimEnd(','); //insert to access accCmn.CommandText = string.Format(insertAccessQuery, table.Key, parameters); try { accCmn.ExecuteNonQuery(); } catch (Exception exc) { ToolBox.logThis("Faild to insert to access db. msg -> " + exc.Message + "\\n\\tInsert query -> " + accCmn.CommandText ); } parameters = ""; } } } catch (Exception e) { ToolBox.logThis("Faild to populate access db. msg -> " + e.Message + "\\n" + e.StackTrace); } } Disconnect(); accCmn.Dispose(); accCon.Close(); 

Problems:

• Memory usage is very high (300MB ++), while the MS Access file size does not change all the time! The insert seems to cache the data, not save it to disk.

• It is very slow! I know that my request is completed in a few seconds, but the insertion process is time consuming.

I tried to use the prepared statement in MS Access and insert the values ​​as parameters instead of the concat string to create an insert request. However, I get this exception message:

Data type mismatch in criteria expression.

Does anyone know how to fix this or have a better approach?

Answer 1

Thanks to everyone for the answers. I just found the main problem in my code. The reason for using large memory (problem # 1) was ODBC, which cached data from MySQL regardless of the C # (DataReader) approach. This issue is resolved by checking the Don't cache results of forward-only cursors in the DSN settings. It also accelerated the process (30%). However, a more specific approach continues to be what Brian Pressler and Egil Hansen have suggested. But since they require software installation and / or a migration plan, the easiest way would be to stick to this part of the code.

Answer 2

You can create a VBA macro that uses the DoCmd.TransferDatabase method to retrieve data through ODBC into your Access database. It will probably be much faster and easier.

To run VBA code from an external program or scheduled task, simply run Access to open the file using the / x command line and run the import macro at startup. GB of data, although it will still take some time. I found an article by David Catriel that implemented this approach .

Better yet, use a different database server, such as the free version of SQL Server Express. Then you have much more options, and it is much more durable. If you need MS Access forms and reports, you can create an ADP project file if you use SQL Server, or you can use linked tables to retrieve your data. You can even use Access as an interface to your MySQL database instead of copying all the data if it meets your requirements.

Answer 3

Instead of writing code, you can contact SQL Server Integration Services (SSIS) and do this before lunch. It is available as an extension for Visual Studio if you do not already have it on your computer with SQL Server.

With SSIS, you can create a reusable SSIS package that can be run from the command line or a scheduled task. This tutorial shows how to retrieve data from MySQL in SQL Server, but part of SQL Server should be easy to replace Access .

Answer 4

some changes with a comment to add a transaction to execute a command. if transactions are not manually controlled, they will be created and committed automatically each time and this will take a lot of time.

  OleDbConnection accCon = new OleDbConnection(); OdbcCommand mySQLCon = new OdbcCommand(); try { //connect to mysql Connect(); mySQLCon.Connection = connection; //connect to access accCon.ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;" + @"Data source= " + pathToAccess; accCon.Open(); var cnt = 0; while (cnt < 5) { if (accCon.State == ConnectionState.Open) break; cnt++; System.Threading.Thread.Sleep(50); } if (cnt == 5) { ToolBox.logThis("Connection to Access DB did not open. Exit Process"); return; } } catch (Exception e) { ToolBox.logThis("Faild to Open connections. msg -> " + e.Message + "\\n" + e.StackTrace); } //AMK: transaction starts here var transaction = accCon.BeginTransaction(); OleDbCommand accCmn = new OleDbCommand(); accCmn.Connection = accCon; accCmn.Transaction = transaction; //access insert query structure var insertAccessQuery = "INSERT INTO {0} values({1});"; // key = > tbl name in access, value = > mysql query to b executed foreach (var table in tblNQuery) { try { mySQLCon.CommandText = table.Value; //executed mysql query using (var dataReader = mySQLCon.ExecuteReader()) { //variable to hold row data var rowData = new object[dataReader.FieldCount]; var parameters = ""; //read the result set from mysql query while (dataReader.Read()) { //fill rowData with the row values dataReader.GetValues(rowData); //build the parameters for insert query for (var i = 0; i < dataReader.FieldCount; i++) parameters += "'" + rowData[i] + "',"; parameters = parameters.TrimEnd(','); //insert to access accCmn.CommandText = string.Format(insertAccessQuery, table.Key, parameters); try { accCmn.ExecuteNonQuery(); } catch (Exception exc) { ToolBox.logThis("Faild to insert to access db. msg -> " + exc.Message + "\\n\\tInsert query -> " + accCmn.CommandText); } parameters = ""; } } //AMK: transaction commits here if every thing is going well transaction.Commit(); } catch (Exception e) { ToolBox.logThis("Faild to populate access db. msg -> " + e.Message + "\\n" + e.StackTrace); //AMK: transaction rollback here if there is a problem transaction.Rollback(); } } Disconnect(); accCmn.Dispose(); accCon.Close(); 

Answer 5

Create a DSN (data source name) for the SQL server database. Then select this DSN by opening the Microsoft Access database and choosing to import from this DSN. You should be able to import the exact 1GB table (schema, data, everything).

Additional information on using DSN: https://support.office.com/en-us/article/Link-to-SQL-Server-data-0474c16d-a473-4458-9cf7-f369b78d3db8

Alternatively, you can simply reference the SQL Server database (do not import into the Access table) using this DSN and generally skip the import.

Answer 6

If INSERT will be part of the transaction. Being inside a transaction usually speeds up BULK INSERTS