SQL-query extremly slow

19 Ansichten (letzte 30 Tage)
Fredrik
Fredrik am 3 Sep. 2021
Beantwortet: Fredrik am 24 Nov. 2021
I have a fairly simple SQL-query that i use to get data from an MS-SQL-database. If I use Microsoft SQL Server Management Studio, the it takes less than one second. The exact same query in Matlab takes over 100 seconds. I use the autogenerated code from Database Explorer app with some modifications.
Am I doing something wrong? Is there some way to improve performance?
%% Automate Importing Data by Generating Code Using the Database Explorer App
% This code reproduces the data obtained using the Database Explorer app by
% connecting to a database, executing a SQL query, and importing data into the
% MATLAB(R) workspace. To use this code, add the password for connecting to the
% database in the database command.
% Auto-generated by MATLAB Version 9.3 (R2017b) and Database Toolbox Version 8.0 on 09-Sep-2019 12:19:26
%% Set preferences
prefs = setdbprefs('DataReturnFormat');
setdbprefs('DataReturnFormat','table')
%% Make connection to database
conn = database('something','', '');
%% Query
sqlquery=['SELECT ...];
%% Execute query and fetch results
curs = exec(conn,sqlquery);
curs = fetch(curs);
MDdmTable = curs.Data;
close(curs)
%% Close connection to database
close(conn)
%% Restore preferences
setdbprefs('DataReturnFormat',prefs)
%% Clear variables
clear prefs conn curs

Antworten (1)

Fredrik
Fredrik am 24 Nov. 2021
I have found that using "ODBC Driver 17 for SQL Server" as driver instead of "SQL Server", when configuring the connection in Database Exploer, results in much faster queries. Still 5-10 times slower than using MS SQL Server Management Studio, but not 100 times as before.

Produkte


Version

R2019b

Community Treasure Hunt

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

Start Hunting!

Translated by