Subject Re: [firebird-support] Best strategy for performance analysis on stored procedures
Author Thomas Steinmaurer
> We have a database with about 500 stored procedures in it, and I'm finding a number have queries that are executing with non-indexed searches. As the user community on this application is increasing, this is becoming harder for the users to deal with. This is a legacy application in FB 1.5 so I don't have the luxury of upgrading it at this time. I'm looking for a tool or a technique to identify performance issues, particularly issues relating to non-indexed searches.
> I took a quick Google search on this and found tools like FBScanner out there, but is there a generally accepted method or tool for detecting bottlenecks in performance such as non-indexed searching?
> All advice is greatly appreciated.

Does analysis need to be in production/customer environments or in your
testing environment? If latter:

- And you basically get similar execution plans compared to production data
- And your application doesn't need to be re-written to run on-top of
Firebird 2.1+

You still could try to adopt the enhanced monitoring capabilities
available since 2.1+. One problem might be that you get different
execution plans with 2.1+ than with 1.5.

If you are stick with Firebird 1.5, even in a test environment, you can:

* Try to use FBScanner as it is acting as a proxy between your client
application and the Firebird server, or

* Use client side tracing via your database access components. E.g.
IBObjects, IBDAC etc., they all have monitoring components tracing
executed statements of your client application.

With regards,
Thomas Steinmaurer

Professional Tools and Services for Firebird
FB TraceManager, IB LogManager, Database Health Check, Tuning etc.