Subject Firebird Prepared Queries
Author ruan_h
Hi All

We have a Delphi application running on Firebird 2.1 64 bit classic on Linux with about 280 connections during peak times. We use FIBPlus to access the database.

We've always encouraged our developers to parameterise SQL queries so that we can re-use prepared queries, but recently it has been brought to our attention that how many queries should we keep prepared? How long should we keep them for? Are the overheads (e.g. local memory, database server memory, etc.) of keeping them worth doing?

To get a better idea of this, we try to learn more details of how Firebird manages prepared queries:

1. What's the effect of having too many prepared queries in each connection? Does Firebird have an optimum number of prepared queries kept in cache?

2. Does Firebird ever unprepare some queries when there are too many or does it only do this when clients ask it to? If it's the former, what happens to the handles that clients hold to the prepared queries?

3. If I have an SQL already prepared and I ask for prepare the same SQL again, will Firebird return the already prepared one or prepare it again?

That's the questions we can think of at this stage. Any information and opinions regarding this will be appreciated.