Sped up for loop

Dear all,

Please show me how to speed up the for loops below. It took me a day to run for M=50.

I would appreciate your help! Best, Dat

clear all;
clc;
M = 20;
A_pro_cct = zeros(16,M);
tm = zeros(16,16);
s = zeros(17,4);

for N = 1:M

for ii = 1:N
filename = 'C:\Users\Dat\Documents\CT_TMs_counties.xlsx';
sheet = ii;
tm = xlsread(filename,sheet,'b2:q17');
s = xlsread(filename,sheet,'h21:k37');

for i = 1:16
  pro_cct = s(i,1)*tm(i,9)+tm(i,3)*s(i,3) + tm(i,1)*s(i,1);
  A_pro_cct (i,N) = pro_cct;

end
end  
end
AA = A_pro_cct';

Answer 1

[Steven Lord]

You're reading the Microsoft Excel spreadsheet file M*(M+1) times in your double nested FOR loop. Instead pull those lines of code outside the double FOR loops, read the file into a variable once (or once per sheet) and refer to the appropriate section of that variable inside the loop.

[Dat Tran]

Thank you for promptly response! I have tried to do as below but results was only saved for the last input.

clear all; clc;

N = 2; A_pro_cct = zeros(16,N);

for ii = 1:N filename = 'C:\Users\Dat\Documents\CT_TMs_counties.xlsx'; sheet = ii; tm{ii} = xlsread(filename,sheet,'b2:q17'); s{ii} = xlsread(filename,sheet,'h21:k37'); end

for ii = 1:N for i = 1:16 pro_cct = s{ii}(i,1)*tm{ii}(i,9)+tm{ii}(i,3)*s{ii}(i,3) + tm{ii}(i,1)*s{ii}(i,1); A_pro_cct (i,N) = pro_cct;

end end AA = A_pro_cct';

[Dat Tran]

I found how to save the results but the loop does take quite a bit of time!

clear all;
clc;

N = 10;
A_pro_cct = zeros(16,N);

for ii = 1:N
filename = 'C:\Users\Dat\Documents\CT_TMs_counties.xlsx';
sheet = ii;
tm{ii} = xlsread(filename,sheet,'b2:q17');
s{ii} = xlsread(filename,sheet,'h21:k37');
end

for M = 1:N
for ii = 1:M
for i = 1:16
  pro_cct = s{ii}(i,1)*tm{ii}(i,9)+tm{ii}(i,3)*s{ii}(i,3) + tm{ii}(i,1)*s{ii}(i,1);
  A_pro_cct (i,M) = pro_cct;

end
end
end
AA = A_pro_cct';

[Jan]

Please format your code using the "{} Code" button, such that it is readable. Thanks.

Omit te time consuming clear all, because it removes all loaded functions from the memory. Reading them from the slow hard disk wastes time.

[Dat Tran]

Thank a lot!!!

[Image Analyst]

As I understand it, if you have R2015b or later, the Excel reading will be a lot faster because it does not shutdown the Excel server after xlsread(), and so does not have to launch it again either.

And cell arrays take a huge amount of memory and a lot of overhead. You don't even need to use cell arrays at all. Just use regular numerical arrays.