Ivan,

Thanks !

I am also playing with mass import SPs.
Your mail simply describes all the oddities I am experiencing lately. Except
that I haven't been able to establish cause to effect chains as clearly as
you did. With a 10 Gb DB, each single maintenance op takes minutes if not
hours. This makes it hard to clearly see the whole picture.

Your mail gives me some tracks to explore but I'm getting crazy as can't
seem to predict things. Slowdowns seem to happen when least expected. I just
rerun my test case trying the reproduce the pb, but this time, everything
went well...

Since then, did you better your understanding of the pb ? Did you find
workarounds to at least avoid it ?

Thanks

--
Jerome

> From: Ivan Prenosil
> To: firebird-support@yahoogroups.com
> Sent: Tuesday, May 11, 2004 2:04 PM
> Subject: Re: [firebird-support] UPs and DOWNs after bulk inserts.
>
>
> > Does anyone have a clue of what could cause this
> slowdown_and_back_to_normal
> > thing ?
>
> No clue, but your case seems similar to problems I encountered several
> months ago
> and reported on fb-devel list (and have not time to create reproducible

test

> case
> since then yet). Here is copy:
>
>
> ========================================
> From: "Ivan Prenosil" <Ivan.Prenosil@...>
> To: <firebird-devel@...>
> Subject: Strange speed problems.
> Date: Tue, 11 Nov 2003 19:12:13 +0100
>
> Hi all,
> When trying to optimize import speed into my database
> I encountered some strange results. I would appreciate
> if somebody could explain what is going on. (I know this is more
> support like question, but I believe firebird-devel is the only place
> where I could get answer on this one.)
>
> Short summary:
> Firebird-1.5RC6, WinXP (no system restore "feature" etc.),
> Single table with approx. 50000 rows, no indexes except primary key.
>
> I need to update the database using file with approx. 50000 rows,
> where only 60 rows are updated all other are identical with data already
> stored in database. I wanted to compare speed of two updating SP
> (called by application for each row of external data) that look like this:
>
> A: INSERT (PK, ...) VALUES ...
> WHEN unique_key_violation DO
> UPDATE ... WHERE PK=:PK
> (i.e. update all 50000 rows)
>
> B: INSERT (PK, ...) VALUES ...
> WHEN unique_key_violation DO
> IF new_values_and_existing_values_are_not_identical THEN
> UPDATE ... WHERE PK=:PK
> (i.e. update only 60 rows)
>
> After two days of playing with Altering procedure from A to B and from B

to

> A,
> sweeping, restargin server etc. I am still confused by results and have

two

> questions:
>
>
> 1) Why the sweep after metadata change is so sloooow ?
>
> Here are some times to compare
> - UPDATE tab SET X=X; ... 4 sec.
> - sweep after that update ... less than 2 sec.
> - sweep after ALTER PROCEDURE ... 14 sec !!!
>
> Even with fresh new database, containing only single SP, the times are:
> - sweep ... unmeasurable
> - sweep after ALTER PROCEDURE ... 4 sec !
>
>
>
> 2) Why the procedure B (unlike A) is much slower on swept database (or
> garbage collected) ?
>
> Here are times of some operations
> (each row represents separate operation using extra connection, without
> overlapping)
>
> - update A (i.e. calling procedure A 50000x times) ... 45 sec
> - update A ... 45 sec
> - update A ... 45
> - sweep ... 2
> - update A ... 45 << o.k. still the same time after sweep
> - update A ... 45
> - ALTER PROCEDURE A->B
> - update B ... 40 << o.k. procedure B seems to be faster as expected
> - update B ... 40
> - sweep ... 16 (we already know that first sweep after Alter Procedure

is

> slow)
> - update B ... 90 <<<<< first import using B is always that slow after

sweep

> ! Why ??
> - update B ... 80
> - update B ... 70
> - update B ... 40 <<<<< now it returned to normal
> - update B ... 40
> - sweep ... less that 1 sec
> - update B ... 90 <<<<< slow, slow, slow
> - update B ... 80
> - update B ... 70
> - update B ... 40
>
> =====
> Now try the same, but restart Firebird server after altering SP:
>
> - update A ... 45
> - update A ... 45
> - ALTER PROC A->B
> - Restart
> - update B ... 42
> - update B ... 95 <<<<< the second import using B after Alter Procedure

and

> Restart is always that
> slow ! Why ?
> - update B ... 65
> - update B ... 40
>
> Since Firebird disconnect completely from the database after each

operation,

> I did not expect that restarting server could have such impact.
>
> Ivan
>