查询执行时间MATLAB到MS Access

Question

由于一段时间我正在测试现有MS-Access数据库和MATLAB之间的连接。目前，我有以下本地配置(MATLAB和DB在同一本地驱动器上):
MATLAB 2013a(32位)和MS Access 2007.
在解决了MATLAB 64位的连接问题后，我移动到32位，现在连接正常。连接通过数据库工具箱完成:

conn = database('Test_DB', '', '');

最恼人的是执行时间。我比较了MS Access内的执行时间(直接在数据库中执行查询与运行!按钮)，用MATLAB执行查询并带取数据所用的次数。差别几乎是一个数量级。

通常，我有两个大表(Table1 - 20列x 1'000'000行，Table2 - 10列x 10'000'000行)。查询非常简单，可以根据所选日期组合两个表中的几个字段。内部访问(取决于2003或2007版本)大约需要7到10秒。当从Matlab执行时(SQL命令完全相同)，它需要70到75秒。我尝试了很多东西来了解这里的问题，但没有成功。如果有人知道类似的问题，我很乐意提一些意见。

更具体地说:Matlab 32位版本。2013a, 64位Win 7, i7-3770, 8GB RAM。对于数据库工具箱，我使用ODBC Microsoft Access Driver 6.01.7601.17632, ODBCJT32.DLL日期为23.12.2011。

查询使用两个表T1和T2，如下所示:

strSQL = [ 'SELECT DISTINCT T1.TF1, T1.SI1, T1.SI2, T2.TF2, T1.DATE1 ' ...
           'FROM T2 INNER JOIN T1 ' ...
              'ON T2.TF1 = T1.TF1 ' ...
           'WHERE (((T1.DATE1)=#', date1, '#));'];   

TF1、TF2为文本字段
SI1, SI2是数字(简单)字段
DATE1是日期字段
T1有7,000,000行，2个文本字段，3个数字字段，1个日期字段
T2有13000行，39个文本字段，12个数字字段，1个日期字段

Answer 1

从Matlab中运行它所花费的额外时间可能是将数据从Access引擎中传输出来并将其转换为Matlab数据类型。那里有相当大的阻抗不匹配，Matlab不一定使用最有效的类型。

这是足够慢，听起来你可能正在使用默认的cellarray数据返回格式。这是一种低效的格式，不适合较大的数据集。(或者，恕我直言，大部分事情都是如此。)它将所有列(包括数字)存储在二维单元格数组中。

使用setbdprefs()切换到structure或新的table数据返回格式。这应该会给你一些加速和帮助内存碎片。

setdbprefs('DataReturnFormat', 'table');
conn = database( ... )

(我不确定table是否在R2013a中可用;这是新的。试试它，看看它是否有效;即使在R2014a中也没有很好的记录，在那里它绝对可用。)

在这一点上，字符串和日期列将是您的主要数据传输成本。如果您可以更改查询或模式以返回数字标识符，则可以大大加快速度。如果您有低基数字符串列，您可以将它们转换为@分类变量，以便在Matlab中节省空间。

将日期作为字符串检索开销很大。你希望它们最终成为基准。您可以通过使用用SQL编写的转换表达式，将从SQL DATE到Matlab datenum的转换推到Access层，从而进一步加快这一速度。事实上，在这个问题中，既然你已经固定了T1。DATE1到WHERE子句中的已知值，不要在查询中将其作为列检索。只需将列设置为Matlab层中的已知值。这将节省传输和转换日期值的费用，这是昂贵的。像这样。

setdbprefs('DataReturnFormat', 'table');
conn = database('Test_DB', '', '');
myDates = % ... a list of dates as datenums, not strings
for date = myDates
   sql = [ 'SELECT DISTINCT T1.TF1, T1.SI1, T1.SI2, T2.TF2' ...
           'FROM T2 INNER JOIN T1 ' ...
             'ON T2.TF1 = T1.TF1 ' ...
           'WHERE (((T1.DATE1)=#', datestr(date, 'yyyy-mm-dd'), '#));']; 
   curs = fetch(exec(conn, sql));
   t = curs.Data;
   t.DATE1 = repmat(date, [height(t) 1]);
   % ... do stuff with t ...
end

并尝试使用本机ODBC连接方法。这将节省JDBC-ODBC桥接驱动程序的额外费用，这就是普通database('DSN', '', '')连接所使用的。