ZeosLib + PostgreSQL performance - 7.0 vs 7.1 vs 7.2

[jaromir]

Hi,

Several days ago we switch to 7.1 Zeos Lib at Delphi XE7 (before it was 7.0.3 at Delphi 2010). After upgrade our customers asked us about poor application performace. After some test we found Zeos Lib speed malfunction.

We made simple tests (XE7) at 7.0.6, 7.1 and last SVN 7.2. It was very simple loop with 15000 SELECTS with WHERE condition on 60k records table.
The results was (in seconds):
7.0.6 - 90 s
7.1 - 99 s
7.2 - 125 s

This was not real life probe but in real application with million records database results are very bad. Some complicated query process takes about 6 seconds at 7.0.6 and more than 30 seconds at 7.1. It is impossible to upgrade our customers with 5 times slower database cooperation.

So now we had to switch back to 7.0.6 (with some simple mod for XE7 compatibility).

Could anyone confirm this ? Is not possible to upgrade with speed limits like this. Every never version is just slower than older.

Best regards

[marsupilami]

Hello jaromir,

could you provide your test application and describe your test database? Maybe by doing so we can profile the components to find out where they need much time.
Best regards,

Jan

[jaromir]

Hi,

I prepared simple demo with sql database script.

"Create data" buton insert demo data to database. It tooks some time - about 10 minutes.
"Load data" makes simple SELECTS on inserted data.

Delphi XE7
32 bit
PostgreSQL 9.4
Windows 7 64 bit
Intel i7 2.8 GHz
12 GB RAM

ZEOS 7.0.6 - Load data - 47,674 s
ZEOS 7.1.4 - Load data - 59,327 s

Again - it's not perfect performance test but the difference is visible.
In our real software 7.1 vs 7.0 is about 5 Times slower (a lot of complicated SQL in 1 operation).

Best regards
Jaromir

[miab3]

In our real software 7.1 vs 7.0 is about 5 Times slower (a lot of complicated SQL in 1 operation).

Could you try or something will change for UseMetadata= False in ZConnection?

Michal

[jaromir]

Hi,

Tested in sample app:
Turning UseMetadata to False reduce loading time but version difference is still present:

ZEOS 7.0.6 - Load data - was 47,674 s - is 40,453 s
ZEOS 7.1.4 - Load data - was 59,327 s - is 53,265 s

Regards

[miab3]

@jaromir,

I asked for the check that is 5 Times slower.

Michal

[jaromir]

Ok
I'll try this but this is old stuff and perhaps there can be problems with Metadata turned off.

But my performance tests was with metadata turned on in both cases.
Do You mean that Metadata feature is slower in newer ZEOS versions ?

Regards

[marsupilami]

Hello Jaromir,

I am currently trying to profile into this but maybe there is something else going on in your application. Did you try to use AQtime or GpProf to profile the parts of your application and Zeos that now take longer to execute? Especially the parts that now use 30 seconds instead of 6 seconds?

The problem is - I profiled your testapplication when using Zeos 7.1 and it uses about 39% of the time to wait for PostgreSQL on my computer and the rest of the time is quite evenly spread about on several parts of the Zeos Code. So your real life application seems to hit some hotspot that is not hit by the test application.

My test environment for now: Delphi XE2, Zeos 7.1-patches from SVN, your test application

With best regards,

Jan