Page 1 of 1
PostgreSQL performance - CAPIPreparedStatement
Posted: 11.01.2014, 10:59
by 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?
Re: PostgreSQL performance - CAPIPreparedStatement
Posted: 12.01.2014, 00:06
by miab3
@rayanAyar,
Could you give an example?
Michal
Re: PostgreSQL performance - CAPIPreparedStatement
Posted: 12.01.2014, 14:46
by 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....
Re: PostgreSQL performance - CAPIPreparedStatement
Posted: 12.01.2014, 16:24
by rayanAyar
EgonHugeist wrote:
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.
EgonHugeist wrote:
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.
EgonHugeist wrote:
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.
EgonHugeist wrote:
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.
Re: PostgreSQL performance - CAPIPreparedStatement
Posted: 12.01.2014, 22:12
by 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.