SQL: setoptions for databaseImportOptions very slow

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

Answer 1

[Nithin]

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:

• fetch: https://www.mathworks.com/help/database/ug/database.odbc.connection.fetch.html
• varfun: https://www.mathworks.com/help/matlab/ref/table.varfun.html

[HP]

Hi @Nithin,

thanks for the clarification.