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

Hi Lester!

Apart from the obvious question: "What about the PLANs", have you tried

DELETE FROM TRANSACTIONS T1
WHERE EXISTS(SELECT * FROM TRANSACTIONS T2
WHERE T1.TICKET_ID = T2.TICKET_ID
AND T1.TRANSACT_NO = T2.TRANSACT_NO
AND T1.RDB$DB_KEY < T2.RDB$DB_KEY)

to delete the duplicates?

I'm sorry that I'm not sufficiently up-to-date on Fb 2 to tell why it is slower (at least not unless there is some clue in the plans).

HTH,
Set


[Non-text portions of this message have been removed]