How to make faster sql queries

012786534

2019 9 18

0 个回答

8 次查看（30 天）

0 个投票

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;