SQL: setoptions for databaseImportOptions very slow

4 次查看（过去 30 天）
HP
HP 2025-4-14
编辑： HP 2025-4-25
Hello,
I am loading data from an Microsoft SQL table into Matlab (using ODBC driver). The SQL table contains ~300 columns and ~millions of rows. About halve of these columns contain numerical values in single precision (datatype "real"). Hence I want to load them with single precision into Matlab. However, when default datatype for all numerical values determined by databaseImportOptions is "double". Changing all these variables to "single" takes a significant amount of time. Is there any way of speeding this up? Or is there any way to change the default datatypes determined with databaseImportOptions?
SQL_database_name = 'SQL_database';
tic
SQL_conn = database(SQL_database_name,'','');
query = 'SELECT TOP 10000 * FROM Table_1';
opts = databaseImportOptions(SQL_conn, query);
types = opts.VariableTypes;
names = opts.VariableNames;
i_type_double = strcmp(types, 'double'); % find all "double" types
fprintf('Chainging type of %.0f from "double" to "single"\n', sum(i_type_double)
opts = setoptions(opts, names(i_type_double), 'Type', repmat('single',sum(i_type_double),1)); % change options from "double" to "single"
fprintf('Finished setting options %.1f s\n', toc)
Data_loaded = fetch(SQL_conn,query, opts);
fprintf('Finished loading %.0f rows in %.1f s\n', height(Data_loaded), toc)
close(SQL_conn)
Output:
Changing type of 201 from "double" to "single"
Finished setting options 26.4 s
Finished loading 10000 rows in 27.5 s

请先登录，再回答此问题。

采纳的回答

Nithin
Nithin 2025-4-22
Hi @HP,
Currently, there is no documented way to change the default numeric type for all columns in "databaseImportOptions". SQL "real" datatypes as are always imported as "double" types when using “databaseImportOptions”. The "setoptions" function is not optimized for bulk operations; it updates each column one by one, which can be slow when dealing with hundreds of columns.
A more efficient workaround is to import your data as "double" and then convert the necessary columns to "single" after loading. MATLAB is highly optimized for array operations, so this post-processing step is typically much faster.
Data_loaded = fetch(SQL_conn, query, opts); % import as double
% Now convert relevant columns to single:
doubleVars = varfun(@isdouble, Data_loaded, 'OutputFormat', 'uniform');
Data_loaded(:, doubleVars) = varfun(@single, Data_loaded(:, doubleVars));
Here's the MathWorks documentation about the functions:

更多回答（0 个）

类别

在 Help Center 和 File Exchange 中查找有关 Database Toolbox 的更多信息

标签

产品


版本

R2024b

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!

Translated by