By Holger Klemt
Do you ever think about possibilities to improve your database performance? Sure, a database system such as Firebird or InterBase® is able to speed up typical operations internally but, in a lot of cases, there are very easy but powerful methods to improve performance.
Here is a first example:
When the first user connects to a database, the database cache is empty and all database and index pages must be read from the hard disk. The Superserver architecture will use the cache for all connected users for this database, but when the users are disconnected again, the cache is cleared and everything starts over again.
This is not only important for typical Delphi/C++/.net/Java client applications, but also for web server applications using PHP or ASP.
How to improve the database open performance?
CREATE PROCEDURE FILLCACHE
AS
declare variable SQL VARCHAR(200);
declare variable cnt integer;
BEGIN
/* Fillcache Procedure (c) IBExpert Team*/
FOR
select rdb$relation_name sql from rdb$relations
INTO :SQL
DO
BEGIN
sql='select count(*) from '||sql;
execute statement sql into cnt;
END
END
This procedure is compatible with Firebird >=1.5, but it can be also altered to be implemented with InterBase® or older Firebird versions. Since it counts all data in all tables, all data pages are copied from the hard disk to the cache. When there is enough free memory, all cache pages remain in the memory until the last connection disconnects.
This script should be executed, for example, the first time every morning at 7:30 am. Write a batch file and create a job in the Windows Task Manager or Linux cron:
connect 'localhost:C:\db1.fdb' user 'sysdba' password 'masterkey'; execute procedure fillcache; commit; shell sleep 3600000 execute procedure fillcache; commit; shell sleep 3600000 execute procedure fillcache; commit; shell sleep 3600000 ...... exit;
This script connects to the database, executes the fillcache procedure, commits the transaction and sleeps for one hour before it runs again. The operation is repeated as often as desired and the connections remain active until the command exit is executed. For example when executed hourly 12 times, it fills the cache for twelve hours and stops after that time. On the next day, the script starts again automatically.
Additional advantages: this script also starts the garbage collector when it finds outdated records in the database, but this will only happen as long as there is no older active transaction (OAT) blocking the garbage collector.
Résumé
Feel free to implement these operations in your database server to improve the performance. We have a number of customers who have used this and reported very satisfactory improvements.