Firebird database too slow

Hi All,

We log on to our Data Center over a Citrix Terminal Farm.
We have 5 dual Xeon Citrix Terminal servers and our Dual Xeon Firebird Server. Everything is ok when we open word docs ect... on the Citrix Servers, but when we use our application from EXSOFT that accesses the DataBase ( Firebird) the results take far to long. The network responce time is very good, so I think that is has to do with the Firebird Database !. We have over 200 Users logging on to it, and the database is 35GB in size.

Can anybody help me ?. Must we swap to Oracle or sybase for better performance ?.

Thanks in advance.

Mark

Some ideas for performance improvement
http://www.dotnetfirebird.org/blog/...-page-size.html
http://www.dotnetfirebird.org/blog/...ning-links.html
I see you have dual cpu system , in that case Firebird Classic is working better (it will make use of them)
Add more ram (is cheap if system is swap-ing)
Also try to backup and restore the database (it will flush unused disk pages)
One more tip : try to optimize the query-ies for database (work with vendor and they should help you)

Yes, follow Mariuz's suggestions, read this comparison to know more about the architecture and why Classic server is good for your multiprocessor machine.
Note that SuperServer on a multiprocessor machine has this problem: (partly valid for non-windows machines)

Hi Mariuz,

we have tried everything you said. The vendor keeps trying to tell us that the Servers are the problem, and that his software is OK :-( . I believe myself that it is the query-ies, because when you try to look in the address data for a customer it takes about 5 Mins before you get a answer and sometimes the software freezes !. Our problem is also that we cannot chance the vendor ,because he is the only one in Germany that has this software. I also believe that the database and his software is'nt good enougth for so many users !!.

What OS are you using? Can you switch to ClassicServer? What does (in terms of SQL) mean "look for customer address", do you have properly indexed the table?
I've done some Firebird benchmarking in the past and it's speed was good.

The OS on the Firebird Server is Red Hat 8. The Citrix Server have MS 2003 Server.
I mean "Select from .........." in SQL.
We have no way to change anything. It is the vendor that made our Software & the Database. Every time we complain that everything is slow, he says that the network is the problem. We have a 1Gbit Netz, so very fast !, and a Fibre Glass Backbone.

This means NOTHING in SQL, apart from that, ARE YOU USING CLASSIC OR SUPERSERVER? Are you aware that with proper backup policies in place you can load the entire database in RAM?

We have Firebird-Classic-Server Version 1.5.0.4290 .
How does it affect the performance when we load the database into RAM ? and how do we make proper backup policies, because we cannot aford to loose any data at all.
Do you agree with me that our Hardware and Network is not the problem ?.

1. Can you check what the bottleneck is? Disk, CPU, memory ...
2. Can you check the actual sql statements issued and if proper indexing is in place?
3. Can you do a parallel test with current production Firebird (1.5.2)?
4. Loading the entire db in ram (i.e. copying file to ramdisk) should avoid disk usage ...
5. Google for GBAK and start backing up data

Can not speak to the hardware; but if word docs are not
a problem it is unlikely that the network is the problem
IF the SQL queries
are properly designed to retrieve only the required data.
How many of the connected users are writing as oppose
to reading the data?
What happens to performance if you run the application when
no-one else is connected?
If the data is growing in size, how does performance degrade:
1. Little change?
2. Linear to the data growth?
3. Exponential to the data?
The answers to these questions should help you focus on
the most likely suspects.
Clive.

Hi Clive,
I would say that it is about 50-50.
I tried that already, and when nobody was online I started the application that we use, and the Performance was still terrible.
Performance degrade = Linear to data growth.

Rgds

Mark

Still don't have the important infos ...

You need to folow up on the questions from "pabloj". Especially the SQL statements and indexing.

If performance is terrible with only one user
the SQL definately merits review.

If performance degrades in a linear fashion with data then the
indexing of columns in WHERE and JOIN clauses need review.

Clive.

Also, don't forget to refresh database statistics, see this interesting article on dotNetFirebird.org