Title: Improve Interbase Client Server Performance
Question: How do you get the best out of your Interbase Client Server System?
Answer:
Interbase Performance Guidelines
This document attempts to set down several guidelines on how to get the best performance out of Interbase. Before getting down to the aspects that I would recommend when designing a Database to meet the needs of your clients, I would ask that you remember that usually the most expensive (in order) aspects of the project costs are:
1. Developers
2. Network
3. Server
So when prioritising your resources, keep the above in mind. Also a shortcut taken initially (such as not testing on a fully populated database), always takes at least three times as long to fix later on and costs at least three times as much.
DATABASE DEVELOPMENT
Normalize Your Database
No matter what database you are developing for you have to start with a good design. Ensure that you have normalised your database to at least third normal form. Ensure that your primary keys are independent of any business objects that you are storing, and for performance reasons use an integer (Integers are 32 bit and sorted faster by most sort algorithms compared to char and varchar fields), unless you cant do that for a specific reason (such as you need a unique identifier for replication, or you are using a relational database to store objects).
Primary Keys
If you define a compound primary key more than one index is created (one is created for each column that makes up the compound key). Since the query optimiser will use the multiple indexes to solve a query this can cause a bottle -neck for the optimiser as the multiple indices being used by the optimiser are the same as the fields in the query.
Foreign Keys
Foreign Keys are essential referential integrity constraints. The problem with foreign keys is that they will create an Index on the respective tables, to facilitate the constraint. Normally this is not a problem. However if you have a foreign key constraint on columns that tend not to have fairly unique values, then you have a very poor index. If the Query Optimiser gets hold of these Indexes then this will cause a performance bottleneck due to the poor index. In these cases removing the indexes can increase the performance of queries by a few hundred percent. So be careful where you define your foreign keys.
Use Indexes on joined columns
An index is a balanced tree data structures that provide an improvement in sort speeds for a database. Interbase Indexes are direction specific (Ascending or descending), so if you look back through a table you should define a descending index. Indexes work better with data that tends to have some uniqueness about it. The Interbase query optimiser will use Indexes to speed up queries. Indexes are actually detrimental to performance when created on a column that has few unique values. Indexes slow the insertion of data into a table, as the indexes have to be recalculated. If you are going to do a large number of inserts into a table you may want to consider temporarily de-activating the Indexes to minimise the performance impact (ALTER INDEX name INACTIVE, and then ALTER INDEX name ACTIVE), and reactivate the index after the inserts have been done. To determine the effectiveness of an index, run the following SQL statement Select RDB$INDEX_NAME, RDB$STATISTICS from RDB$INDICES. The RDB$STATISTICS value shows the effectiveness of the Index. The lower the value the better the index, with a value of one indicating a very poor index. As a general rule of thumb, you should only ever define a few indexes on a table (the query optimiser will use multiple indices to reference the same fields as needed by a query, so too many indices can degrade performance).
SQL
Certain SQL statements are slower than other SQL statements. In general avoid the use of functions like CONTAINING, LIKE, , COUNT and UPPER as these functions will not use Indexes during their operation making them slower than other commands. The book SQL Performance Tuning by Peter Gulutzan and Trudy Pelzer covers in very good details how to increase the effectiveness of your SQL statements without reference to specific proprietary functionality. I would recommend this book to anyone involved in database development.
Correlated Subqueries
A correlated Subquery is a subquery where the conditions of the subquery are different for each row in the parent query. Because of this the subquery needs to be executed a number of times for each row of the parent query. In some instances a join can replace a correlated subquery, and will result in a faster query execution time.
Outer Joins
Outer joins are a reality of Database programming. Left outer Joins have a tendency to be slow, and an index will only be used for the resolution of the first outer join in a query. Where possible reduce the need for left outer joins. This can be done by:
Design your tables so they are not needed
Use a Sub-Query (this can be faster than resolving left outer joins)
Use a Select Stored Procedure to increase the speed
Stored Procedures
When creating a stored procedure that contains nested loops ensure that the outer loop returns the least possible number of records. If possible structure your stored procedures so that the inner loops are always the fastest, as they are iterated through the most number of times.
Do Not Use Large Chars or Varchars
Before Interbase 7.0 Varchars and Chars were padded out to the field length when being returned to the client. If you only populate part of a large varchar or char field and then return the results from the client, a large amount of network traffic is being generated, which will degrade your applications and networks performance. If possible do not use large Varchar or Char fields, or alternatively use BLOBs. A Blob also has the advantage that it is stored on its own page in the database and thus reduces the chance of any locking occurring. Alternatively upgrade to Interbase version 7.0.
BLOBs
A BLOB (Binary Large Object) is a data type that supports large objects. A Blob is defined with a segment size, and in Interbase this defaults to eighty bytes. If a Blob field is defined with a segment size equal to the page size of the database, then queries of blob fields become extremely fast, as only one page needs to be retrieved to return the data. Under these situations Interbase is not a bottle-neck for data transfer, other systems such as the System Hardware, Network etc usually slow the data transfer rate down.
Use Client-Server Processing
Interbase provides a number of features that allows processing to be performed on the server (which often has more processing capacity than the client machines) and to reduce Network traffic. Triggers, UDFs and Stored Procedures are all adequately detailed in the Programmers Guide and Data Definition Guide without being detailed here.
Query Plan
Interbase uses a cost based optimiser to optimise the execution of SQL statements. In most cases the optimiser does a very good job. Under some circumstances the optimiser does not select the best plan for performance. Be warned that if you specify your own Query plan, the optimiser will not analyse your plan to ensure that it is correct. Details on when and how to adjust Interbases query plan can be found at the following sites:
http://www.ibphoenix.com Specifying Query Access Plans in the Interbase Optimiser on the documentation page.
http://www.mers.com Managing your Interbase Server by Paul McGee.
RDB$DB_KEY
The rdb$db_key is a low level record identifier which is faster than primary keys for retrieving records. Rdb$db_keys are only valid for the life of the current transaction, and cant be thought of as an unchanging record identifier. It is still possible to utilise rdb$db_keys to increase performance of SQL routines in an Interbase database. The disadvantage is that this performance gain is Interbase specific. For more details on how to utilise the rdb$db_key, please refer to the documents located at the following website http://www.cvalde.com/ibdocumentation
SERVER GUIDELINESS
Use a Dedicated server
A dedicated server provides the best performance for a client server database. The cost of inadequate performance far outweighs the cost of purchasing a dedicated server. Poor performance will reflect on you as a developer and will usually result in time spent identifying, and rectifying problems. It does not take too much of your time before the cost of a dedicated server is justified.
Use Linux or Unix as a Server OS
Linux or Unix Servers, have better memory and virtual memory utilisation, has a good multi-processor prioritisation model, and often requires less CPU and Memory resources than other operating systems. Linux servers demonstrate impressive uptimes when compared to their Windows counterparts. Linux also does not seem to be plagued by mysterious processes which degrade performance, and which seem to disappear as soon as you log onto the server to examine the problem. Linux or Unix servers can be integrated into a Windows network with SAMBA providing a seamless environment as far as the client machines are concerned.
If you have to use Windows
Check the configuration of your Windows Server to see if it is configured to provide maximum resources to file sharing or to Background Applications. As these options are located in different areas on different Windows machines refer to your Windows Documentation. This setting can have a large impact on the performance of Interbase.
Use Single Processor Machines with InterBase
If you are using a version of Interbase under Windows NT before version 7.0 then do not use a Multi-processor server on the Windows platform. If you do the Interbase server process will flip from processor to processor causing degradation in your Servers performance. If you do want to use Interbase (prior to version 7.0) on a multi-processor system then use a tool to tie Interbase to only one processor (there are a few of these available including one from Microsoft). However you should still be aware that since some of the earlier Windows NT operating systems have not implemented a proper SMP support model, the performance gain might not be as much as you would expect. Linux systems have proper SMP support.
Use a Dedicated Hard Drive
If you have your Database stored on the same drive as the pagefile of a server, the increased I/O operations of the servers virtual memory operations will impact on your applications performance. Always locate your database on a separate drive. If you have the money then I would suggest that your operating system goes on one drive, your Database on another drive, and the swap file on another drive.
Fast Disk I/O
Disk I/O operations are often a bottleneck for Database performance. IDE drives often use some of the CPU resources. Always go for the fastest SCSI systems you can afford. Skimping in this area will cost you big in terms of degraded performance. RAID systems also offer better performance than single disk systems. Aproper RAID array comprised of SCSI drives is the best solution.
Use a Static IP on the Server
A static IP on the server means that the IP address of the server can be found faster, than using name address translation services. This IP should be recorded in the Servers Hosts file, and in the host files of client machines.
USE TCP/IP as your Network Protocol
TCP/IP is a connection-based protocol (packets are routed to the intended recipient rather than going to all machines) which generates less network traffic than connectionless protocols like NetBEUI and IPX/SPX. TCP/IP is also faster than either of these protocols, and has the advantage of being available on a wider variety of platforms. Where-ever possible TCP/IP should be used to communicate with Interbase for its speed advantage. To further enhance performance it is advisable not to install multiple protocols on the network as this increases noise on the network. If this is unavoidable then ensuring that the network protocol that you use is first on the stack will give you more priority across the network.
Dont use Screen savers
Screen savers, particularly 3GL types can be processor intensive, and will degrade system performance noticeably. In most cases screen savers are not necessary as modern monitors are designed so that the risk of screen burn in is negligible, energy saving monitors will switch themselves off and save power. If you dont have an energy saving monitor, then just turn it off, in the very least you are using less electricity and not generating as much heat in your server room. If you have to use a screen saver then the blank screen or the marquee (set the speed of the text scrolling across the screen to as slow rate to avoid excessive CPU utilisation) screen saver as these use the least amount of resources.
Console logins
Many people tend to use Windows NT/2000 as they are more familiar with the Windows Operating System. If you do use Windows NT, then dont unnecessarily log into the server, or stay logged in. Apart from being a security issue, leaving the server logged in allows process to run in the background, which can degrade the Servers performance. Even background processes can use 20-30% of the servers resources. There are numerous tools that allow you to maintain your Interbase Server without the need to have the server continuously logged in.
Use the Same Interbase Client Version as your Server
If you have an outdated version of Interbase Client Installed on your client machine, compared with version installed on your Server you may be suffering a performance hit. Tests with Interbase 5.1 and 5.6 can demonstrate a performance difference of slightly more than 50% by using an outdated client installation. Also newer features may not be available if you are not using the same Client and Server versions and program errors could result (in some cases this can cause your Interbase Server to crash and possibly corrupt your database).
Overcoming Disk I/O restrictions
It is possible to reduce the problem of Disk I/O operations in Interbase. After having performed a backup and restore, create a temporary table in the database (using Blob fields or large Varchar columns). Populate this table with a large amount of data. Then drop the temporary table and sweep the database. This means that Interbase will reclaim the space inside the database during operations, and will not have to ask the OS for disk space until the empty space inside the database has been reclaimed. This increases the speed of the write operations to the database, as disk I/O is essentially not a limiting factor.
Windows File Protection
Some versions of Windows (Windows XP) have implemented Windows file protection mechanisms for files with the .gdb extension (Interbase Database). If you are using a Windows Operating System greater than Windows 2000 you may wish to experiment with de-activating Windows File protection or renaming your database to have a different extension (for Interbase Database greater than 7.0 or Firebird 1.5). The cynical amongst us might wonder if Microsoft did this to reduce the performance of Interbase in comparison to Microsofts own Database SQL Server?
INTERBASE CONFIGURATION
Regularly Backup and Restore
Your database is a source of competitive advantage to your customers because of the information that it contains. Performing regular backups is essential to ensure the integrity of data for your clients. A restore is just as important as it optimises the database by:
rebuild indices;
eliminate obsolete record versions (Garbage Collection);
defragment database pages;
rewrite database tables contiguously; and
recalculates Database Statistics.
A good tool for performing backups and restores is IBBackup which is available from www.ibphoenix.com.
( Note: It is suggested that before restoring your database that you make a copy of the original, and then perform your restore. This is since the Restore does Integrity checking of your database, as this is processor intensive. Essentially this means that it could be possible to have a faulty backup that cant be restored. The backup does not do this because the idea of backing up is to secure your data without impacting on system performance.)
Interbase Priority Class
The Interbase Priority Class should be set to high (SuperServer version under Windows) to ensure that Interbase can demand more of the systems resources. This can be done through IBConsole in the Windows version. Or edit the ibconfig file and set the SERVER_PRIORITY_CLASS to two to achieve this (remove the # to uncomment this line otherwise the default will apply).
Turn Async Writes off
Under Windows NT, Interbase defaults to having forced writes on. This means that write operations to Interbase, go direct to the database and not through the Operating System Cache, which is slow. On Linux systems the reverse occurs. This can result in a performance difference of more than 300% for some operations.
The problem is that if you turn Async Writes off under Windows, and the Operating system crashes while Interbase is writing to disk, you run the risk of losing data, and potentially corrupting your database. As Linux is inherently more stable this is not so much of a risk. If you wish to turn Async writes off then make sure that you make lots of backups of your database and that you have a good UPS on your server (You should do lots of backups and have a good UPS anyway). (GFIX -WRITE ASYNC/SYNC MYDATABASE.GDB)
Turn off Garbage Collection
Interbase by default performs a sweep of the database to Garbage collect old records when too many accumulate. This unfortunately can severely impact on the performance of the client process that caused this threshold to be reached. To disable automatic Garbage collection, you issue the following command from Gfix to your database gfix -h 0
(Note: Regular Backup and Restores will perform Garbage collection on the Database. This is another reason why the database should be backed up regularly and restored as often as is practical).
Lock Hash Slots
The Lock hash slots parameter is used to determine the size of the hash table utilised to find locks on a particular database object (this is true of all systems except VAX/VMS). The number should be a prime number to help the hash algorithm produce a good distribution. Usually the first indication that a problem exists with the size of the Lock Hash Slots configuration is declining performance for the Interbase Server (where you have lots of users and a large cache pages). To determine if this is the problem produce a Lock table print out (while the system is actively being used) and examine the average hash length. If this is greater than 10 you have a problem. To calculate a new Lock Hash Slot parameter multiply the average length by the current number of slots and divide the result by nine. Adjust this up as necessary to ensure that it is a prime number, but one that is between 101 and 2048 (these are the limits for this parameter). To change the parameter edit the ibconfig file and change the value, but remember to remove the # from LOCK_HASH_SLOTS so this setting will take effect. I initially set the LOCK_HASH_SLOTS to 501 for most Interbase installations that we use. If you perform this on the Super Server Architecture you should increase the lock table size as well.
Database Page Size
The Database page size determines how much data will be retrieved with one logical access to the database. Interbase has a default 1Kb page size, with permitted values being 2 Kb, 4 Kb, and 8 Kb.
For small Databases (less than 4 Gig in size), set the Database Page size to 4096 (4 k) bytes, for large databases this should be set to 8192 (8 k). Tests on database performance indicate that a change from a 1 Kb page size to a 4 Kb page size can increase database performance around 20%.
By having a 4 Kb, page size for most databases, the database I/O matches the operating system I/O default sizes, resulting in greater efficiency and performance. Other advantages of a large page size include:
Less record fragmentation as more records can be stored on a page.
Fewer pages returned for a given query (as records are stored more contiguously).
Index B-trees are shallower.
I/O is more contiguous.
The above Database Page sizes are guidelines only; it is recommended that you perform some testing, to determine what size is most applicable to your system.
For a change in the database page size to take effect you will need to perform a backup and restore of your database.
Working Set
The working set configuration parameters are only applicable to Interbase run under Windows (using the Super Server architecture). These configuration parameters determine how much RAM is dedicated to the Interbase process. The minimum Process working set defines the minimum amount of physical RAM that is guaranteed for the Interbase process. The maximum working set defines the amount of memory above which Interbase will start to use the system cache. I recommend that the minimum working set is determined by taking the allocation for the Database Cache Pages and adding three megabytes. Leaving the maximum Process working set to zero will allow the system to determine the point at which Interbase needs to swap to disk. The maximum working set should always be higher (or set to zero) than the Database Cache otherwise the system will continuously page to disk for all operations.
Database Cache
The Database Cache is a Cache of the number of pages of the database that are cached by the Servers RAM. Some experimentation with setting the database Cache (in ibconfig) to a value between 256 and 10,000 produces increased performance up to a point. After this point declining performance is noticed.
It is also a good idea to set the cache for the database using GFIX with the following command. Gfix buffers 10000 user sysdba password masterkey mydatabase.gdb. If you have a small database then you should use statistics to find out how many pages are in the database. You shouldnt set the Cache on the database to a figure higher than the number of pages in the database, since any given page from disk will only occupy one page in the cache.
DEVELOPMENT CONSIDERATIONS
Always work with the smallest amount of Data possible
Network traffic is a major factor in the perceived performance of your application. If you have 100,000 records and you do something like Select * from myTable you will be pulling down all the records. Using filters to only return a subset of data helps reduce this. If possible avoid the use of grids that display a large set of records. Use SQLMonitor (or its equivalent) to profile your SQL commands and see what ACTUALLY happens as your application runs.
Dont Keep Transaction Open longer than you need
Holding a transaction open can lock records in a database. Holding transactions open for a long period of time will also increase the amount of memory that Interbase requires to keep track of those transactions. Instead of starting a transaction as soon as a data input screen is opened and then committing the transaction when the user presses Save, it is better to get a copy of the data and then start a transaction and commit the transaction as soon as the Save button is pressed. A number of techniques facilitate this approach, such as ClientDatasets. These techniques have the advantages of:
Reducing Network Traffic.
Reduces the Risk of Database Locks
Reduces the life of a transaction
Reducing the amount of Memory that Interbase requires keeping track of those transactions.
Large System, Lots of Traffic, Cache Your Lookup Tables.
If you have any data that does not change (such as States, or Countries), then you have an opportunity to cache this information in a ClientDataSet on the client machine (rather than retrieve the details from the Server), then you can simulate a join on the client side by using a calculated field. This allows you to reduce the number of fetches that you need to do to the database, and reduces your network traffic.
Program Directly to the InterBase API
If speed is an absolute necessity then a program written in C with embedded SQL performs better than an Interbase Client application running through layers of middleware (such as the BDE etc). This type of application is programmed directly to the API in GDS32.DLL, which produces its speed improvement.
Use a Remote Connection
When developing your application do not use the local connection string when connecting to your database (c:\path\mydatabase.gdb), as it uses memory-mapped files to communicate to your database. With some versions of Interbase the use of local and remote connection strings together could cause a physical corruption of the database. Use a remote connection string, as this will give a better indication of your systems actual performance (servername:c:\path\mydatabase.gdb) and will allow you to pinpoint problems early on. Better still; actually develop your application by connecting to a database on another machine across a network.
Fully Populate and Test your System during Development
Often developers will use a cut down system during the development and testing process, on their development machine. The disadvantage of this is that retrieving only a few records (or even a few hundred records) locally will be extremely fast. The reality is that you are most likely going to be developing a client server system which has to run across a network, and may have many thousands or even millions of transactions in your system. To test your system, run it across a network, ensuring that the database is properly populated (and I would recommend populating it with around 1/3 more records than you would expect that someone would have in their system). Preferably perform your tests on the minimum hardware and network environment that you would expect your system to run on.
Prepare and Parametise your Queries
Where ever possible write a query/statements which can be parameterised and prepare the query before executing it (it only needs to be prepared once as it stays prepared until you explicitly unprepare it or you change the contents of the SQL property). Prepared and parameterised queries/statements are faster than non-prepared parameterised queries/statements.
VCL Components
A TQuery component was designed for use in client server applications, and should always be used in replace of a TTable component which is for local database development. When using TQuery, allow the server to handle updates, deletes and conflicts by having RequestLive= False. Dont use Locate or RecordCount as these perform a fetchall. Use a where clause to ensure that the server does the filtering of data for you.
Think about what you are doing
When designing your system remember just because Interbase lets you do something that does not mean to say that it is a good idea. All actions have a consequence. Think about how you are designing and developing your system and what the ramifications are going to be for you, the network and the clients. Remember it is possible that someone else will have to maintain this system.
Somethings will be slow
When developing a system if you have to perform the same task over and over again, say a million times, it is going to be slow regardless of how fast your client and server are. If this is the case see if your design philosophy can allow you to design this type of processing out of the system. In some cases this is not going to be possible, in which cases you may need to employ a system where this sort of processing is performed outside of normal client usage.
Use the Client
In a client server system, you dont have to send everything back to the server for processing. You can distribute the load by performing some of your work on the client machine. If you cache the details of your calculations then the advantage of this is reduced network traffic, shorter transactions and not bogging the server down with every single process. An example of this would be calculating the cost for each item on a purchase order and the total for the purchase order on the client.
See what your application actually does rather than what you think that it does
Tools such as SQL Monitor give you an insight into the way your application requests data from Interbase. These tools are invaluable as they allow you to see things like:
Connect/Disconnect
Transactions
Execute Statements
And Statement Operations
Also various VCL components behave in different ways. Using SQL Monitor gives you the opportunity to investigate what is going on behind the scenes with these components, and also what happens when you make design changes to your application.
Other tools, which should be in your Application Development Arsenal, include:
Interbase Performance Monitor by Craig Stuntz (requires Interbase 7.0 or higher)
Interbase Plan Analyser by Craig Stuntz
Sleuth QA Suite by TurboPower Software (Allows you to performance tune your Delphi/C++ Builder Application)
Use Native Access Components
The BDE is now a deprecated system and will no longer be supported by Borland. Components like IBX, FibPlus, and IBObjects provide a performance increase by around 40%, in comparison to the BDE. These components also provide access to more of Interbases internal features. The use of such components however does tie you to the Interbase platform. If this is a problem then the DBX components, provide you with the ability to connect to Interbase and other databases, while still obtaining good performance (not quite as good as IBX etc, but certainly no where near as slow as the BDE).
Searching for Records
A Soundex is a method of indexing that was developed by the U.S. Census department, for grouping similar sounding names together. As an example performing a search of 300,000 records for the name smith could take around 2.95 to complete, whereas a soundex could yield the result in around 0.77 seconds, except it would include the name smith and names that sound like smith. For a good overview on creating and using a Soundex function refer to the article Implementing a Soundex Function by John Midwinter found at www.ibphoenix.com.
OTHER WAYS TO IMPROVE PERFORMANCE
Upgrade Interbase
Each new version of Interbase (or firebird) implements enhancements that increase the performance of the RDBMS, or increases programmer flexibility through language extensions. The increased performance and functionality allows you to build a better client server system. Due to the recent enhancements to these products anyone using a version of Interbase prior to version 7.0 should consider upgrading. If this is not an option, then consider upgrading to the latest version of firebird.
Read Only Databases
A database will normally leave some free space on its pages (about 25% is left free) to allow for new record versions. In a database where the primary purpose for retrieving and viewing data, this means more pages need to retrieved to view the data. For these read only databases these pages can be filled up so that the data is more contiguous and less pages need to be retrieved by using the following GBAK command:
GBAK -C -USE_ALL_SPACE backup.gbk mydatabase.gdb