Database : Fastest way to Calculate the Sum of ..

Given the code above, SQL will be faster. You have to take into consideration that you must consider the time (+overhead) caused by applying the ranges + the time taken in the loop. Only when you consider these 2 factors you must decide whether SQL is faster or not. I honestly believe that, in your specific case, SQL will be faster.

Now, on your code, there are a few thing I'd change, alhtough they wouldn't increase performance drastically (only slightly). Here how I'd rewrite it:

Var
 Sum : Integer;
Begin
 Sum := 0;
 with Table do
 begin
    first;
    while not eof to
    begin
      inc(Sum, Table.FieldByName('MyField').AsInteger);
      next;
    end;
 end;
     
The WITH statement cause the code to be much more optimized than the code you had, thus giving a faster loop.

The WHILE loop is a lot faster than the FOR loop, since less calculations are involved.

Using INC() is faster than using the "+" operator when both operands are integer.

Note that these optimizations may not be noticeable, unless you run both codes on a slow machine (like 486), in which case the time difference *might* be noticeable.

yours,

Alex
Athena's Place: http://www.bhnet.com.br/~simonet

hi k6,

i agree with alex, sql will be faster for this aggregat function,
why not use an additional tquery with a statement like this

select sum(Myfield) as MySum from MyTable where <YourRange>

you can code it like this

query1.close;
query1.sql.clear;
query1.sql.add('select sum(Myfield) as MySum from MyTable where <YourRange>');
query1.open;
sum := query1.FieldByName('MySum').AsInteger;

meikl

Hi k6,

I think your right about SQL on paradox tables being too slow.
Simonet gave you the correct code, but I do believe it will run faster.
Not because of INC and with staments, the compiler is supposed to be very clever in optimizing.
I think inspecting Table.RecordCount is the bottle-neck. Each time RecordCount is inspected the BDE will fetch all records and count them. I assume RecordCount is inspected every loop of your repetition.

Bye,

Ramon

You can also improve performance moving FieldByNamet function out the loop in
Alex code.

Var
  Sum : Integer;
  MyField : TField;
Begin
  Sum := 0;
  with Table do
  begin
    MyField := Table.FieldByName('MyField');
    first;
    while not eof to
    begin
      inc(Sum, MyField.AsInteger);
      next;
    end;
  end;

Ramon:

For-loop make condition evaluation only once, instead of while-loop that make it every loop cycle. So using a eof property that only inspect boolean value in dataset object prefer in while loop.

Slavak,

Are the others right about faster code when you use INC and WITH, or is the compiler as smart as I was told it is.

By the way good comment on the FieldByName.

Ramon "Feeling not so WiseGuy" Huijbens

Very nice guys: I like the evolution Will this work?

Var
  Sum : Integer;
Begin
  Sum := 0;
  with Table,FieldByName('MyField') do
  begin
    first;
    while not eof to
    begin
      inc(Sum,AsInteger);
      next;
    end;
  end;

k6, are you there?

Alex

yes... let me test the above samples first! =)
I know that SQL is faster but BDE 5.0x (i didn't tested }
5.10) has problems if the Table language is other thanx ASCII (it takes 3x - 4x more times than BDE 4.51 to give
the result).

Starting to testing right now.

Using BDE 5.10 and a Paradox Table with 50000 Records,
I did some benchmarks and here are the results :

Simonet   Routine = 42 Seconds
Slavak    Routine = 39 Seconds
CalvinDay Routine = 41 Seconds

Kretzschmar SQL Routine = 13 Seconds!!!!!
(7 seconds if it's in the disk cache)

That means BDE 5.10 has fixed language problems and SQL is
faster so i'll use SQL!

Now according to the question who deservers to get the points ?

Thanx all of you!

Quoting someone in the Lounge area, "I don't like to fight for points... I like to fight because it's fun".

In my first comment, I clearly stated that SQL would be faster, even 'though you were reluctant to accept that.  Then I posted the basic code that was improved by everyone else afterwards. So, basically everyone got a share on a good answer. I'll suggest 3 things (you folks choose the best one):

1) Split the points between those you think helped you most.
2) Decide who helped you most. Let's say you decided experts A, B and C deserve to be rewarded for the contribution. So you let A answer this Q, then A posts a 50-pts dumb Q for B and B posts a 50-pts dumb Q for C. The grade A will give B and B will give C is the same grade you give A. Obviously this works better for C, who doesn't spend any of his points, but then life's a bummer anyway.
3) If you have enough points, you can post dumb questions for experts B and C and let expert A answer this one.

The suggestions are given. It's up to you now.

Alex

I think we were all just expanding on Simonets original comments. I just enjoyed the question. No points needed here. Thanks for posting the results.

I also like reducing code down using the WITH statement. However, it has gotten me into problems when multiple components share the same property name.

with query1, sql, FieldByName('MySum') do
  begin
  close;
  clear;
  add('select sum(Myfield) as MySum from MyTable where <YourRange>');
  open;
  sum:=AsInteger;
  end;

Simonet Answer this thing =)