At 18:05 20.02.2003 +1100, you wrote:

> >We used Firebird workbench and found that if we data-pumped the whole
> lot out
> >and back in it ran much faster. What had been taking 9s was taking <2s.
> >
> >So, explain that! Especially if it helps us to tweak our DB.
>
>Oh, it helps. Your indexes got rebuilt in the process of emptying and
>refilling....It seems as if you need to pay close attention to the indexes
>your database inherited from its Paradoxical past, or you will be right
>back there quite soon. Go back to Svein's suggestions, and to my warning
>about duplicate indexes, and start pruning and grafting.

Add to that a couple of things that may greatly effect speed:

Transactions: Committing transactions take time, so when you handle a batch
of records, you may gain speed by having quite a few of them within the
same transaction (e.g. commit every 10000 insert/update/delete).

Datasets: Preparing a dataset does takes time, hence if possible prepare
the dataset in advance and use parameters rather than recreating the sql
for each insert/update/delete. I'm using IBO, and using Params is also a
bit quicker than ParamByName, but not to the magnitude 9/2 I don't think.

And then, this list is a good 'utility' to find what make your queries
slow, once you identify which queries are slow, how slow they are and give
us lots of information about them (including the sql, plan, index
definition and selectivity and the occational piece of code ;o)


Set

- I support Firebird, I am a FirebirdSQL Foundation member.
- Join today at http://www.firebirdsql.org/ff/foundation