Recent

Author Topic: What better Zeos or native library to PostgreSQL  (Read 1806 times)

lublinkermi

  • New Member
  • *
  • Posts: 46
What better Zeos or native library to PostgreSQL
« on: December 01, 2017, 08:46:30 pm »
Hi!
Which library is faster in accessing PostgreSQL data, as in the subject? Zeos is simpler to use, but probably not suitable for large bases. Master database have about 1 000 000 record's,  linked database have about 15 000 000 record's.
As I observed, Zeos loads the entire database and only then selects the dependent records. With small bases it is correct but at large it is very slow.
Or maybe there are some options that cause acceleration, because, after all, zeos is much more convenient?

marcov

  • Global Moderator
  • Hero Member
  • *****
  • Posts: 7796
Re: What better Zeos or native library to PostgreSQL
« Reply #1 on: December 01, 2017, 08:50:03 pm »
Afaik zeos is SQL based, but yes there is filtering on several levels.

The default is often "select * from table" and then filtering is done clientside, but you can easily add your own SQL parametrization to limit the base set serverside.


taazz

  • Hero Member
  • *****
  • Posts: 5363
Re: What better Zeos or native library to PostgreSQL
« Reply #2 on: December 01, 2017, 08:53:37 pm »
Hi!
Which library is faster in accessing PostgreSQL data, as in the subject? Zeos is simpler to use, but probably not suitable for large bases.
Zeos as well as SQLDB uses the native library to access the database server and retrieve data there shouldn't be any difference in speed.
Master database have about 1 000 000 record's,  linked database have about 15 000 000 record's.
As long as you do not try to retrieve them all at once you shouldn't have a problem regardless of data size.
As I observed, Zeos loads the entire database and only then selects the dependent records. With small bases it is correct but at large it is very slow.
You observered wrong. Provide a small sample that demonstrates the observed behaviour, we will try to propose changes.
Or maybe there are some options that cause acceleration, because, after all, zeos is much more convenient?
No way to know what might be wrong, not with out a sample.
Good judgement is the result of experience … Experience is the result of bad judgement.

OS : Windows 7 64 bit
Laz: Lazarus 1.4.4 FPC 2.6.4 i386-win32-win32/win64

zeljko

  • Hero Member
  • *****
  • Posts: 1095
    • http://wiki.lazarus.freepascal.org/User:Zeljan
Re: What better Zeos or native library to PostgreSQL
« Reply #3 on: December 01, 2017, 10:14:46 pm »
Hi!
Which library is faster in accessing PostgreSQL data, as in the subject? Zeos is simpler to use, but probably not suitable for large bases. Master database have about 1 000 000 record's,  linked database have about 15 000 000 record's.
As I observed, Zeos loads the entire database and only then selects the dependent records. With small bases it is correct but at large it is very slow.
Or maybe there are some options that cause acceleration, because, after all, zeos is much more convenient?

I'm using ZEOS for databases with ascii dump of > 50GB (so much more than 15 000 000 records I guess). If you load complete database into datasets then you're doing someting wrong. On the other hand, if you're talking about metadata, you can disable it under Zeos if you're on version >= 7.2, just set TZConnection.UseMetadata := False; . There's also question what kind of TransactionIsolationLevel are you using ? AutoCommit is false ?