query performance

[lamjd]

Dear all,

I found that using SELECT SUM() is outperform FOR EACH / OPEN QUERY. Does anyone can tell me why ?

1. SQL statement : SELECT SUM( credit ) from customer where cust-no > 20000.
( there are about 33000 customers with cust-num > 20000 and it tooks 19s)

2. For each customer where cust-no > 20000 no-lock :
accumulate customer.credit ( total ).
end.
disp ( accum total customer.credit ).

This took 60s. If using open query, it is worst, took 140s.

 

[jongpau]

Hi,

I am not sure if this is a solution because I am not on a system where I can test it right now, but have you tried specifying the fields phrase when using the for each and open query (looks like you only need the one field in your query and when doing a for each without fields Progress retrieves all fields, including the ones you do not need)?

Just and idea worth trying...

 

[MHotovec]

A '>' is not the best use of an index, which would cause you some slow down. Using '=' will get you more speed. To that end you want to think about a different way to accomplish your goal, by using the indexes in the best fashion possible.
You might try the following:

def var i as int.
def var j as int.

find last customer no-lock.

(specify using your cust-no index if that's not the primary index in your table. Also I'm assuming the index is ascending not descending. If it's descending you'll want to find first rather than last)

j = customer.cust-no.

do i = 20001 to j:
find customer where customer.cust-no = i no-lock no-error.
if avail customer
then accumulate cusotmer.credit (total).
end.
disp (accum total customer.credit).

Naturally all of this assumes that there IS an index in cust-no!

I'm interested in knowing how this compares to the others. Do please let us know.

Mark

Dear all,

I found that using SELECT SUM() is outperform FOR EACH / OPEN QUERY. Does anyone can tell me why ?

1. SQL statement : SELECT SUM( credit ) from customer where cust-no > 20000.
( there are about 33000 customers with cust-num > 20000 and it tooks 19s)

2. For each customer where cust-no > 20000 no-lock :
accumulate customer.credit ( total ).
end.
disp ( accum total customer.credit ).

This took 60s. If using open query, it is worst, took 140s.

 

[jammerjim]

Is this a Progress database, or some other product you are developing in Progress for?