[SQL] PERSISTANT PREPARE (another point of view)

Hi,

We are new to Postgre, actually we are migrating from MICROSOFT DBMS technologies to...hopefully Postgre.
Our company is involved in ERP business software in Serbia and region, currently counting over 200 clients. Some of them have DB's over 4GB in size.

Reason for posting is implementation of PREPARE statements.
I've read a thread "# PREPARE and stuff PFC" on pgsql-performance 2007-06 list and I do agree that it would not gain performance issues.

What could we gain by introducing a kind of global prepared statement area, is SIMPLICITY of DB DEVELOPMENT AND MAINTENANCE.

Here is our point of view:

We have an application layer running over db layer. Application layer consists of classes and interfaces and db layer contains data and various data manipulation structures.
Application layer calls SQL statements expecting some datasets as results (inventory list for instance). What it doesn't care about is HOW is query built (sorting, conditions, etc.) as long as it returns EXPECTED columns. Application simplly calls EXECUTE <statement> (<param1>,<param2>...). Developers working application layer do not interfere with developers working on DB and queries. Plus MOST queries can be written to be reusable in various situations !!!

The idea is: LETS SEPARATE SQL STATEMENTS FROM APPLICATION CODE.

This way, we can introduce fine tuning to each of our clients without having to recompile our application. We can also work on improvements of queries performance and complexity without recompile of the application layer.

Since one company has one set of rules PREPARED statements apply to every client connected to that database.

Now, instead of preparing statements on each connection request (and we use around 900 prepared statements), why couldn't we simply prepare these statements ONCE and keep them in some global storage for future everyday usage.

We use this approach for forms & reports creation where Crystal Report engine creates outlined report based on static prepared statement.

This approach is probably not suitable for large db systems with tons of data, but is very efficient in 90% of small and medium business size databases.

Please consider this issue when planning your WISH LIST or hopefully To-do-task-list.

Ill be glad to here comments on this topic as well.

Milan Oparnica
MELANY SOFWARE TEAM

---

Invite your mail contacts to join your friends list with Windows Live Spaces. It's easy! Try it!