Keeping Order of sql query in Matlab? How can I speed up my Code?
1 次查看(过去 30 天)
显示 更早的评论
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');
2 个评论
Guillaume
2019-12-18
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.
采纳的回答
Guillaume
2019-12-18
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'});
3 个评论
Guillaume
2019-12-19
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.
更多回答(2 个)
另请参阅
类别
在 Help Center 和 File Exchange 中查找有关 Graph and Network Algorithms 的更多信息
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!