Lester Caine wrote:

> 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.
>

not what you are asking for, but couldn't you just used a generator to
populate that Transact_No Field ?

update Transactions set Transact_no = Gen_id(MyGen, 1);

No dups, the vale will not be reseted for each Ticket_ID, but AFAIU you
just need a unique conjunct.

> 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.
>

So, you have no indices on both databases...

> 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.
>

Could you provide the plan ?

> 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.
>

I had upgraded my costumers database from 1.5 to 2.0 (and one from 1.0
to 2.0) no problem so far.

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

I knew 2 things to take care when upgrading from 1.x to 2.0

1.) Queries that uses NOT IN would not use indices on FB 2.0 so it would
be slower, but always returns the correct results.
2.) Because of the cooperative GC on SS some queries could have to
handle the load to clean-up the garbage left by other transactions, so
in some cases a query would be slow than it would be if cooperative GC
was turned off.

See you !

--
Alexandre Benson Smith
Development
THOR Software e Comercial Ltda
Santo Andre - Sao Paulo - Brazil
www.thorsoftware.com.br