Keeping Order of sql query in Matlab? How can I speed up my Code?

Hello everbody,

running the following code takes forever, as there is a huge amount of data in the database. Is it possible to speed up the process with all the loops and if statements?

The reason, why I have to do it like this is, that when I include the statements in the where-clause of the sql query, the data gets rearranged. I need the original order to split the data in upper side and bottom side of the airfoil. If there is a possibility to keep the order of the database in matlab with using the where clauses, it would be much better to solve it like this.

Thank you very much.

clc
clear
close

tic
Datenbank=sqlite('05_F15_lam_wAoAfine.sqlite','readonly');
sqlquery= 'SELECT DISTINCT MsesData.X_C, MsesData.Z_C, MsesData.CP, AeroSettings.Ma, AeroData.CL_res, AeroSettings.ReynoldsNumber, AeroSettings.AirfoilId, AeroSettings.Transition, MsesData.AeroDataId FROM MsesData INNER JOIN (AeroData INNER JOIN AeroSettings ON AeroSettings.AeroDataId=AeroData.AeroDataId) ON AeroData.AeroDataId=MsesData.AeroDataId WHERE MsesData.AeroDataId BETWEEN "0" AND "2000"';
% sqlquery= 'SELECT DISTINCT MsesData.X_C, MsesData.Z_C, MsesData.CP, AeroSettings.Ma, AeroData.CL_res, AeroSettings.ReynoldsNumber, AeroSettings.AirfoilId, AeroSettings.Transition, MsesData.AeroDataId FROM MsesData INNER JOIN (AeroData INNER JOIN AeroSettings ON AeroSettings.AeroDataId=AeroData.AeroDataId) ON AeroData.AeroDataId=MsesData.AeroDataId WHERE AeroSettings.ReynoldsNumber = "2500000" AND AeroSettings.Transition="0" AND AeroSettings.AirfoilId="0"';
OuU=fetch(Datenbank,sqlquery);
toc


% load('OuU');
xc=[OuU{:,1}]';
zc=[OuU{:,2}]';
cp=[OuU{:,3}]';
Ma=[OuU{:,4}]';
cl=[OuU{:,5}]';
Re=[OuU{:,6}]';
id=[OuU{:,7}]';
tl=[OuU{:,8}]';
Id=[OuU{:,9}]';
M=[xc,zc,cp,Ma,cl];
D=[];
E=[];
F=[];
G=[];
H=[];

tic
for i=1:1:size(M,1)
    if id(i,1)==0 && tl(i,1)==0 && Re(i,1)==2500000 && size(find(Id==Id(i,1)),1)==280
            D(size(D,1)+1,:)=[xc(i,1),zc(i,1),cp(i,1),Ma(i,1),cl(i,1)];
    end
end

%% This is how I tried to speed it up

% for i=1:1:size(M,1)
%     if Re(i,1)==2500000
%         D(size(D,1)+1,:)=[xc(i,1),zc(i,1),cp(i,1),Ma(i,1),cl(i,1)];
%     end
% end
% 
% for i=1:1:size(D,1)
%     if id(i,1)==0
%         E(size(E,1)+1,:)=[xc(i,1),zc(i,1),cp(i,1),Ma(i,1),cl(i,1)];
%     end
% end
% 
% for i=1:1:size(E,1)
%     if tl(i,1)==0
%         F(size(F,1)+1,:)=[xc(i,1),zc(i,1),cp(i,1),Ma(i,1),cl(i,1)];
%     end
% end
% 
% for i=1:1:size(F,1)
%     G=find(Id==Id(i,1));
%     if size(G,1)==280
%         H(size(H,1)+1,:)=[xc(i,1),zc(i,1),cp(i,1),Ma(i,1),cl(i,1)];
%     end
% end
    

Oberseite=[];
Unterseite=[];

c=size(D,1)/280;
d=1;
tic
for b=1:1:c
for a=1:1:280
    if a<=140
        Unterseite(size(Unterseite,1)+1,:)=D(d,:);
    else
        Oberseite(size(Oberseite,1)+1,:)=D(d,:);
    end
    d=d+1;
end
end
toc

figure
hold on
grid on
ylim([-0.5,0.5]);
plot(Oberseite(:,1),Oberseite(:,2),'o');
plot(Unterseite(:,1),Unterseite(:,2),'x');

[Guillaume]

First, add comment to your code explaining what it's meant to be doing, in particular what the loop is supposed to do. Don't leave it to the reader to try to figure out. Most likely the loop is not needed and certainly, the find appears to be a complete waste of time.

I would also strongly recommend against having variables that only differ by the casing of one letter. Having both Id and id is asking for troubles. At one point, you'll write one when you meant to write the other. I would recommend longer variable names, you're allowed to use as many letters as you want so use full words.

In any case, fetch returns a table by default. A table is by far easier to use than separate matrices so keep the table. There's absolutely no reason to waste time splitting the table.

"If there is a possibility to keep the order of the database in matlab with using the where clauses"

This part has nothing to do with matlab. The query is executed by sqlite and the order is determined by sqlite. Matlab receives the data in whichever order sqlite sends it in. Now, you could probably modify your query (using ORDER BY) to return the data in a specific order but again, this is purely a SQL problem not a matlab one.

[Gerrit]

Thanks for your reply. You are right. The names could be better, but I knew I will change them in the future.

First of all, fetch returns cell arrays here. I did some performance testing and seperate the matrices was much faster than using the cell arrays.

I will try to explain the loops, but it is really difficult for me.

The first loop goes through every line of the cell array and checks if the Reynoldsnumber is 250000, the airfoil id is 0, the transition position is zero and if there is 280 times the same AeroDataId. This is important as in the secound loop, the data gets split up into the upper side and the bottom side of an airfoil. The data now consists of alternately 140 data points for the bottom side and 140 for the upper side for every AeroDataId, that matches the settings in the if clause of the first loop. So the secound loop splits up the data and alternately puts 140 points in the upper side vector and 140 points in the bottom side vector.

I hope you understand my Problem. Maybe there is another way to code this, but I am quite new to Matlab and Sqlite.

Thanks.

Answer 1

[Guillaume]

I don't have the database toolbox, but according to the online documentation fetch returns a table by default. If it's not the case for you, I'd investigate why. In any case, it's trivial to convert the cell array into a table. I recommend you use a table. it will simplify things for you.

%I'd recommend using better variable names for most variables below. E.g 'TransitionLength' instead of 'tl'
%In particular having both 'Id' and 'id' is really asking for troubles
airfoil = cell2table(OuU, 'VariableNames', {'xc', 'zc', 'cp', 'ma', 'cl', 'Re', 'id', 'tl', 'Id'});

All the below works with matrices, but it's clearer with a table:

%keep only the rows that match your conditions. Ignore the id count for now:
filtered_airfoil = airfoil(airfoil.Re == 250000 & airfoil.id == 0 & airfoil.tl == 0, :);
%filter on Id count. Better name required to avoid confusion with id!
filtered_airfoil = groupfilter(filtered_airfoil, 'Id', @(Ids) numel(Ids) == 280)

As you can see, this is much simpler and clearer than your original code. No loop needed and very fast.

Splitting the table into two tables consisting of alternative runs of 140 rows is also easily done:

mask = repmat(repelem([true; false], 140), height(filtered_airfoil)/280, 1);
upper_airfoil = filtered_airfoil(mask);
lower_airfoil = filtered_airfoil(~mask);

Personally, I wouldn't split the table but just add another column to table:

filtered_airfoil.side = categorical(mask, [true, false], {'upper', 'lower'});

[Gerrit]

I think it's not a table as I am working with sqlite, not sql. Thank you very very much. I will test your code now.

[Guillaume]

I made a small typo

upper_airfoil = filtered_airfoil(mask, :);
lower_airfoil = filtered_airfoil(~mask, :);

Note that the only reward we get from helping you is the reputation points awarded when you accept or vote our answer, so it's not particulalry appreciated if you accept your own answer which is just a copy/paste of the answer you were given.

[Gerrit]

I an sorry, this was not intentional. I corrected it.

Answer 2

[Gerrit]

clc
clear
close

Datenbank=sqlite('05_F15_lam_wAoAfine.sqlite','readonly');
sqlquery= 'SELECT DISTINCT MsesData.X_C, MsesData.Z_C, MsesData.CP, AeroSettings.Ma, AeroData.CL_res, AeroSettings.ReynoldsNumber, AeroSettings.AirfoilId, AeroSettings.Transition, MsesData.AeroDataId FROM MsesData INNER JOIN (AeroData INNER JOIN AeroSettings ON AeroSettings.AeroDataId=AeroData.AeroDataId) ON AeroData.AeroDataId=MsesData.AeroDataId WHERE MsesData.AeroDataId BETWEEN "0" AND "100"';
% % % sqlquery= 'SELECT DISTINCT MsesData.X_C, MsesData.Z_C, MsesData.CP, AeroSettings.Ma, AeroData.CL_res, AeroSettings.ReynoldsNumber, AeroSettings.AirfoilId, AeroSettings.Transition, MsesData.AeroDataId FROM MsesData INNER JOIN (AeroData INNER JOIN AeroSettings ON AeroSettings.AeroDataId=AeroData.AeroDataId) ON AeroData.AeroDataId=MsesData.AeroDataId WHERE AeroSettings.ReynoldsNumber = "2500000" AND AeroSettings.Transition="0" AND AeroSettings.AirfoilId="0"';
OuU=fetch(Datenbank,sqlquery);

airfoil = cell2table(OuU, 'VariableNames', {'xc', 'zc', 'cp', 'ma', 'cl', 'Re', 'id', 'tl', 'Id'});

%keep only the rows that match your conditions. Ignore the id count for now:
filtered_airfoil = airfoil(airfoil.Re == 250000 & airfoil.id == 0 & airfoil.tl == 0, :);
%filter on Id count. Better name required to avoid confusion with id!
filtered_airfoil = groupfilter(filtered_airfoil, 'Id', @(Ids) numel(Ids) == 280);

mask = repmat(repelem([true; false], 140), height(filtered_airfoil)/280, 1);
upper_airfoil = filtered_airfoil(mask);
lower_airfoil = filtered_airfoil(~mask);

filtered_airfoil.side = categorical(mask, [true, false], {'upper', 'lower'});

I get the following error.

Error using ForumUnterscheidung (line 18)

Subscripting into a table using one subscript (as in t(i)) or three or more subscripts (as in t(i,j,k)) is not supported. Always specify a row subscript and a variable subscript, as

in t(rows,vars).

Answer 3

[Gerrit]

Nevermind, I found all the errors. Thank you very very much. I think you helped me a lot.