I'm pushing forward moving everything to use FB2, but I'm finding a number of
issues that are causing concern since they are showing FB2 in a bad light :(

The current customer databases are 30 to 40Mb and growing, so I needed to tidy
an index on one of the detail tables. This was being provided by a timestamp
to order the few records in detail, the activity on an enquiry which in 90% of
cases is never more than 2 or 3 records. The speed-up is to add a numeric
order field so I can look at the 2 or 3 record directly. I've got a stored
procedure which runs taking about an hour on both FB1.5 and FB2 to populate
the field. There is a niggle which - while it would be nice to fix is probably
not worth the effort. A few records in perhaps 100k get 0,1,1,3 or 0,0 rather
than 0,1,2,3. I'm manually fixing these at present, and once the database is
updated it will not go wrong again, but the speed problem I'm finding relates
to a simple query to check for the problem.

SELECT TICKET_ID, TRANSACT_NO, COUNT(TRANSACT_NO) FROM TRANSACTIONS
GROUP BY 1,2
HAVING COUNT(TRANSACT_NO) > 1

Both TICKET_ID and TRANSACT_NO are numbers, but I can't put the (TICKET_ID,
TRANSACT_NO) primary key in place until I have removed the duplicates.

On both FB1.5 and FB2 the database is restored from the site copy, the extra
field added, the stored procedure loaded and run. Then the check query is run.
On FB1.5 the answer is back in seconds, on FB2 it's taking minutes! I've
checked by running everything on the same machine so hardware and OS don't
come into play.

Can anybody explain why the vast difference. I know once the index has been
applied there will not be such a problem, but the reports that access this
data are now being asked to handle a year at a time, and early indications are
that THESE are somewhat slower on FB2 that FB1.5 so I am worried that a switch
will only cause problems.

Is there anything I should be looking for in the existing code while I'm
updated the source?

--
Lester Caine - G8HFL
-----------------------------
Contact - http://home.lsces.co.uk/lsces/wiki/?page=contact
L.S.Caine Electronic Services - http://home.lsces.co.uk
MEDW - http://home.lsces.co.uk/ModelEngineersDigitalWorkshop/
Firebird Foundation Inc. - http://www.firebirdsql.org/index.php