| Subject | Re: [firebird-support] Re: Newbie: Query speed and DB size |
|---|---|
| Author | Helen Borrie |
| Post date | 2008-03-27T10:45:55Z |
At 09:09 PM 27/03/2008, kogerbnz wrote:
In fact, Firebird does not have "tables" in the physical sense. It has pages (chunks of disk) containing blocks of data that are mapped to a metadata definition that you think of as a table. A page might contain one record (row) or it might contain more. The more efficiently the data are stacked on the page, the better. That means you don't want pages choked up with "clutter" that isn't accessed very often...
For performance, you should stop worrying about how many rows are in tables and concentrate on clean relationships and making frequently accessed data as available as possible. Don't fall into the trap of believing that cramming dozens of pieces of data into one table is a way to "optimize your design". Very wide table structures for frequently accessed entities will choke up the page cache with data that nobody is interested in and reduce the effectiveness of the cache. That *is* an important issue for you to ensure good performance on a low-spec'ed host machine. Normalise, normalise, normalise.
./heLen
>I'm not expecting an exact answer, I'm just trying to get a littleThis is a non-sequitur. "Using fewer queries" does not optimize your design. Everything you do is a query so forget "fewer queries". And "messy" table design invariably makes performance worse, not better. Everything in relational database management is about efficiency, good abstraction and fast retrieval and it is about SETS, not tables.
>feeling with how the DB would perform.
>Which would guide me when deciding if I should try optimize my design
>to use fewer queries, but giving a bit messier table design.
In fact, Firebird does not have "tables" in the physical sense. It has pages (chunks of disk) containing blocks of data that are mapped to a metadata definition that you think of as a table. A page might contain one record (row) or it might contain more. The more efficiently the data are stacked on the page, the better. That means you don't want pages choked up with "clutter" that isn't accessed very often...
For performance, you should stop worrying about how many rows are in tables and concentrate on clean relationships and making frequently accessed data as available as possible. Don't fall into the trap of believing that cramming dozens of pieces of data into one table is a way to "optimize your design". Very wide table structures for frequently accessed entities will choke up the page cache with data that nobody is interested in and reduce the effectiveness of the cache. That *is* an important issue for you to ensure good performance on a low-spec'ed host machine. Normalise, normalise, normalise.
./heLen