How to make faster sql queries

4 次查看（过去 30 天）

012786534 2019-9-18

0
链接

此问题的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/480972-how-to-make-faster-sql-queries

评论： Klemen Peter Kosovinc 2020-10-21

Hello,

I am struggling to make SQL queries faster in Matlab. For example, the SQL query below contains not quite 500 000 rows and takes 250 seconds to fetch in Matlab but takes only 15 seconds in R. This is problematic particularly for large queries that can include as much as 7 millions rows. So how does one make SQL queries faster ? I have tried to increase the batch size but it doesnt really make a difference.

% Pass user and password info for connection to database
conn = database('NFG', input{1,1}, input{2,1});  
tic;
% Query data
sqlquery =  ['SELECT mi.desc_miss, ', ...
                    'xbt.latd lat, ', ...
                    'xbt.lond lon, ', ...
                    'to_char(xbt.sytm, ''YYYY'') as year, ', ...
                    'to_char(xbt.sytm, ''MM'') as month, ', ...
                    'xbt.deph, ', ...
                    'xbt.te90 temp ', ...
              'FROM NFG.MISSION_EXP mi, NFG.JEU_DONNEES jd, NFG.DONNEES_XBT xbt ', ...
              'WHERE xbt.SEQ_JD = jd.seq_jd ', ...
                'AND mi.acr_miss = jd.acr_miss '];
xbt_data = fetch(conn, sqlquery, 100000);
toc;