sql - 如何使用MATLAB和JDBC加快表检索的速度？

Question

我正在使用MATLAB调用的JDBC访问PostGreSQL 8.4数据库。
我感兴趣的表基本上由不同数据类型的各种列组成。通过时间戳选择它们。

由于我想检索大量数据，因此我正在寻找一种使请求比现在更快的方法。



我目前正在做的事情如下：
首先，我建立与数据库的连接，并将其称为DBConn。下一步将是准备一个Select-Statement并执行它：

QUERYSTRING = ['SELECT * FROM ' TABLENAME '...
WHERE ts BETWEEN ''' TIMESTART ''' AND ''' TIMEEND ''''];

QUERY = DBConn.prepareStatement(QUERYSTRING);
RESULTSET = QUERY.executeQuery();

然后，我将列类型存储在变量COLTYPE中（对于 FLOAT为1，对于 BOOLEAN为-1，其余为0-几乎所有列都包含 FLOAT）。下一步是逐行处理每一行，并通过相应的方法检索数据。 FNAMES包含表的字段名称。

m=0; % Variable containing rownumber

while RESULTSET.next()
  m = m+1;

  for n = 1:length(FNAMES)

    if COLTYPE(n)==1 % Columntype is a FLOAT
      DATA{1}.(FNAMES{n})(m,1) = RESULTSET.getDouble(n);
    elseif COLTYPE(n)==-1 % Columntype is a BOOLEAN
      DATA{1}.(FNAMES{n})(m,1) = RESULTSET.getBoolean(n);
    else
      DATA{1}.(FNAMES{n}){m,1} = char(RESULTSET.getString(n));
    end

  end

end

完成请求后，我将关闭语句和连接。

我没有MATLAB数据库工具箱，所以我正在寻找没有它的解决方案。



我了解要求每个字段的数据都非常无效。但是，我仍然没有找到一次一次获取更多数据的方法-例如同一列的多行。有什么办法吗？您还有其他加快请求的建议吗？

Answer 1

摘要

为了加快速度，请使用“数据库工具箱”或自定义Java代码将循环以及您的列数据类型转换向下推送到Java层。 Matlab到Java的方法调用开销可能是杀死您的原因，并且无法使用纯JDBC进行块获取（一次调用中有多行）。确保所使用的JDBC驱动程序上的旋钮设置正确。然后优化昂贵的列数据类型（如字符串和日期）的传输。

（注意：我尚未使用Postgres进行此操作，但已使用其他DBMS进行此操作，这也将适用于Postgres，因为其中大部分与它上面的JDBC和Matlab层有关。）

细节

推送循环到Java以获取块

最快速的方法是将行和列上的循环向下推送到Java层，并使它将数据块（例如一次100或1000行）返回到Matlab层。从Matlab调用Java方法会产生大量的每次调用开销，并且会在M代码中循环JDBC调用（请参阅Is MATLAB OOP slow or am I doing something wrong?-完全公开：这就是我的答案）。如果要像这样从M代码调用JDBC，则会在每一行的每一列上产生开销，这可能是目前执行时间的大部分。

JDBC API本身不像ODBC那样支持“块游标”，因此您需要将该循环深入到Java层。像Oleg建议的那样使用数据库工具箱是一种实现方法，因为它们在Java中实现了较低级的游标。 （可能正是出于这个原因。）但是，如果您没有数据库工具箱的依赖关系，则可以编写自己的瘦Java层，然后从M代码中调用它。 （可能通过与您的自定义Java代码耦合的Matlab类，并且知道如何与之交互。）使Java代码和Matlab代码共享一个块大小，使用原始数组而不是在Java端缓冲整个块尽可能使用列缓冲区的对象数组，并让您的M代码分批提取结果集，将这些块缓冲在原始列数组的单元格数组中，然后将它们串联在一起。

Matlab层的伪代码：

colBufs = repmat( {{}}, [1 nCols] );
while (cursor.hasMore())
    cursor.fetchBlock();
    for iCol = 1:nCols
        colBufs{iCol}{end+1} = cursor.getBlock(iCol); % should come back as primitive
    end
end
for iCol = 1:nCols
    colResults{iCol} = cat(2, colBufs{iCol}{:});
end

旋转JDBC DBMS驱动程序旋钮

确保您的代码向M代码层公开了特定于DBMS的JDBC连接参数，并使用它们。阅读适用于您特定DBMS的doco并适当地摆弄它们。例如，Oracle的JDBC驱动程序默认将默认的提取缓冲区大小（在其JDBC驱动程序内部，而不是您正在构建的）设置为大约10行，这对于典型的数据分析集大小而言太小了。 （每次缓冲区满时，都会导致数据库往返网络。）将其简单地设置为1,000或10,000行就像打开出厂时设置为“ off”的“ Go Fast”开关一样。使用样本数据集对速度进行基准测试，并绘制结果图形以选择适当的设置。

优化列数据类型传输

除了为您提供块获取功能外，编写自定义Java代码还提供了针对特定列类型进行优化类型转换的可能性。处理完每行和每单元的Java调用开销后，您的瓶颈可能会出现在日期解析中，并将字符串从Java传递回Matlab。通过将日期类型转换为Java，可以通过将SQL日期类型转换为正在缓冲的日期类型转换为Matlab datenum（随着Java加倍，带有列类型指示符），从而将日期解析向下推送到Java中，也许使用缓存来避免重新计算重复日期。同一套。 （请注意 TimeZone问题。请考虑使用Joda-Time。）在Java端将所有 BigDecimal转换为 double。而且，cellstr是一个很大的瓶颈-单个char列可能会淹没几个float列的成本。如果可以（返回大Java char[]然后使用 reshape()），则返回窄的CHAR列作为2-d字符而不是cellstrs，并在必要时在Matlab端转换为 cellstr。 （将Java String[]转换为 cellstr的效率较低。）并且您可以通过将低基数字符列作为“符号”传递回去来优化对低基数字符列的检索-在Java方面，建立唯一字符串列表值并将其映射为数字代码，然后将字符串作为数字代码的原始数组以及该数字映射-> string返回；在Matlab端将不同的字符串转换为cellstr，然后使用索引将其扩展为完整数组。这将更快，并为您节省大量内存，因为写时复制优化将为重复的字符串值重用相同的原始char数据。或将它们转换为 categorical或 ordinal对象，而不是cellstrs（如果适用）。如果您使用大量字符数据并具有较大的结果集，则此符号优化可能会是一个大赢家，因为这样一来，您的字符串列将以大约原始数字的速度传输，这实际上要快得多，并且可以减少cellstr的典型内存碎片。 （数据库工具箱现在也可能支持其中一些功能。我已经有两年没有实际使用它了。）

之后，根据您的DBMS，可以通过将DBMS支持的所有数字列类型变量的映射包括到Matlab中的适当数字类型，并尝试在模式中使用它们或在SQL内进行转换来提高速度。查询。例如，在这样的db / Matlab堆栈中，Oracle的 BINARY_DOUBLE可能比正常的 NUMERIC快一点。 YMMV。

您可以考虑通过使用便宜的数字标识符替换字符串和日期列来优化针对该用例的架构，这些数字和标识符可能作为外键来分隔查找表以将其解析为原始字符串和日期。可以使用足够的架构知识在客户端缓存查找。

如果您想发疯，则可以在Java级别使用多线程，以使其异步预取并在单独的Java工作线程上解析下一个结果块，如果您有一个大的列，则可能并行处理每列日期和字符串处理游标块大小，而您正在为前一个块执行M代码级处理时。但是，这确实提高了难度，并且在理想情况下，这是一个小小的性能胜利，因为您已经将昂贵的数据处理推到了Java层。最后保存。并检查JDBC驱动程序doco；它可能已经为您有效地做到了。

杂

如果您不愿意编写自定义Java代码，则仍可以通过将Java方法调用的语法从 obj.method(...)更改为 method(obj, ...)来提高速度。例如。 getDouble(RESULTSET, n)。这只是一个奇怪的Matlab OOP怪癖。但这并不会有太大的成功，因为您仍然需要为每次调用支付Java / Matlab数据转换的费用​​。

另外，考虑更改代码，以便可以在SQL查询中使用 ?占位符和绑定参数，而不是将字符串作为SQL文字插入。如果您正在执行自定义Java层，则定义自己的@connection和@preparedstatement M代码类是一种不错的方法。所以看起来像这样。

QUERYSTRING = ['SELECT * FROM ' TABLENAME ' WHERE ts BETWEEN ? AND ?'];
query = conn.prepare(QUERYSTRING);
rslt = query.exec(startTime, endTime);

这将为您提供更好的类型安全性和更易读的代码，还可以减少查询解析的服务器端开销。在只有几个客户端的情况下，这不会给您带来很大的提速，但是会使编码变得更容易。

定期剖析和测试您的代码（在M代码和Java级别上），以确保您的瓶颈处在您认为的位置，并查看是否需要根据数据集大小调整参数，无论是在行数，列数和类型的术语。我还喜欢在Matlab和Java层上构建一些检测和日志记录，以便您可以轻松进行性能测量（例如，让它汇总分析不同列类型所花费的时间，Java层中的多少以及Java层中的多少）。 Matlab层，以及等待服务器响应的时间（可能由于流水处理而等待的时间不多，但您永远不知道）。如果您的DBMS公开了其内部工具，也许也可以使用它，这样您就可以看到您在服务器端花费的时间。