IBExpert Newsletter 02d/2018

Firebird White Paper

Software Performance Optimization

Holger Klemt, February 2018

Print version

You use self-written software for your own company or for your customers and wonder why processes take longer than planned?

SQL performance evaluations return very fast results when tested individually. But as the database increases in size the performance deteriorates. Therefore there must be a problem somewhere in the program flow, but even using familiar application debug techniques you cannot really pinpoint the actual root of the cause.

The Remote Software Architect

IBExpert is specialized in offering our clients deep insight database and application analysis in order to find optimal performance advantages. We will find the critical performance areas for you and consult you with the recommended improvement ideas in order to get your application ready for large custom installations and data volumes to deliver optimal performance. This service is provided especially for Firebird database systems or applications written in Delphi/Lazarus/C++/C# or Java technology.

In the case of a Firebird or InterBase database, we will review the execution of SQL commands using IBExpert’s Monitoring and Trace API tools. Often these tools provide us with clear hints like SQL commands being executed quickly but unnecessarily often, or ignoring basic mechanisms in the database. Even at the network protocol level, we examine your application, and when the debugger jumps from one line to the next, there is often significant network traffic created, which should be identified and corrected. Even a seemingly simple record count property should be considered with caution.

Missing indices are often the smallest problem. The programmer is often not even aware of the negative effects of commands using MAX(), TRIM(), or UPPER() functions. Incorrectly formulated JOIN relationships often lead to a considerable additional burden for the database, even when returning simple result sets. In the age of fast SSDs, the problem is hardly noticeable locally on the developer’s machine, but in a network with 20 or 50 users, the impact can be immense.

Low CPU utilization initially indicates that the data medium is too slow. But the performance problems are mostly in other areas. For example if a query runs isolated and creates 50 GB read/write operations, it could be the result of a faulty query or a too small set cache. But if you choose to set a too large cache, you can create significant disadvantages.

With special tools for Lazarus or Delphi, we make sure to localize not just database-oriented operations that could slow down the application process. For example excessively frequent Application.processMessages in loops or underperforming retrieved data calls from TStringlist objects can be localized. Even invisibly generated objects in complex multi-page forms, or seemingly trivial color runs on components can have unforeseen negative impacts, especially in terminal server applications.

You will see that our 23 years’ experience in Delphi development and 8 years’ Lazarus can certainly teach you new aspects of how to rebuild and optimize your existing source code without rewriting the entire application. Optimal performance will allow your application to get utilized in large client projects.

In our IBExpert Firebird 3 Bootcamp we will provide insights into the above technologies, and participants will be able to apply significant improvements to their own software solution.

If your time does not allow you to visit our IBExpert Firebird 3 Bootcamp, we offer a 12-hour remote education and project support by telephone and TeamViewer/pcvisit/ GoToMeeting. The training takes place in 3 daily sessions of 4 hours each. The price for distance education is $2450 USD.

Please contact our IBExpert office in Orlando, Florida for more information. You can reach us email at contact@ibexpert.com or telephone: 407-462-7283. We are looking forward to hearing from you.