Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Performance of index creation

In trying to choose which indexing method to recommend, I tried to measeure the performance. However, the measurements confused me a lot. I ran this multiple times in different orders, but the measurements remain consistent. Here is how I measured the performance:

for N = [10000 15000 100000 150000]
    x =  round(rand(N,1)*5)-2;
    idx1 = x~=0;
    idx2 = abs(x)>0;

    tic
    for t = 1:5000
        idx1 = x~=0;
    end
    toc

    tic
    for t = 1:5000
        idx2 = abs(x)>0;
    end
    toc
end

And this is the result:

Elapsed time is 0.203504 seconds.
Elapsed time is 0.230439 seconds.

Elapsed time is 0.319840 seconds.
Elapsed time is 0.352562 seconds.

Elapsed time is 2.118108 seconds. % This is the strange part
Elapsed time is 0.434818 seconds.

Elapsed time is 0.508882 seconds.
Elapsed time is 0.550144 seconds.

I checked and for values around 100000 this also happens, even at 50000 the strange measurements occur.

So my question is: Does anyone else experience this for a certain range, and what causes this? (Is it a bug?)

like image 458
Dennis Jaheruddin Avatar asked Jul 23 '13 11:07

Dennis Jaheruddin


People also ask

How does index increase performance?

Indexing makes columns faster to query by creating pointers to where data is stored within a database. Imagine you want to find a piece of information that is within a large database. To get this information out of the database the computer will look through every row until it finds it.

How do you speed up index creation?

What are the ways to speed-up the creation of an index in Oracle? Answer: Creating an index involves a full scan against the target table and a sort, so whatever you can use to speed-up these operations will speed-up the performance of a "create index" statement.

Do indexes affect performance?

The number of indexes on a table is the most dominant factor for insert performance. The more indexes a table has, the slower the execution becomes. The insert statement is the only operation that cannot directly benefit from indexing because it has no where clause.


2 Answers

I think this is something to do with JIT (results below are using 2011b). Depending on system, version of Matlab, the size of variables, and exactly what is in the loop(s), it is not always faster to use JIT. This is related to the "warm-up" effect, where sometimes if you run an m-file more than once in a session it gets quicker after the first run, as the accelerator only has to compile some parts of the code once.

JIT on (feature accel on)

Elapsed time is 0.176765 seconds.
Elapsed time is 0.185301 seconds.

Elapsed time is 0.252631 seconds.
Elapsed time is 0.284415 seconds.

Elapsed time is 1.782446 seconds.
Elapsed time is 0.693508 seconds.

Elapsed time is 0.855005 seconds.
Elapsed time is 1.004955 seconds.

JIT off (feature accel off)

Elapsed time is 0.143924 seconds.
Elapsed time is 0.184360 seconds.

Elapsed time is 0.206405 seconds.
Elapsed time is 0.306424 seconds.

Elapsed time is 1.416654 seconds.
Elapsed time is 2.718846 seconds.

Elapsed time is 2.110420 seconds.
Elapsed time is 4.027782 seconds.

ETA, kinda interesting to see what happens if you use integers instead of doubles:

JIT on, same code but converted x using int8

Elapsed time is 0.202201 seconds.
Elapsed time is 0.192103 seconds.

Elapsed time is 0.294974 seconds.
Elapsed time is 0.296191 seconds.

Elapsed time is 2.001245 seconds.
Elapsed time is 2.038713 seconds.

Elapsed time is 0.870500 seconds.
Elapsed time is 0.898301 seconds.

JIT off, using int8

Elapsed time is 0.198611 seconds.
Elapsed time is 0.187589 seconds.

Elapsed time is 0.282775 seconds.
Elapsed time is 0.282938 seconds.

Elapsed time is 1.837561 seconds.
Elapsed time is 1.846766 seconds.

Elapsed time is 2.746034 seconds.
Elapsed time is 2.760067 seconds.
like image 150
nkjt Avatar answered Oct 23 '22 04:10

nkjt


This may due to some automatic optimization matlab uses for its Basic Linear Algebra Subroutine.

Just like yours, my configuration (OSX 10.8.4, R2012a with default settings) takes longer to compute idx1 = x~=0 for x (10e5 elements) than x (11e5 elements). See the left panel of the figure where the processing time (y-axis) is measured for different vector size (x-axis). You will see a lower proceesing time for N>103000. In this panel, I also displayed the number of cores that were active during the calculation. You will see that there is no drop for the one-core configuration. It means that matlab do not optimize the execution of ~= when 1 core is active (no parallelization possible). Matlab enables some optimization routines when two conditions are met: multiple cores and a vector of sufficient size.

The right panel displays the results when feature('accel','on'/off') is set to off (doc). Here, only one core is active (1-core and 4-core are identical) and therefore no optimization is possible.

Finally, the function I used for activating/deactivating the cores is maxNumCompThreads. According to Loren Shure, maxNumCompThreads controls both the JIT and BLAS. Since feature('JIT','on'/'off') did not play a role in the performance, BLAS is the last option remaining.

I will leave the final sentence to Loren: "The main message here is that you should not generally need to use this function [maxNumCompThreads] at all! Why? Because we'd like to make MATLAB do the best job possible for you."enter image description here

accel = {'on';'off'};
figure('Color','w');
N = 100000:1000:105000;

for ind_accel = 2:-1:1
    eval(['feature(''accel'',''' accel{ind_accel} ''')']);
    tElapsed = zeros(4,length(N));
    for ind_core = 1:4
        maxNumCompThreads(ind_core);
        n_core = maxNumCompThreads;
        for ii = 1:length(N)
            fprintf('core asked: %d(true:%d) - N:%d\n',ind_core,n_core, ii);
            x =  round(rand(N(ii),1)*5)-2;
            idx1 = x~=0;
            tStart = tic;
            for t = 1:5000
                idx1 = x~=0;
            end
            tElapsed(ind_core,ii) = toc(tStart);
        end
    end
    h2 = subplot(1,2,ind_accel);
    plot(N, tElapsed,'-o','MarkerSize',10);
    legend({('1':'4')'});
    xlabel('Vector size','FontSize',14);
    ylabel('Processing time','FontSize',14);
    set(gca,'FontSize',14,'YLim',[0.2 0.7]);
    title(['accel ' accel{ind_accel}]);
end
like image 40
marsei Avatar answered Oct 23 '22 05:10

marsei