PostgreSQL performance - CAPIPreparedStatement

[rayanAyar]

I migrated from Zeos 7.0.3 to 7.1.2. And PostgreSQL performance highly reduced in some parts of code. One query which executes several times per minute:
on 7.0.3 takes 150 milliseconds
on 7.1.2 takes 1000 milliseconds

Еxamining and tracing code, I found some dirty solution:
file "src/dbc/ZDbcPostgreSql.pas", function TZPostgreSQLConnection.CreatePreparedStatement:

Code: Select all

      if GetServerMajorVersion >= 8 then
        //Original code: Result := TZPostgreSQLCAPIPreparedStatement.Create(GetPlainDriver, Self, SQL, Info)
        Result := TZPostgreSQLClassicPreparedStatement.Create(GetPlainDriver, Self, SQL, Info)
      else
        Result := TZPostgreSQLClassicPreparedStatement.Create(GetPlainDriver, Self, SQL, Info);

CAPIPreparedStatement works very slow in my case, so I changed it to ClassicPreparedStatement.

In this regard, I have two questions:
1. Is this a bug?
2. If this is not a bug - can I perform such patch as described above? Will this affect other functions or stability?

[miab3]

@rayanAyar,

Could you give an example?

Michal

[EgonHugeist]

There is one Main-difference on 7.1 between the two statments:

the CAPI one always !prepares! the request, while the Classic stmt just do it if ParamCount > 0 or ExecutionCount > 2.

So you difference is because of the Prepare compile on Postgres-side. BUT a second or more execution should be loads faster than. Again just the first call should be slow. Reopening same query performes loads (hope the slow PG is really able to do that) faster than.

Also is this an design thing on your application. Try to keep one query request alive until you close your app. Than the prepared stmts do really make sence. Also working with parameters is prefered....

So could you test reopening your select with min. 5 times?

ps. Prepared stmts do have advantages but preparing the execution plans first does take time. IMO the CAPI type should be the fastest becouse PG doesn't need any string parsing if prepared....

[rayanAyar]

Again just the first call should be slow. Reopening same query performes loads (hope the slow PG is really able to do that) faster than.

Works slow every time.

Try to keep one query request alive until you close your app. Than the prepared stmts do really make sence.

Practically all querries that I use - created once and reused until close app.

Also working with parameters is prefered....

Almost all querries that I use - working with parameters. And query about I told in first message (which became slower in 7.1.2) - too.

Prepared stmts do have advantages but preparing the execution plans first does take time.

Thats why I always try to use prepared statements. First call is slow but next calls are very fast. It works well in Zeos 7.0.3. But does not work for me in Zeos 7.1.2 - all calls (second, third, ... tenth) takes the same time. And this time is big.

[mdaems]

This is strange behavior indeed. did you try adding a zsqlmonitor to check if only one prepare is executed? Maybe the statement is not preparable for some reason.