Exactly how is the continuous numbers calculated? With a primary key, you would get an error if inserting duplicates, and hopefully a unique constraint would achieve the same thing. It is important not to apply the same method for calculating the continuous numbers as one would normally do with a desktop database like Paradox or Access. Since Firebird is designed for multiuser access, a number may appear to be available, but still be in use by a concurrent transaction. Hence, using MAX + 1 (or similar) is futile with Firebird, and not having a primary key makes it harder to discover that there is a problem.

This, of course, has nothing to do with file size, and may not be your problem at all. Though there should be lots of databases around that runs flawlessly despite being considerably bigger than yours (gigabyte is common), so I doubt the cause of your problem is mainly due to the database size.

Set

-----Original Message-----
From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] On Behalf Of olaf.kluge@...
Sent: 19. januar 2009 21:51
To: firebird-support@yahoogroups.com
Subject: Re: [firebird-support] database and structure problem

Hi,

It is a stored procedure. It goes through every single position (data record) of an order-table and compacts them if possible. Then inserts the stored procedure it into a table and a second stored procedure takes each position (after the first stored procedure has created all positions) (data records) and examines the needed material from the high-bay-storage. But if the data record is missing in the one table, the second stored procedure can not works completely. I have found the problem in the first stored procedure.

the uncompressed size of the database is 45 megabytes and the database is one month online. In this condition the first stored procedure often forgets the last position (data record). After I had compressed the database (18 megabytes), the stored procedure attends all positions (. There is no apparent reason why it omits the position, even not, if I debug the stored procedure.

each data record in both tables has a unique integer identification value. it is also not a great value in a smaller writing, it is always the ongoing nummer. Often it is written into the database, about 10 updates every second. The java application with access to the database does auto-commit. (not written by me) The database is also not createc by me, but it is now my problem :o(

For fundamental changes, it is too late, the production has begun at the customer side. Almost no table has an primary key, but that should not be the problem, it works fine the most time and in these two tables a continuous number identifies the records.

I have no idea, why it works fine and if the database has grown, it works not always properly.

Has someone an idea?

Thanks.

Hi,

Olaf Kluge írta:

> Hello,
>
> I have a database server, firebird 2.1 superserver.
>
> A procedure of the database does create job-positions in a separate table,
> based on the predetermined positions 1 through n. As long as the database is
> a small size, the procedure worked fine. Now that the database has become a
> bigger size, the procedure sometimes forgets the last position. After the
> database is compressed and restored, it works fine again.

What small and bigger size cover in megabytes? And please describe then
"the procedure sometimes forgets the last position" thing. Procedure
means stored procedure? Not an smallint/integer/int64 problem?
Store a big number in a smallint or something similar?

>
> The database structure was not created by me, but it is now my task
>
> I have had this problem in other projects, that the database doesn't works
> fine if the size has grown.
>
>
> Thank you for your help.

Gabor