Performance on simple reading

Discussion of open issues, suggestions and bugs regarding PgDAC (PostgreSQL Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
uffek
Posts: 37
Joined: Fri 14 Nov 2014 21:49
Location: Roskilde, DK

Performance on simple reading

Post by uffek » Sun 25 Nov 2018 13:12

I am testing basic performance of looping through a table. My code is like this:

Code: Select all

  Con:= TUniConnection.Create(nil);
  Con.ProviderName := 'PostgreSQL';
  Con.Server := '192.168.1.2';
  Con.Username := 'zzzz';
  Con.Password := 'xxxx';
  Con.Database := 'yyyy';
  Con.LoginPrompt := False;
  Con.Open;
  Qry:= TUniQuery.Create(nil);
  Qry.Connection := con;
  Qry.UniDirectional:= true;
  Qry.SQL.Add('select attribute from brazil');
  Qry.open;
  i:= 0;
  while not Qry.eof do
  begin
    Qry.Next;
    i:= i+qry.Fields[0].Asinteger;
  end;
This takes 410 sec (rather big table, the same as in my previous questions).

Doing the same test with C# and Npgsql.dll, it takes 11 sec. Thats 37x faster.

When I do the same comparison test with SQL Server, C# still takes 11 sec, but UniDAC is only 6.5 sec, which is great.

Any reason it is so slow with UniDAC and postgreSQL ?

The solution to use "select sum(attribute)" is not the answer here. The sum is just for making sure all records are read on the client side.

azyk
Devart Team
Posts: 1119
Joined: Fri 11 Apr 2014 11:47
Location: Alpha Centauri A

Re: Performance on simple reading

Post by azyk » Tue 27 Nov 2018 08:53

Please provide us with additional information:
- the exact PostgreSQL version
- a script for creating the brazil table
- a script for filling the brazil table with test data
- duration time of the following line in UniDAC and in C#:

Code: Select all

  Qry.open;
- duration time of the following lines in UniDAC and in C#:

Code: Select all

  while not Qry.eof do
  begin
    Qry.Next;
    i:= i+qry.Fields[0].Asinteger;
  end;

uffek
Posts: 37
Joined: Fri 14 Nov 2014 21:49
Location: Roskilde, DK

Re: Performance on simple reading

Post by uffek » Tue 27 Nov 2018 09:54

PostgreSQL 9.4, win64
The table has a single field Decimal(6,0).
Content is in the range 0 to 65535.
6.5 million records.
The open statement requires 109 msec in C#, 218 msec in UniDAC.
The rest of the time is spent in the loop.

I am getting similar results when I compare between C# and UniDAC, with the same table in an Oracle 11g database. It is only when I use MSSQL as database, that UniDAC performs as fast as C# (actually faster). Maybe that can give a clue as to, what is happening.

azyk
Devart Team
Posts: 1119
Joined: Fri 11 Apr 2014 11:47
Location: Alpha Centauri A

Re: Performance on simple reading

Post by azyk » Tue 27 Nov 2018 15:23

It seems that the main part of time is spent not on data fetching, but on converting field data from the Double type to integer. The attribute field on PostgreSQL side has the type Decimal(6,0). Therefore, PgDAC maps it as TFloatField. And in the code, you get the value as integer.

Please make two changes in your test:

1) Replace the line

Code: Select all

i:= i+qry.Fields[0].Asinteger;
with

Code: Select all

qry.Fields[0].Value;
Tell us the cycle duration time.

2) Return the line

Code: Select all

i:= i+qry.Fields[0].Asinteger;
Change the attribute field type into int. For example:

Code: Select all

CREATE TABLE brazil
(
    attribute int
)
Fill the table with test data and run the test. Tell us the cycle duration time.

uffek
Posts: 37
Joined: Fri 14 Nov 2014 21:49
Location: Roskilde, DK

Re: Performance on simple reading

Post by uffek » Tue 27 Nov 2018 20:42

First I removed the statement with i:= i+ ....
Calculation time is unchanged.

Then I also changed the type from dec(6,0) to int.
Calculation time is still unchanged.

azyk
Devart Team
Posts: 1119
Joined: Fri 11 Apr 2014 11:47
Location: Alpha Centauri A

Re: Performance on simple reading

Post by azyk » Wed 28 Nov 2018 14:39

We reproduced the specified behavior. Indeed, the reason for the long cycle duration in your example is data fetching. To be more exact, due to setting TPgQuery.UniDirectional to True. Since TPgQuery.FetchAll becomes False, almost all the time is spent on requesting PostgreSQL. You can count it. For this, divide the number of table records to TPgQuery.FetchRows. That means that PgDAC requests PostgreSQL 6500000 / 25 = 260000 times to fetch data. More details in our online documentation about

UniDirectional: https://www.devart.com/pgdac/docs/devar ... tional.htm
FetchAll: https://www.devart.com/pgdac/docs/devar ... tchall.htm
FetchRows: https://www.devart.com/pgdac/docs/devar ... chrows.htm

To reduce data fetching duration, in your sample, it is better to fetch as many records as possible
for one request to PostgreSQL. Since the only record in your table occupies very little memory.

The results of testing the sample in our environment:

Qry.UniDirectional:= True;
----------------------------
UniDirectional=True FetchAll=False FetchRows=25 fetched 6500000 records
Query.Open; duration=0:00:00
Query.Next; duration=0:01:37
Query Total duration=0:01:38

Qry.UniDirectional:= True;
Qry.FetchRows := 65535;

----------------------------
UniDirectional=True FetchAll=False FetchRows=65535 fetched 6500000 records
Query.Open; duration=0:00:00
Query.Next; duration=0:00:09
Query Total duration=0:00:09


Qry.UniDirectional:= False;
----------------------------
UniDirectional=False FetchAll=True FetchRows=25 fetched 6500000 records
Query.Open; duration=0:00:03
Query.Next; duration=0:00:03
Query Total duration=0:00:07

uffek
Posts: 37
Joined: Fri 14 Nov 2014 21:49
Location: Roskilde, DK

Re: Performance on simple reading

Post by uffek » Wed 28 Nov 2018 22:25

Thanks, that helped and for Oracle too.
Strangely enough performance drops if I set a higher value of Fetchrows for SQL server.

azyk
Devart Team
Posts: 1119
Joined: Fri 11 Apr 2014 11:47
Location: Alpha Centauri A

Re: Performance on simple reading

Post by azyk » Thu 29 Nov 2018 10:19

Please specify:
- the exact version of SQL Server
- connection string to SQL Server or Delphi code for set up TUniConnection
- script for creating a test table
- testing results as in my previous message

Post Reply