At 11:09 PM 24/04/2009, you wrote:

>I am new both my employer and the database they use, Interbase. I know this is a Firebird forum, but I tried posting to the Borland newsgroup yesterday and than realizaed this morning that it is not very active, no posts in like two months! So I found you all. It is my understanding that Firebird is the open source of Interbase, so I hope I am not out of line asking this question:
>
>We are using Interbase 7, I have heard from co-workers that as the database grows in size (300M to 400M), performance really takes a hit. I am currently adding a whole new feature to the application, so I will be adding a bunch of new tables.
>
>I am wondering... Is it worth while to optimize tables to fit into pages?

No, the "optimization" that you propose would have no predictable effect on performance. IB/Fb tables are not stored as discrete structures like, say, a MySQL or Access table. One or more records are stored on a data page. While no data page ever contains records from different tables, the pages containing the data for table "x" could be anywhere on the disk.

If performance problems start happening as the database grows, it's a fairly clear signal that the applications are not handling transactions correctly. The performance problems develop as obsolete records get left behind after updates and deletes and the application code's behaviour prevents the engine from clearing out the garbage. It's one of the two most fundamental performance issues for a multi-versioning database.

The other is indexing.

Both of these are big issues that need to be well understood. You can use gstat to help you analyse what's happening. gstat -h returns statistics about how well (or badly) the apps are managing transactions. It also tells you important db header stuff that's important for performance: page size and the size of the page cache. gstat -i returns stats about the structures and storage of your indexes.

>My thought is that by rounding the desc up to 84, that will make the table max out at 128 bytes, and again always fit nicely into a page.

Though that's of minimal or no relevance to performance, one thing that does matter is the page size. AFAIR, the IB 7 engine creates databases with a 1Kb page size. This means a lot more i/o for searching than one of 4K or 8K, particularly if a significant proportion of the data pages contain garbage.

>Also, does placement of varchar's have a large impact on preformance?

None at all.

>In all my tables, there is a updatedby, updateon, insertedby, and insertedon field. Should these fixed length fields always come before variable length fields

Makes no difference. Anything that's physical about locating the values stored for any field in any particular record is about relative starting addresses. Likewise rows and pages. Forget all your preconceptions about the "structures" of "tables" - they are there just to make it easier for people to conceptualise.

>or will the engine do that automatically?

The engine stores field definitions in creation order. It has no reason of its own to "shuffle" them into some other order: it only has to know what the order is. A human can change the column order using ALTER TABLE ALTER <FIELD NAME> <NEW POSITION> which has advantages from a self-documentation point of view.

>Are there things I should be keeping in mind as I create new tables to help performance?

Certainly! follow the rules of good, *practical* relational database design, *amongst which* -- Avoid redundancy (normalize!). Use atomic keys. Create good indexes where they will be useful (which means understanding a lot about how the data will be used). Avoid indexing or creating foreign keys on fields that have low selectivity (few possible values for a high number of records). Don't create indexes that are the same as those created by PK, FK or UNIQUE constraints...etc., etc.

Don't have a ".gdb" extension for your database file name on a windows server. (*.ib is the convention for IB databases but it can be anything that is "safe" from special treatment by Microsoft's internal tools.) And if the database engine itself is IB 7.0, then *rapidly* get the IB 7.1 upgrade from your agent (should be free; though it's been a long, long time since IB 7.x, and agents are dealing with a different owner now).

Don't allow file backup or anti-virus tools target the directories where you will store databases. Do gbak backups regularly, as these also do housekeeping on the database. Note, though, that if you have performance degradation coming from bad transaction handling by applications. housekeeping won't be very effective if you run it while users are logged in.

Above all, *don't* perform any changes on the production database. Back up the current one (with gbak) and restore it with the -c[reate_database] switch under a different name, into a development space with plenty of room. Don't forget to assign temporary sort space for it.

./heLen