which is more efficient, for loop or just regular adding

Question

I am developing a c app which reads data from a mysql table,process it and then inserts it in to 100 different tables in a oracle database. The thing is there is a huge concern for the performance of the app. In the mysql table I mentioned ,there are 4,800,000 and there are 92 columns in it. 90 of theme are just integers. So in my app for each entry of 4,800,000 , I'm running a for loop as given below.

    for (col = 1; col < 92; col++) {
        if (row[col] != NULL) {
            sum += strtol(row[col], NULL, 10);
        } else
            sum = sum + 0;
        if (col == 14) {
            p1_2weeks = sum;
        } else if (col == 31) {
            p2_1month = sum;
        } else if (col == 90) {
            p3_2month = sum;
        }
    }

So since this runs that much of iterations I believe there might be a possibility that I would be able to reduce the time taken by using regular addition to do this like.

 p1_2weeks = strtol(row[1], NULL, 10) +
             strtol(row[2], NULL, 10) +
             ... +
             strtol(row[14], NULL, 10);
 p2_1months = p1_2weeks +
             strtol(row[15], NULL, 10) + 
             ... +
             strtol(row[31], NULL, 10);
 p3_3months = p2_1months +
             strtol(row[32], NULL, 10) +
             ... +
             strtol(row[91], NULL, 10);

So can someone suggest which one is better or some other way of doing this right.

Answer 1

A for loop or manual unrolling with explicit additions is not going to make a significant difference. Most of the time will be spent reading from MySQL and converting fields to strings, and inserting the result into Oracle. If the program spends 99% of the time in the database code, which would not surprise me, optimizing this loop would be a moot point.

You might want to look for an different method for extracting values from MySQL that does not convert integers to strings.

Profiling will tell you where the bottleneck is for a given combination of compiler / options / cpu / memory / dataset, but it might be tricky to tell time spent in your program and time spent waiting for the Oracle process or the MySQL connection. Elapsed time is the final judge.

Note that your proposed alternative is not semantically equivalent: in the sequence of explicit additions, you do not test for NULL fields. If the columns cannot be NULL, you can remove the if (row[col] != NULL) test, if they can be NULL, you must modify the addition to add more tests, which will make it very clumsy. In addition, as seleciii44 suggests, the proposed alternative generates much larger code, which has an impact as well.

Node also that the loop is very straightforward, whereas the sequence of additions is error prone: it is easy to make cut and paste errors and miss an index or duplicate one. If you go for the additions, use a code layout that makes the indices stand out clearly. Look at how I reformated your code.

Indeed you have typos in the loop (p2_1month/p2_1months, p3_2month/p3_3months) and the addition does not compute p3_3months the same way: the loop sums 90 days, where the addition sums 91 days. But of course the number of days in a quarter varies, from 89 to 92 days.

Note also that sum = sum + 0; is a no op that should be removed, but the compiler most likely will not generate code for it.

If you are concerned with the extra tests for 14, 31 and 90 days performed for each iteration, you can use this:

    long temp[92];
    for (col = 1; col < 92; col++) {
        if (row[col] != NULL) {
            sum += strtol(row[col], NULL, 10);
        }
        temp[col] = sum;
    }
    p1_2weeks = temp[14];
    p2_1month = temp[31];
    p3_3months = temp[90];

It may be more efficient than the version with tests, but only careful benchmarking will tell you, because modern processors' branch prediction will minimize the cost if these tests while storing 91 values into a local array is a small extra cost.

By the way, are you sure about col < 92? Your loop treats 91 columns, but you write that only 90 of the 92 columns are integers.

Again, I would first look for a method for extracting values from MySQL as integers.