Hi Todd,

> Thought I would share some experience we have gained recently.
>
> We have an application consisting of a C++ Builder front end and
> Firebird 2.5 as the backend. Running on Windows. The database includes
> a ton of stored procedures with some fairly intricate calculations.
>
> A typical installation might have 45,000 records in the main table, with
> another 180 tables that can have a few records to a couple of hundred
> thousand of records in each of them.
>
> We had one main calculation routine that goes through the 45,000 records
> (one by one) and runs a bunch of stored procedures that populate once
> particular table in the database. This process was taking approx. 30
> minutes for one client.
>
> We had an idea to change the calculation to divide the 45,000 records
> into smaller chunks and processing them at the same time in different
> threads. We are using 10 threads for this operation. This cut the
> processing time in half from 30 minutes to 15 minutes.
>
> Also, we had always run Firebird as Superserver. We decide to install
> Firebird as Superclassic in order to take advantage of the multiple
> processors that most of our clients have on their servers. The
> particular client above has 4 processors on his server.
>
> After changing the Firebird installation to Superclassic, the processing
> time went down to approx. 5 minutes.
>
> Needless to say, we are very happy with the results of this. Still
> doing some testing before getting this out to our client base.

Thanks for sharing. So that's around 7ms per record. SuperClassic and
Classic are known to scale much, much better in SMP environments.
Firebird 3 will be (again) a game changer in that context.

Although I might be wrong as I don't use the installer package, but
"unfortunately" SuperServer might be the default architecture being
installed if not chosen otherwise.

But be careful when switching from SS to CS/SC, because this is a
completely different playground from a configuration/tuning perspective,
e.g. cause of the page cache being local per connection in CS/SC etc. My
architecture comparison sheet might be useful.

http://www.firebirdsql.org/file/fb25_architecture_comparison.pdf


Good luck.

--
With regards,
Thomas Steinmaurer
http://www.upscene.com/

Professional Tools and Services for Firebird
FB TraceManager, IB LogManager, Database Health Check, Tuning etc.