Hi,

inoffensive_2006 wrote:

>> 1) do you use parametrized update statement, or do you compile it
> each time?
>
> I'm not sure what a "parametrized update statement" is, but I
> believe the SQL is being compiled each time.
>
> I'm using dynamic SQL and each row involves defining a new
> IBPP statement with IBPP's StatementFactory().
>
> This is an example of the SQL that's run for each row.
>
> UPDATE CON
> SET
> CON_ALPHA_SORT_ORDER = '1130038230440428'
> WHERE
> CON_KEY = '2301';
>
> Each sort order will have a unique value.
>
> Does this SQL lend itself to parameterization?

Here you are a good spot to improve speed significantly. This is not
parametrized statement, which would look like:

UPDATE CON
SET
CON_ALPHA_SORT_ORDER = ?
WHERE
CON_KEY = ?;

You'll create just one statement instance with SQL in this form (I'm not
familiar with IBPP, so you should check the manual what convention it
uses to define parameters. I've used '?' in this example as it's
standard parameter marker for Firebird, but some libraries use their own
convention). Then you'll call Prepare on this statement once, and use it
repeatedly with different parameters values. It significantly improves
the execution speed when basically the same statement with just
different values is executed many times (in a loop for example).

>> 2) Do you commit each update or (whole) batch?
>
> The transaction is started. A SELECT is run for each
> row's key and user's full name columns, these are placed in
> an STL vector and sorted, and the gap between each row's
> sort order is calclated. All of the indexes for the table
> are altered to inactive, the updates of the sort order are
> run on each row. All of the indexes are altered back to
> active, and the transaction is committed.

Ok, that's good re. transaction handling. But you don't need to
deactivate *all* indices here, just the one(s) that involve actually
changed column(s).

best regards
Pavel Cisar
IBPhoenix