Hi!

How to increase from 100000 records per second to 200000 records per second??? I have absolutely no idea, I've never managed anywhere near this speed (and as a matter of fact, I haven't used 'execute block')!

But you may still want to check a slightly different logic:

execute block
returns (
"Account" integer,
"Debit" numeric(18,4),
"Credit" numeric(18,4),
"PriorBalance" numeric(18,4),
"Balance" numeric(18,4)
)
as
declare variable "aAcc" integer;
declare variable "ItmSite" integer;
declare variable "ItmDate" date;
declare variable "aDebit" numeric(18,4);
declare variable "aCredit" numeric(18,4);

begin

for
select "ItmSite", "ItmDate", "ItmAccount", "ItmLocalDebit", "ItmLocalCredit"
from "VouItems"
where "ItmAccount"=11
and ("ItmDate" between '05/01/2006' and '06/30/2006'
or "ItmDate" between '08/01/2006' and '08/31/2006')
into :"ItmSite", :"ItmDate", :"aAcc", :"aDebit", :"aCredit"
do
begin
--some calculations
end
for
select "ItmSite", "ItmDate", "ItmAccount", "ItmLocalDebit", "ItmLocalCredit"
from "VouItems"
where "ItmAccount"=11
and "ItmDate"<='04/30/2006'
into :"ItmSite", :"ItmDate", :"aAcc", :"aDebit", :"aCredit"
do
begin
--some calculations
end

end

just to see if it is quicker or slower. In your particular example, you would probably want to add '+0' to ItmDate in your WHERE clauses, but you would only want to do that in cases where the restriction on this field doesn't eliminate a large proportion of the records.

I don't know how this will work, and would appreciate if you reported your results back to this list.

HTH,
Set

redtneen wrote:

> hi
> i made this statement
>
> execute block
> returns (
> "Account" integer,
> "Debit" numeric(18,4),
> "Credit" numeric(18,4),
> "PriorBalance" numeric(18,4),
> "Balance" numeric(18,4)
> )
> as
> declare variable "aAcc" integer;
> declare variable "ItmSite" integer;
> declare variable "ItmDate" date;
> declare variable "aDebit" numeric(18,4);
> declare variable "aCredit" numeric(18,4);
>
> begin
>
> for
> select "ItmSite", "ItmDate", "ItmAccount", "ItmLocalDebit",
> "ItmLocalCredit"
> from "VouItems"
> where ("ItmAccount"=11)
>
> into :"ItmSite", :"ItmDate", :"aAcc", :"aDebit", :"aCredit"
> do
> begin
> if ( ("ItmDate" between '05/01/2006' and '06/30/2006') or
> ("ItmDate" between '08/01/2006' and '08/31/2006')) then
> begin
> --some calculations
> end
> if ( ("ItmDate"<='04/30/2006')) then
> begin
> --some calculations
> end
> end
>
> end
>
> i take about 8 seconds to execute about 800000 records
>
> when i removed just if statement the time decrease to about 4 seconds
>
> is there any way to speed up this code ????
>
> notice: this code is made in run time so i don't know how many dates
> i would comapre
>
> my regards to u

[Non-text portions of this message have been removed]