Library

Testing application’s queries (the excerpt from "Migration Guide To Firebird 3")

This is an excerpt from "Migration Guide To Firebird 3" book, by Carlos Cantu. 

Changing from one Firebird version to another can cause problems with existing queries, ranging from bad performance to total failure when queries use newly reserved words or incorrect SQL that had been accepted.

An application often includes hundreds of queries, from browsing information on the screen to creating reports to data entry and validation. There could be external scripts and query generators which generate SQL queries on the fly, so it is not possible to check all combinations of SQL queries even with the thorough check-up of application's sources.

HQbird FBScanner from IBSurgeon is a tool that can help you check that existing applications work with Firebird 3 and find out if there are errors or slowness in SQL queries of migrating applications. FBScanner is a part of HQbird Standard, the package of analysis tools that monitor and optimize Firebird servers. It allows you to log queries on your current version of Firebird and test them on the new Firebird server.  

FBScanner works as a proxy between the clients and the server, logging all communications: connections, transactions, SQL statements texts, number of fetches, SQL plans, errors, and prepare and execution times for the queries.

You can install FBScanner while using your current version of Firebird; configure it to monitor the communication and to store queries in a log database. The most popular configuration is to install FBScanner on the same server, on the port 3050 and change Firebird port to other values (3053, for example): in this case existing Firebird applications will continue to work with the database(s) on the server without any change in their connection  string:


​

Also, it is possible to setup FBScanner on the third-party computer, and route only several clients through FBScanner to catch only their communications.

After a few days of monitoring, the log database contains a large number of queries used in your applications. Ideally, it should cover all SQL activity in the database, with the details about times and execution:

​

FBScanner allows you to execute the queries you have logged. Using the log database, you can set up a test Firebird 3 server, restore the original database to it, and use the FBScanner Log Analyzer to run the logged queries.
FBScanner displays the result in a grid, including the performance information, so you can compare the current execution time with what you got in the old server, and compare the old PLAN to the new PLAN.

​

The information in the grid can be exported to an Excel spreadsheet.

When comparing the execution time of queries, keep in mind that external factors influence the result. One consideration is whether the caches of the operating system or Firebird are "cold" or "hot". Obviously, queries performed when most information is already cached are faster than when retrieving data from disk.

You can purchase the full version of "Migration Guide To Firebird 3" here.

The HQbird Standard (which contains FBScanner) can be bought here. We offer 10% discount for HQbird Standard for those who have bought "Migration Guide To Firebird 3" - please contact us to get a discount coupon code.

IBSurgeon also offers comprehensive "Migration Service for Firebird" - we can help to migrate the databases with complex stored procedures, queries, and other logic, from early Firebird versions (1.0, 2.0, 2.1, 2.5) to modern (2.5, 3.0, or even 4.0).