Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

# Database : Fastest way to Calculate the Sum of ..

Posted on 1999-08-18
Medium Priority
190 Views
I have a paradox table with 10000 records and then i
apply a range.. now i have 3000 records. If i try to do
this

Var
I,
Sum : Integer;
Begin
Table.First; Sum := 0;
For I := 0 To Table.RecordCount -1 Do
Begin
Sum := Sum + Table.FieldByName('MyField').AsInteger;
Table.Next;
End;
End;

it takes long time to finish the summury and a lot of hard
disk activity! Is there any other faster way except SQL
(BDE SQL is too slow when a using paradox table with Greek
Code Page) to calculate the summary of a speciefic field ?

Thanx
0
Question by:k6__
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 4
• 3
• 2
• +3

LVL 15

Expert Comment

ID: 1395199
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

0

LVL 27

Expert Comment

ID: 1395200
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
0

LVL 1

Expert Comment

ID: 1395201
Hi k6,

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
0

LVL 3

Expert Comment

ID: 1395202
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.

0

LVL 1

Expert Comment

ID: 1395203
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
0

LVL 2

Expert Comment

ID: 1395204
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;

0

LVL 15

Expert Comment

ID: 1395205
k6, are you there?

Alex
0

LVL 2

Author Comment

ID: 1395206
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.
0

LVL 2

Author Comment

ID: 1395207
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!

0

LVL 15

Expert Comment

ID: 1395208
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
0

LVL 2

Expert Comment

ID: 1395209
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;

0

LVL 2

Author Comment

ID: 1395210

0

LVL 15

Accepted Solution

simonet earned 200 total points
ID: 1395211
Thank you.

Alex
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this tutorial I will show you how to use the Windows Speech API in Delphi. I will only cover basic functions such as text to speech and controlling the speed of the speech. SAPI Installation First you need to install the SAPI type library, thâ€¦
Introduction Raise your hands if you were as upset with FireMonkey as I was when I discovered that there was no TListview.  I use TListView in almost all of my applications I've written, and I was not going to compromise by resorting to TStringGridâ€¦
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for â€¦
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/weâ€¦
###### Suggested Courses
Course of the Month4 days, 11 hours left to enroll