从SQL数据库导入数据到MATLAB：2密尔行需要30秒，但6密尔需要21分钟？

问题描述：

我可以在36秒内使用Matlab的DB工具箱导入200万行。如何在20分钟内导入600万行？从SQL数据库导入数据到MATLAB：2密尔行需要30秒，但6密尔需要21分钟？

以下查询的最终提取步骤大约需要36秒。

q = 'select ... from mytable limit 2000000'; %notice 2 mil limit 
result = exec(conn, q); 
final_result = fetch(result); % Takes about 36 seconds.

我的整个表格有6,097,227行。 但是，如果我做的：

q = 'select ... from mytable'; 
result = exec(conn, q); 
final_result = fetch(result);

MATLAB 完全失去了它最终取一步！ CPU使用率约为500-600％（即正在使用6/8个内核），并且需要永久使用。目前，它设置为10k批次，最终在21分钟内完成。

想法？该怎么办？我真的很努力地想知道这在行数上如何不至少是线性的。我是否跨过了一些奇怪的限制？

顺便说一句：整个查询和导入到R使用PostgreSQL驱动程序等在R中需要大约43秒...和0摆弄。我可以使用ODBC在类似的时间导入Stata。

注意：在上面的查询中...是10个左右的数字变量：一些整数，一些双精度。没有文字。

你可以使用Node.js来进行导入吗？ –

@ vitaly -t我真的不知道Node.js在这里如何应用？（我编辑了一下这个问题，我可能一直不清楚。） –

在这种情况下，我只能在一般情况下提出建议，正如我在下面的答案中所做的那样。 –

答

如果任何人遇到在未来这种类型的问题，我已经找到了巨头 1GB大小的查询，它的速度更快，更稳健：

使用复制或类似写的查询结果到一个CSV文件。
将csv文件读入Matlab（例如带可读的）。

答

这是为像这样的大型进口的一般战略提供建议。如果你使用的任何组件都没有遵循它，那么你自然会遇到问题。

首先，根据记录的平均大小，批量导入1,000到10,000条记录中的记录。

其次，插入每批有一个多行INSERT：

INSERT INTO TABLE(columns...) VALUES (first-insert values), (second-insert values),...

即串联所有的每批记录到一个单一的多行插入并执行它的方式。它将为IO提供巨大的节约。

答

首先，我建议您尝试增加Java堆内存大小。其次，在导入/导出大量数据的情况下，似乎Matlab Database Toolbox可能不是PostgreSQL的高效连接器。这可以通过原生Matlab格式的重要数据转换开销来解释。减少这种开销的方法之一是遵循http://undocumentedmatlab.com/blog/speeding-up-matlab-jdbc-sql-queries中提出的解决方案。但是JDBC本身有一些无法解决的限制。这是在下面的图片很好的说明（事实上，这些照片是对数据的插入，而不是数据检索不会改变任何东西的开销是存在的，不管你通过数据哪个方向）：

这里fastinsert和datainsert性能与batchParamExec 从PgMex之一（见https://pgmex.alliedtesting.com/#batchparamexec了解详细信息）进行比较。第一张图片用于标量数字数据，第二张图片用于数组。每个图的端点对应于到通过相应的方法传递到数据库中的某个最大数据量而没有任何错误。大于最大值（特定于每种方法）的数据量导致“Java堆内存不足”问题（每个实验的Java堆大小在每个图的顶部指定）。有关实验的更多详细信息，请参阅以下 "Performance comparison of PostgreSQL connectors in Matlab" article。

这里的主要原因是PgMex根本不使用JDBC，而是基于libpq，并且在Matlab和PostgreSQL之间提供100％的二进制数据传输，而无需任何文本解析。同时，所有的工作都通过Matlab友好的和本地方式（以矩阵，多维数组，结构和任意其他Matlab格式的形式）完成，因此，不会将Java对象转换为Matlab格式。

关于数据检索的情况，初步实验表明，PgMex比Matlab数据库工具箱快大约3.5倍，用于标量数值数据的最简单情况。这样的代码可以使用PgMex重写，如下所示（我们假设以下所有标记为<>的参数都被填充，查询q固定为正确，并且fieldSpecStr中的类型对应于已存在的mytable的类型相应的数据库）：

% Create the database connection 
dbConn = com.allied.pgmex.pgmexec('connect',[... 
    'host=<yourhost> dbname=<yourdb> port=<yourport> '... 
    'user=<your_postgres_username> password=<your_postgres_password>']); 

% Execute a query 
q = 'select ... from mytable'; 
pgResult = com.allied.pgmex.pgmexec('exec',dbConn,q); 

% Read the results 
nFields=com.allied.pgmex.pgmexec('nFields',pgResult); 
outCVec=cell(nFields,1); 
fieldSpecStr='%<field_type_1> %<field_type_2> ...'; 
inpCVec=num2cell(0:nFields-1); 
[outCVec{:}]=com.allied.pgmex.pgmexec('getf',pgResult,... 
    fieldSpecStr,inpCVec{:});

请另见http://pgmex.alliedtesting.com/#getf对有关的输入和输出参数的格式命令getf细节（包括fieldSpecStr）。总而言之，outCVec 的每个元素包含具有字段valueVec,isNullVec和isValueNullVec的结构。所有这些字段沿着第一维的大小为，等于检索的元组数，valueVec包含各个表字段的值，而isNullVec和isValueNullVec是NULL的指示符。

从SQL数据库导入数据到MATLAB：2密尔行需要30秒，但6密尔需要21分钟？

相关推荐