从MySQL到MS Access的1GB数据

本文关键字：1GB 数据 Access MS MySQL | 更新日期: 2023-09-27 18:04:29

情况:我正在创建一个自动任务，查询MySQL(通过ODBC)，并使用OLEDB将结果集插入MS Access数据库(.mdb)。

代码: 

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

问题:

  1. 内存使用率非常高(300MB++)，而MS Access文件大小没有不断变化!

  2. 非常慢!我知道我的查询在几秒钟内执行，但插入过程需要很长时间。

我试过在MS Access中使用准备好的语句，并将值作为参数插入，而不是字符串连接来创建插入查询。然而，我得到这个异常消息:

标准表达式中的数据类型不匹配。

有人知道如何解决这个问题或有更好的方法吗?

从MySQL到MS Access的1GB数据

您可以创建一个使用DoCmd的VBA宏。方法通过ODBC将数据拉入Access数据库。这样做可能会更快、更简单。

要从外部程序或计划任务运行VBA代码，只需初始化Access以使用/x命令行开关打开文件，它将在启动时运行import宏。尽管1gb的数据仍然需要一段时间。我发现David Catriel的一篇文章实现了这种方法。

一个更好的选择是使用不同的数据库引擎后端，如SQL Server Express的免费版本。这样你就有了更多的选择，它也更加健壮。如果需要MS Access表单和报表，可以使用SQL Server创建ADP项目文件，或者可以使用链接表来获取数据。如果满足你的要求，你甚至可以使用Access作为MySQL数据库的前端，而不是复制所有的数据。

不用写代码，你可以求助于SQL Server集成服务(SSIS)，在午餐前完成。它可以作为Visual Studio的扩展，以防您的计算机上没有SQL Server。

使用SSIS，您可以创建一个可重用的SSIS包，可以从命令行或计划任务触发。本指南展示了如何将数据从MySQL拉到SQL Server，但是SQL Server部分应该很容易用Access替换。

为命令执行添加事务的注释。如果事务不是手动控制的，则每次都将自动创建和提交，这是一个耗时的操作

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

为SQL server数据库创建一个DSN(数据源名称)。然后通过打开Microsoft Access数据库并选择从该DSN导入来选择该DSN。您应该能够导入精确的1GB表(模式、数据、所有内容)。

关于使用DSN的更多信息:https://support.office.com/en us/article/link - - sql - server -数据- 0474 - c16d a473 - 4458 - 9 - cf7 f369b78d3db8

或者，您可以使用该DSN链接到SQL server数据库(不导入到Access表)，并完全跳过导入。

INSERT应该是TRANSACTION的一部分吗?在TRANSACTION中通常会加快BULK insert

感谢大家的回答。我刚刚发现了我代码中的主要问题。大量内存使用的原因(问题#1)是ODBC缓存来自MySQL的数据，而不考虑c#方法(DataReader)。该问题通过检查DSN设置中的Don't cache results of forward-only cursors复选框来解决。这也使这个过程稍微快了一些(30%)。然而，Brian Pressler和Egil Hansen提出了更具体的方法。但是由于它们需要软件安装和/或迁移计划，最简单的方法是坚持使用这段代码。

相关文章：