Alexandre Benson Smith wrote:

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

And no use when processing the reports :(
TRANSACT_NO will be 0 for initial waiting time, 1 for initial serving time, 2
for referral waiting time and 3 for referral serving time ( and extra counts
if the ticket is referred back again.
Prior to this change the report generator was using an in line SELECT FIRST 1
SKIP to get the relevant record, but now I can use
SELECT WHERE TRANSACT_NO = x

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

Index on TICKET_ID, but until I can populate the TRANSACT_NO I can't index ;)

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

A simple Natural in both cases. FB1.5 literally does only take a few seconds
to scan all 100k+

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

Don't use IN

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

Now THAT is a possible. All the records were updated by populating TRANSACT_NO
so there will be 100k+ duplicate records following the scan by the stored
procedure. I do a backup and restore once everything has been tidied, so I
would not notice that, but if FB2 is doing an in line tidy, that could explain
the difference ? I did not just try running the query again so I'll try that.

SET - I don't want to delete anything, the detail sets have 0,1,1,3 so I need
to change the second 1 to a 2 to correct the niggle from the stored procedure.
Now why the stored procedure makes 6 mistakes in 100k+ records .....

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