Hello, big thanks for your fast response. With the help of your input I have
fix up my problem. Just to give you a little background I am working at
Statistic Canada and we are developing a record linkage application (vb
layer talking to a C layer that in turns talk to the firebird api). In
record linkage application, we can end up with really big tables in millions
and millions or record (up to 200 to 300 millions).



So I have experimented a lot with the sweep in order to speed up the
process. The best set up for us is to set the sweep interval to 0 and make
to sweep after we have done big updates (every record of a table). We also
want to keep a reserve since it seems to speed up the process. I know that
by default firebird keeps a reserve. Last I week I added the no_reserve
option at the api level as follow just to make sure it was on:



*thd++ = isc_dpb_no_reserve;

ADD_SPB_NUMERIC(thd, 0);



*thd++ = isc_spb_prp_res;



*thd++ = isc_spb_prp_sweep_interval;

ADD_SPB_NUMERIC(thd, 0);



thdlen = thd - thd_buff;



But when I do that it set the sweep interval to 20 000 and does not take
into account the sweet interval that I set to 0. So, I created projects with
a sweep interval of 20 000 that resulted in speed diminution and erratic
behaviour. Since firebird give reserve by default I got rid of the
no_reserve setting in the c level and now everything is back to normal.



A note on my store procedure, we like to use cursor, that gives us the
opportunity to update a generator on the fly that we can read via VB in a
progress bar. (I know we could also do it via a straight update and
trigger.) I did test the straight update you have provided and it took the
same time to run.



Thanks A lot Antoine !



*Antoine Chevrette*

Project Leader | Charg� de projet

Generalized Systems | Syst�mes g�n�ralis�s

R.H. Coats Building | Immeuble R.-H.-Coats / Floor | �tage 14 A

Statistics Canada | 100 Tunney's Pasture Driveway, Ottawa ON K1A 0T6

Statistique Canada | 100, promenade Tunney's Pasture, Ottawa ON K1A 0T6

Antoine.Chevrette@...

Telephone | T�l�phone 613-951-9903

Facsimile | T�l�copieur 613-951-4087

Government of Canada | Gouvernement du Canada

On Tue, Dec 15, 2009 at 8:05 AM, emb_blaster <EMB_Blaster@...>wrote:

>
>
> Hi!
> I dont know very much about cursors, so, if I was in your place, I would
> try to nails the problem.
>
>
> > The thing is sometime it will take 40 seconds to go through a 1 >million
> record table and sometime 5 minutes to go through 20 000 >records, that does
> not make sense.
> > Any idea why the performance differ so much?
>
> this could be by many things, including missuse of index, even in a select
> that show only one row (o.�). Are this tables in two distincts databases? if
> yes, can you provide gstat -h of both? (preffer of a working in progress)
> Also what is the version of your FB server/Clients?
>
>
> >
> > DECLARE STATUS_UPDATE CURSOR FOR (
>
> Like I said, I don't understand many about cursors, but seems for me that
> you will do it throught all the table.
>
> >
> > SELECT (...)
> > FROM FNDY_THRESHOLDS
>
> this is a only one row table?
>
> >...
>
> > :T_ENSEMBLE;
> > END
> > CLOSE STATUS_UPDATE;
> > EXECUTE PROCEDURE COUNT_RULE;
> Hey!! theres another procedure here!! what it does? can you disable it and
> test again against the tables??
>
>
> > DUMMY = GEN_ID(GEN_FNDY_NL,1);
> why this all DUMMY? Does it do something in any place of the code?
>
> > END^
> > SET TERM ; ^
>
> After reading your SP, i don't figured out why you dont used a UPDATE or a
> FOR SELECT loop.
> I should change all your procedure by this update below, except by: I dont
> get why did you used CURSORS, why no a FOR SELECT?; I dont know why the
> dummy things; and what that procedure COUNT_RULE realy does?.
> Said that, this UPDATE below should do the trick.
>
> begin
>
> SELECT FNDY_THRESHOLDS.CUTOFF_THRESHOLD,
> FNDY_THRESHOLDS.LOWER_THRESHOLD,
> FNDY_THRESHOLDS.UPPER_THRESHOLD
> FROM FNDY_THRESHOLDS
> INTO :T_CUTOFF_THRESHOLD,
> :T_LOWER_THRESHOLD,
> :T_UPPER_THRESHOLD;
>
> UPDATE FNDY_LINK
> SET STATUS =(case when (ENSEMBLE = 'N') THEN 'N'
> when (TOTWGHT < T_LOWER_THRESHOLD) THEN 'R'
> when (T_LOWER_THRESHOLD <= TOTWGHT AND TOTWGHT < T_UPPER_THRESHOLD) THEN
> 'P'
> ELSE 'D'
> end),
> SOURCE='R';
>
> end^
>
> PS.: I haven't sure about what you are trying to get with this procedure,
> so maybe my code is not that usefull for you. And I don't tested the code
> above by now. Anyway, that kind of update could let you try it even out of
> the SP and see the plan that it use for any update. This may let you nails
> the problem.
>
> WTH
> regards,
>
>
>


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