Subject Re: [firebird-support] Firebird, ODBC, web access and various beginner questions
Author Thomas Løcke
Svein Erling Tysvaer wrote:
> Hi Thomas, welcome to the world of Firebird!
Thank you! Feeling very welcome already. :o)

> Firebird and Access are two very different databases, and some things
> that work OK with Access will destroy all performance if done the same
> way with Firebird, e.g. Access will not suffer too much if you go
> sequentially through a table, whereas Firebird thrive a lot better when
> queries select only a few rows and columns. Likewise, doing things
> across a local network or over the internet requires different approaches.
The two Access databases I would like to port to Firebird are extremely
simple. Lets call them A and B.

Database A consists of 4 columns (INT, INT, INT, varchar(50)). This DB
grows with approx 3-4000 rows each day. At a certain point, each time an
insert is performed, the oldest record is deleted. Basically it's
nothing more than a log. At the very maximum reads are done on this
database once or twice each month.

Database B is a bit more complex, consisting of two tables, each with a
fairly large amount of columns. Most of the columns are varchar(x). We
perform a good 1500-2000 reads on this database each hour. Writes are
done at maximum 5-10 times each day. A typical SQL no this DB would look
something like this: SELECT * FROM table WHERE ID=123. Again, nothing
complex is done here. All we do is extract all the data for a given row.
Normalization is at a minimum, so data is repeated all over the place.
It's all about fetching a row and handing all the data to the users

The main reason for moving these two databases from Access to Firebird
is convenience. I'd rather have all my data gathered in one place. It's
easier to manage that way, or at least that's how I feel about it. :o)

Again: I have no control over DB design. I just try to live with it.

> From your e-mail, I read that you are starting to get performance
> issues regarding the databases, and this is a good place to obtain
> suggestions regarding Firebird performance (second only to the Firebird
> conference that is due in Hamburg next month?). To get the best answers,
> you ought to tell more about your current system - for Firebird, that
> includes information about things like database statistics when things
> are slow, transactions, how many concurrent users, more exact
> information about what is slow and possibly some information about your
> queries. Sometimes, performance problems are easily solved by simply
> taking proper care of transactions, modifying queries or adding indexes,
> at other times, more drastic changes are required.
The current Firebird DB (was running an old version of Interbase up
until a few weeks ago) is completey out of my hand. I have NO idea
what's going on inside the application itself, and the people who've
build it are very reluctant to release the information needed to conduct
the tests you mention.

What I do know is that Firebird is currently sucking up 50-60% of the
servers resources. If we're unlucky, several hits happen at the exact
same time, and then we max out pretty fast. This is not in itself a
problem. The problem is that when this happens, other parts of the
software start to act weird. We have for example a system in place for
playing .vox files, and this system starts to stutter a lot when the CPU
is running at 100%. By moving the DB away from the application server, I
can instantly eliminate this problem.

So my real issue here isn't as such the performance of the Firebird
database, as much as it is how it affects the remaining system.

Am I making any sense at all? :o)

> Sorry for not answering your questions,
> Set
No problem. I understand my original email perhaps did not convey
properly how little control I have over this app. At this point in time,
it's all about damage control. :o)