Firebird database query extremely slow

Hi Guys,

I have a firebird database which has a size of roughly 1.6 gigs, and is most probably being written to constantly.

When i atttempt to query a table within this database, the query takes EXTREMELY long, (or even locks up the table).

I'm using the firebird ODBC connection 1.069, and have set the tranaction options to no wait.
I've also made sure that the firebird database is installed in classic mode.

Even if i do a simple select statement on the table, the results take forever.
Is there anyway to speed up a Firebird database that is constantly being written to?

Does the size of the database matter (being 1.6gigs? I shouldnt think so, but i have noticed that on the smaller databases this particular query flies, which suggests to me that Firebird is stored as a flat file?)

Any pointers in firebird database query speeds would be highly appreciated, thanks in advance.

1. Make sure it is the database and not the server or the network!

2. Make sure appropriate fields are indexed and statistics are up to date.

3. Make sure writes are committed promptly.

4. Use the Firebird client instead of ODBC.

Clive

To actually help tuning a query you should:

1. Post the query (...)
2. Post the table(s) structure
3. Post the explain plan of that query
4. Knowing Firebird version could be useful

A general tip is to update stats on your database, but before that we need to see the explain plan.

clivew:
1) The database is located on the box that I am executing the query on.
2) When i attempt to upate the stats on the indexes of the troubled table, it seem like it freezes, or is just taking extremely long.
3) Upon checking for limbo transactions, it comes back with nothing.
4) I'll look into using the firebird client, but i feel that the issue lies more with the table itself.

pabloj:
1) A simple SELECT id from STATUS appears never to finish.
2) The table struct:

CREATE TABLE status (
typ NUMERIC(1,0) NOT NULL,
value_i NUMERIC(18,0),
value_s VARCHAR(32) CHARACTER SET UNICODE_FSS COLLATE UNICODE_FSS,
tsec NUMERIC(14,0) NOT NULL,
trendidx NUMERIC(10,0) NOT NULL,
oidid NUMERIC(3,0) NOT NULL,
cdid NUMERIC(18,0) NOT NULL,
id NUMERIC(18,0) NOT NULL,
FOREIGN KEY (cdid,id) REFERENCES domainhost (cdid,id) ON DELETE SET NULL ON UPDATE CASCADE,
FOREIGN KEY (oidid) REFERENCES oid (oidid) ON DELETE SET NULL ON UPDATE CASCADE,
PRIMARY KEY (cdid,id,trendidx,oidid)
);

CREATE DESC INDEX statusd_typ ON status (typ);
CREATE ASC INDEX statusa_typ ON status (typ);
CREATE DESC INDEX statusd_value_i ON status (value_i);
CREATE ASC INDEX statusa_value_i ON status (value_i);
CREATE DESC INDEX statusd_value_s ON status (value_s);
CREATE ASC INDEX statusa_value_s ON status (value_s);
CREATE DESC INDEX statusd_tsec ON status (tsec);
CREATE ASC INDEX statusa_tsec ON status (tsec);

4) The firebird version is 1.5.3 Win32 build.

When running an iSQL session, and attempting a select * from STATUS, (using NO WAIT, READ COMMITTED) the query runs for a long time, then stops, at a different record each time. Alsmost as if it dead locks (which is wierd as i'm using no wait).

Any further suggestions?

Thanks for your assistance thus far

Your queries can't use indexes, you didn't post the explain plan, do you know how many rows are in the status table?

When retrieving the plan for the a SELECT ID FROM STATUS statment, i just get:

PLAN (STATUS NATURAL)

Could you explain further why my queries can't use indexes?
As the amount of rows, were probably looking at over 100 000 rows..

Because you are simply returning all rows, no need to use an index to locate them, and 100.000 rows in output explain why it doesn't work properly.
BTW why are you extracting so many rows? Did you try the latest ODBC driver?

I also thought that that was the issue, unfortunatley it also chokes up when i use a SELECT * FROM STATUS WHERE ID=xxxx..
I have tried the latest ODBC driver and unfortunately the select statement is bitterly slow. When doing the select statement in iSQL i noticed that on some records the query pauses (obviously due to some form of lock) and pauses for a while at that too (between 5 seconds to 2 mins)
Is there any way i can avoid those locks? I have tried NO WAIT,

And what ist he execution plan for that query?

But to me this sounds as if either your .fdb file is corrupt or you are having a problem with your harddisk.
Did you run a chkdsk on that disk?

You should definitely post the explain plan for SELECT * FROM STATUS WHERE ID=xxxx.
I also think that a backup and restore (and fresh stats) could help, can you try that?

I get the same

PLAN STATUS(NATURAL)
(Am i retrieving the plan status correcty? using iSQL, setting PLANONLY ON?)

I could attempt a backup and restore, but what puzzles me the most is that this database was reset just a week ago (and has grown now to 1.8gigs, which would then also suggest to me that the stats need updating).
It is a very active database, perhaps getting exclusive access to it, pulling out the data, then putting the database back online might help?

It's not using the index, check your HD, fragmentation (if appliable) and if your transactions commit, stats and so on.
Can you do a test on a newer version?

I could attempt a test on the newer version, unfortunatley were not the ones who mainly use the database, were extracting information from an F-Secure database, so i'm not too sure which other process require this specific version of firebird. I'll check the stats.

??

Right, each manager has a firebird database. Hosts connect to this database to report their status (alerts, connection status and what not).
We then extract this information from this database and compile reports from it..
Simple enough