Vectorize and/or preallocate my query

Hi together,

is there anybody who might help me to vectorize and/or preallocate my query?

I really don't know how..

Thank you so much for your help.

function myCallback (hTimer,~)    
try
    addpath('C:\Users\MoneyM8ker\Documents\IBMatlab\Data\a')
    bVW = readtable('Schotter_OptionsscheineL1.csv');
    symbolListB = bVW.localSymbol;
    bn = length(symbolListB);
    bVW(1:bn,:);
    
    conn = database('Test','Test','Test');
    tablename = 'Kurs_Optionsschein_Detail';
    
    tnow = {datestr(now, 'yyyy-mm-dd HH:MM:SS.FFF')};
    whereclausen = ['WHERE Import = ''L1'''];
    update(conn,{'Simulation_Zeitstempel'},{'Time'},tnow,whereclausen);
    
    yVW=0;
    i=1;
    for yVW = 1:bn
        optionsname = symbolListB(i);
        stro= optionsname;
        stro1 = char(stro);
        
        DataOption(i) = IBMatlab('action','query', 'localsymbol',stro1, 'QuotesNumber',-1);
        
        w=0;
        for w = 1:2
            try
                i;
                w;
                
                dataTimestampA      = {[datestr(DataOption(i).data.dataTimestamp(w),'yyyy-mm-dd HH:MM:SS.FFF')]};
                bidSizeTimestampA   = {[datestr(DataOption(i).data.bidSizeTimestamp(w),'yyyy-mm-dd HH:MM:SS.FFF')]};
                askSizeTimestampA   = {[datestr(DataOption(i).data.askSizeTimestamp(w),'yyyy-mm-dd HH:MM:SS.FFF')]};
                eval(['C = ' num2str(DataOption(i).data.bidPrice(w)) ';']);
                eval(['D = ' num2str(DataOption(i).data.askPrice(w)) ';']);
                data = table(dataTimestampA, bidSizeTimestampA, askSizeTimestampA,symbolListB(i),C,D, 'VariableNames',{'dataTimestamp', 'bidSizeTimestampA','askSizeTimestampA','Optionsschein','bidPrice','askPrice'});
                sqlwrite(conn,tablename,data) ;
            catch e
                
                continue;
                fprintf(1,'The identifier was:\n%s',e.identifier)
                fprintf(1,'There was an error! The message was:\n%s',e.message)
                return;
            end    
            
        end
        
        
        i=i+1;
    end;
catch e %e is an MException struct
    fprintf(1,'The identifier was:\n%s',e.identifier)
    fprintf(1,'There was an error! The message was:\n%s',e.message)
    % more error handling...
end    
close(conn);
end

Kind regards

Oliver

[Jan]

What is the purpose of vectorization and pre-allocation? Both do not en in itself. Do you want to accelerate the code?

This is a very bad idea:

addpath('C:\Users\MoneyM8ker\Documents\IBMatlab\Data\a')
bVW = readtable('Schotter_OptionsscheineL1.csv');

Adding a folder on top of the path can cause serious troubles, if you shadow a built-in function. If you do this to import a data file, just use a full path:

Folder = 'C:\Users\MoneyM8ker\Documents\IBMatlab\Data\a')
bVW = readtable(fullfile(Folder, 'Schotter_OptionsscheineL1.csv'));

Avoid EVAL:

eval(['C = ' num2str(DataOption(i).data.bidPrice(w)) ';']);
% Better:
C = DataOption(i).data.bidPrice(w));

Neither vectorization nor pre-allocation seems successful strategies for speeding up the function. Use the profiler to find the bottleneck of the code.

Avoid uselues code like this:

                i;
                w;

[dpb]

On top of @Jan's always pertinent advice, it appears the only place preallocation would come into play in the above would be for the DataOption array -- and it doesn't appear there's any reason/need for it to be an array at all; the code as constructed consumes each entry inside the inner loop for each pass of the outer loop, but the variable content is never referenced later so there's no apparent need to not just redefine the variable each pass.

[Oliver Kerzmann]

Thank you so much for your fast help.

I'll update my code and will send the results.

Have a nice weekend.

Kind regards

Oliver

[dpb]

function myCallback (hTimer)
  % the function would be more general if the following were provided
  % arguments to the function, not hardcoded that requires edit to code
  % itself to use different location/file...
  rootpath='C:\Users\MoneyM8ker\Documents\IBMatlab\Data\a';
  filename='Schotter_OptionsscheineL1.csv';
  try
    bVW = readtable(fullfile(rootpath,filename));
    symbolListB = bVW.localSymbol;
    % length() is a dangerous function -- it is max(size(x)) use explicit
    % size argument dimension desired...
    bn = length(symbolListB);
    % use variables to store the text strings -- again, only change data, not code
    conn = database('Test','Test','Test');
    tablename = 'Kurs_Optionsschein_Detail';
    % datestr is deprecated, use new datetime functions
    tnow = {datestr(now, 'yyyy-mm-dd HH:MM:SS.FFF')};
    whereclausen = ['WHERE Import = ''L1'''];
    update(conn,{'Simulation_Zeitstempel'},{'Time'},tnow,whereclausen);
    %yVW=0;     % redefined by loop index immediately -- wasted effort
    %i=1;       % unneeded; no need to save DataOption in array
    for yVW = 1:bn
      optionsname = char(symbolListB(i));
      %stro= optionsname;     % needless extra variables
      %stro1 = char(stro);
      DataOption=IBMatlab('action','query','localsymbol',optionsName, 'QuotesNumber',-1);
      %w=0;     % redefined by loop index immediately -- wasted effort
      for w = 1:2
        try
          dataTimestampA      = {[datestr(DataOption.data.dataTimestamp(w),'yyyy-mm-dd HH:MM:SS.FFF')]};
          bidSizeTimestampA   = {[datestr(DataOption.data.bidSizeTimestamp(w),'yyyy-mm-dd HH:MM:SS.FFF')]};
          askSizeTimestampA   = {[datestr(DataOption.data.askSizeTimestamp(w),'yyyy-mm-dd HH:MM:SS.FFF')]};
          data = table(dataTimestampA, bidSizeTimestampA, askSizeTimestampA,symbolListB(i), ...
                       DataOption.data.bidPrice(w),DataOption.data.askPrice(w), ...
                       'VariableNames',{'dataTimestamp', 'bidSizeTimestampA','askSizeTimestampA','Optionsschein','bidPrice','askPrice'});
          sqlwrite(conn,tablename,data) ;
      catch e
        continue;
        fprintf(1,'The identifier was:\n%s',e.identifier)
        fprintf(1,'There was an error! The message was:\n%s',e.message)
        return;
      end    
    end
    %i=i+1;
  end;
catch e %e is an MException struct
    fprintf(1,'The identifier was:\n%s',e.identifier)
    fprintf(1,'There was an error! The message was:\n%s',e.message)
    % more error handling...
end    
close(conn);
end

Answer 1

[Oliver Kerzmann]

Hi togther,

the code is running much faster now. Thank you very much for the fast help.

But there's an other thing.. Time by time it's getting slower and slower and the RAM runs full.

Restarting Matlab helps, but is there an easier or more compfortable way?

Thank you and kind regards

Oliver

[dpb]

I don't know just what you're doing; I don't have and never used the SQL connection with MATLAB so no experience there.

What I'd say is it would probably be better if you can construct your queries to return the wanted data in large chunks instead of what it appears is a record-by-record manner.

You could use the profiler to see just what it is that is the time hog...