IBExpertDemoDB
IBExpert comes with a sample database, created simply and quickly using the SQL script provided. This demo database should work on all Firebird and InterBase® databases without any problems.
It is not permitted to publish any content of this script anywhere without express permission of IBExpert Ltd, Holger Klemt. To obtain such permission, please send an email to sales@ibexpert.biz with a brief explanation of where, how and why you want to use it.
Preparation
Firstly you will need to have IBExpert installed (the free IBExpert Personal Edition is sufficient, if you do not already have a registered IBExpert version). Please refer to Download and install IBExpert? for installation instructions.
Once installed, you will need to go to the HK-Software\IBExpert Developer Studio\IBExpert Demo Databases directory:
which includes a simple script and UDF libraries. These DLL files need to be copied from this directory into the Firebird\UDF directory. User-defined functions and the most popular libraries are explained in the Database Objects chapter, User-Defined Functions. The function we will be using is called GETRANDOM, and will be used to generate random data in our demo database. The interesting thing about this function is that is does not really generate a random value or string. The random value is 100% reproducable, i.e. when you next generate a database using the same sql and procedure and same number of data sets, you will get exactly the same data generated - ideal for performance testing.
Should you wish to generate entirely random data, the GETRANDOM function can be replaced by the INITRANDOM command.
db1.sql
Open IBExpert and go to the IBExpert Tools menu item, Script Executive, and press [Ctrl + L], right click and select Load from file to load the db1.sql file from the IBExpertDemoDB directory. If you take a look at the Statements page in the Script Executive, you will see just how many statements this script contains:
After loading, press [F9] to execute the script. Initially the database is created, a generator, an exception empty stored procedures and empty tables and, after filling the TMP tables with data, the whole is concluded by specifying primary keys, foreign keys, indices, triggers, the procedures are filled with their content and Grants are specified on all database objects. The TMP data is inserted using the IBExpert command REINSERT. This is the equivalent of the Firebird command
INSERT INTO TABLE (FIELD_NAMES) VALUES (FIELD_VALUES);
REINSERT does nothing other than execute the initial INSERT INTO again.
This script has created a database with table structure commensurate to a simple DVD store. It can be used to generate a classic database with a large amount of test data for a commercial environment.
Register the database in IBExpert
Database registration is necessary, in order for IBExpert to recognize the presence of the database. It is possible to specify certain options, settings and defaults here. The Database Registration Editor can be opened using the IBExpert menu item Database / Register Database, or key combination [Shift + Alt + R]. Refer to the IBExpert Register Database chapter for details.
Following registration you can open the database simply by double-clicking on the database name (or alias name, if you have specified an alias) in the IBExpert Database Explorer to connect to the database.
You can now view the structure of the database objects listed in a tree form in the Database Explorer. However the tables are still empty (only the TMP tables have any content). So the next step is to fill these tables with data.
INITALL stored procedure
A common problem when writing database systems is to start with a completely empty database, as this gives you an artificial view of how the database is going to work and how the program is going to work in a real environment. When the new system is installed it seems to work fine, but as more data is added certain things start slowing down considerably. It is imperative that database systems be tested and analyzed using realistic test data quantities. The same applies to bench mark testing. It's worthless testing and comparing systems with 100 data sets, when you know the system will be dealing with 100,000,000 data sets within the year.
INITALL is a stored procedure which populates the database with data. Open the procedure by double-clicking on the procedure name in the DB Explorer. Run the procedure with [F9]. You will be asked to enter a parameter - the number of data sets you wish to be generated. A reasonable sized database can be created with the parameter 10,000.
INITALL invokes four other procedures:
- DELETEALL: deletes any data that might still be stored in the database from previous tests.
- CREATE_CUSTOMER, CREATE_PRODUCTS and CREATE_ORDERS: then proceeds to generate customers, products and orders.
If the parameter 10,000 has been specified INITALL creates 10,000 customers, 10,000 products, 20,000 orders and approximately 60,000 order positions. Altogether around 800,000 operations are performed to create this data, this in itself providing a simple benchmark test to compare different hardware systems and/or database versions. For example, on a standard laptop Firebird 1 took around 60 seconds to generate the data, Firebird 1.5 about 50 seconds and Firebird 2 approximately 30 seconds.
On the ORDERS table Firebird has carried out 80,222 reads, 20,000 inserts and 20,000 updates.
Please refer to the Performance Analysis chapter for further information.
Important: Although all this data has been created for the database, until it has been committed, you will notice no increase in the database size. Commit is the command which finally writes all alterations to the hard disk. It is at this point that all other users connected to the database can see your alterations. If you're not happy with the data you've generated and want to cancel, you can simply roll back all that the procedure has performed to return to the original database status. Please refer to Data Transaction for further information.
If you now disconnect from the database, you will be able to see your actual database size. The database without any data content was about 4 MB; with a 10,000 data set generation it is now 25 MB. Hence using the parameter 100,000, a database size of approximately 250 MB is created, with 1,000,000 a database size of 2,5 GB.
Now you've seen how this works, you can of course adapt the database structure and procedures accordingly to generate a test database commensurate with your enterprise's requirements.
