IBExpert Database Solutions: How to improve you Firebird/Interbase database?

How to find the procedures, trigger and views, that do not use an index in their operations?
How to find the procedures and triggers that have typical type casting problems?
How to know if your database servers garbage collection is working?
How to change the Character Set of all tables in a database?
How to compare the metadata of different databases and create a script that does all required changes?
How to activate a Version Control System for stored procedures and Triggers?
How to compare the current database object with an object in another database?
How to create a sql for a insert, update, delete or other statements automatically?
How to find all objects that have dependencies to the current object?
How to compare different hardware and database server concerning the speed?

See Solutions from the IBExpert Team below:

How to find the procedures, trigger and views, that do not use an index in their operations?

Just open menu Tools-Stored Procedure/Trigger/View Analyzer and press F9. This analyzes all objects and
shows parts that do not use an index in red color. To modify these objects, just double click the line. A
well designed database should have no red line.

How to find the procedures and triggers that have typical type casting problems?

A typical problem that is often not so easy to find is when a varchar(20) column is copied into a varchar
(10) variable. In most cases it makes no problem, but when the source has more than 10 characters you get a
runtime error. This will typically only happen in your customers database. To find these errors, just open
menu Tools-Stored Procedure/Trigger/View Analyzer and press F9.

How to know if your database servers garbage collection is working?

Just open your database, open menu Services-Database Statistics and press F9. On the summary page you can
see a versions column with subcolumns versions, version length and max versions. When the garbage collection
works properly, there should be only very low values for versions and max versions. When there are higher
values, your garbage collection do not work propery, which might have several reasons, typically improper
transaction handling in your application.

How to change the Character Set of all tables in a database?

In menu Tools-Extract Metadata, you can create a script that recreates the database and also inserts the
data incl. blob data. In this script you can do a search and replace for the character set name and after
rename the original file execute the script again

How to compare the metadata of different databases and create a script that does all required changes?

Use Tools-Database comparer. This will create a script that might be used to apply all required changes to
synchronize the database structure.

How to activate a Version Control System for stored procedures and Triggers?

In IBExpert Stored Procedure or Trigger Editor, just click on the Version History tab and confirm the
dialog. Now all changes on any of these objects are stored automatically and the different versions can be
compared in this window.

How to compare the current database object with an object in another database?

In the database registration info, just set the Comparative DB to the reference database. In most Object
Editors you can click on the Comparison Tab and see the differences between the objects current database and
reference database.

How to create a sql for a insert, update, delete or other statements automatically?

Just open SQL Editor and Drag and Drop the table or any other object from the database explorer to the
editor. A wizzard shows you different possible sql statements for this object.

How to find all objects that have dependencies to the current object?

Just open menu Tools-Dependencies viewer and drag and drop the object in the viewer. It shows you all
objects that have dependencies to this object recursive in a tree structure.

How to compare different hardware and database server concerning the speed?

We recommend to use the IBExpert Demo Database script in program filesHK-SoftwareIBExpertDemoDBdb1.sql.
This creates a simple shop database. When executing the procedure initall with a parameter 10000, it creates
10000 customers, 10000 products etc. All in all there are 750000 operations in the database, which will have
a size of 25 MB.It should run between 10 and 20 seconds. For creating a larger database, just use a larger
parameter, for example initall(100000) creates a database of about 250 MB with about 7.5 million operations
etc. For using this script with Interbase, please replace the select first 1 syntax to select …. rows 1 in
the script before executing it. Firebird 2 showed a speed improvment of about 75 % compared to firebird 1.5

The IBExpert Team

The most Expert for InterBase and Firebird – www.ibexpert.com
HK Software – 26135 Oldenburg – Germany – info@h-k.de – www.h-k.de

You receive this email because it was used in the registration process of IBExpert.
If you want to cancel, just reply with the word UNSUBSCRIBE in the Subject.