Slow query Zeos 8 and Oracle

[babycode]

I'm developing some modules using Lazarus and ZeosLIB ( with connection to the Oracle 19 database. The database is networked where the branches use VPNs to connect to it. In the branch where the bank is hosted, the connection is fast, but in the other VPNs, queries take time in the modules that use ZeosLib. I did a test using SQLdb and the query didn't take long. Are there any settings that can be used to optimize this model?

[CharlyTango]

I have absolutely no idea what this could be, but the ZEOS support forum is usually a good source

https://zeoslib.sourceforge.io/

[Hansvb]

Hi,

It is difficult to measure because Oracle caches your queries of course, but I see little difference in speed between Zeos and default SQL components. (Zeos seems a bit slower). I first disabled use metadata at zeos.

Code: Pascal  [Select][+][-]

1. ZConnection1.UseMetadata:= False;

That does seem to make a difference.
Tested on Oracle 19


Edit...
I tried a query which took a few seconds. Not the query in project.zip

[rvk]

I'm developing some modules using Lazarus and ZeosLIB ( with connection to the Oracle 19 database. The database is networked where the branches use VPNs to connect to it. In the branch where the bank is hosted, the connection is fast, but in the other VPNs, queries take time in the modules that use ZeosLib. I did a test using SQLdb and the query didn't take long. Are there any settings that can be used to optimize this model?

So, you are effectively running the database connection over the internet (although over VPN) ???

What is the ping (latency) of those connections?
If they are higher than 2 or 3ms then those database connections will be slow.

Database protocols are usually very (very very very) chatty. For every packet of records send over the connection, a lot of protocol packets are send too. And if your ping is high, that's going to add up. Even if your download/upload speed is very high, the ping is the one that counts and adds up.

Normally you would add a middle man in this case (multitier architecture). Software which runs locally which provides the data in another format and is much faster than normal database protocols, even over slow connections.

I'm not sure why your tests with SQLdb seem faster. Did you use the same data in the same application?

[babycode]

rvk

yes, what I did was take one of the modules that used zeos and migrated it to sqldb. Practically all queries are created at runtime, only the connection and transaction I leave in the data module. Same queries, I just changed ZQuery to SQLQuery and other components. Same connection configuration, same OCI.dll.

[rvk]

yes, what I did was take one of the modules that used zeos and migrated it to sqldb.

Strange that that fixes your slowness.
Because my experience (and subsequent investigations) shows that using any database over an internet connection (vpn or not) is slow as hell.

This can be mitigated by using some larger buffers etc. but updates will still be record per record.

(But I have never used Zeos myself because of its limitations.)

[babycode]

rvk

I wrote a small program to perform connection tests and try to measure the time. Observe the form and the results obtained in TMemo in the printout. The same query was passed directly to the TQuery component of both libraries The test was set up in a crude way, I don't know if it could be something considerable.

Code: Pascal  [Select][+][-]

1. procedure TForm1.Button1Click(Sender: TObject);
2. var
3.   beginTime, endTime: TDateTime;
4.   elapsedTime: Double;
5. begin
6.   if ZConnection1.Connected then
7.     ZConnection1.Connected := False;
8.  
9.   if ZQuery1.Active then
10.     ZQUery1.Close;
11.  
12.   ZConnection1.UseMetadata := chkMetadata.Checked;
13.  
14.   beginTime := Now;
15.   ZConnection1.Connected := True;
16.   endTime := Now;
17.  
18.   elapsedTime := MillisecondsBetween(beginTime, endTime);
19.  
20.   mStatus.Lines.Add('-----------------------------------------------------------');
21.   mStatus.Lines.Add('ZeosLib v8:');
22.   mStatus.Lines.Add('-----------------------------------------------------------');
23.   mStatus.Lines.Add('Connection Time: ');
24.   mStatus.Lines.Add('-----------------------------------------------------------');
25.   mStatus.Lines.Add('Start connection: ' + FormatDateTime('hh:mm:ss', beginTime));
26.   mStatus.Lines.Add('End connection: ' + FormatDateTime('hh:mm:ss', endTime));
27.   mStatus.Lines.Add('Elapsed time in connection: ' + FormatFloat('0.00#.##', elapsedTime) + ' milliseconds.');
28.  
29.   beginTime := Now;
30.   ZQuery1.Open;
31.   endTime := Now;
32.  
33.   elapsedTime := MillisecondsBetween(beginTime, endTime);
34.   mStatus.Lines.Add('-----------------------------------------------------------');
35.   mStatus.Lines.Add('Query Time: ');
36.   mStatus.Lines.Add('-----------------------------------------------------------');
37.   mStatus.Lines.Add('Start query: ' + FormatDateTime('hh:mm:ss', beginTime));
38.   mStatus.Lines.Add('End query: ' + FormatDateTime('hh:mm:ss', endTime));
39.   mStatus.Lines.Add('Elapsed time in query: ' + FormatFloat('0.00#.##', elapsedTime) + ' milliseconds.');
40. end;
41.  
42. procedure TForm1.Button2Click(Sender: TObject);
43. var
44.   beginTime, endTime: TDateTime;
45.   elapsedTime: Double;
46. begin
47.   if OracleConnection1.Connected then
48.     OracleConnection1.Connected := False;
49.  
50.   if SQLQuery1.Active then
51.     SQLQuery1.Close;
52.  
53.   beginTime := Now;
54.   OracleConnection1.Connected := True;
55.   endTime := Now;
56.  
57.   elapsedTime := MillisecondsBetween(beginTime, endTime);
58.  
59.   mStatus.Lines.Add('-----------------------------------------------------------');
60.   mStatus.Lines.Add('SQLDB Lib:');
61.   mStatus.Lines.Add('-----------------------------------------------------------');
62.   mStatus.Lines.Add('Connection Time: ');
63.   mStatus.Lines.Add('-----------------------------------------------------------');
64.   mStatus.Lines.Add('Start connection: ' + FormatDateTime('hh:mm:ss', beginTime));
65.   mStatus.Lines.Add('End connection: ' + FormatDateTime('hh:mm:ss', endTime));
66.   mStatus.Lines.Add('Elapsed time in connection: ' + FormatFloat('0.00#.##', elapsedTime) + ' milliseconds.');
67.  
68.   beginTime := Now;
69.   SQLQuery1.Open;
70.   endTime := Now;
71.  
72.   elapsedTime := MillisecondsBetween(beginTime, endTime);
73.  
74.   mStatus.Lines.Add('-----------------------------------------------------------');
75.   mStatus.Lines.Add('Query Time: ');
76.   mStatus.Lines.Add('-----------------------------------------------------------');
77.   mStatus.Lines.Add('Start query: ' + FormatDateTime('hh:mm:ss', beginTime));
78.   mStatus.Lines.Add('End query: ' + FormatDateTime('hh:mm:ss', endTime));
79.   mStatus.Lines.Add('Elapsed time in query: ' + FormatFloat('0.00#.##', elapsedTime) + ' milliseconds.');
80. end;

-----------------------------------------------------------
ZeosLib v8: (Metadata := False)
-----------------------------------------------------------
Connection Time:
-----------------------------------------------------------
Start connection: 10:38:41
End connection: 10:38:41
Elapsed time in connection: 395,00 milliseconds.
-----------------------------------------------------------
Query Time:
-----------------------------------------------------------
Start query: 10:38:41
End query: 10:39:19
Elapsed time in query: 37495,00 milliseconds.

ZeosLib v8:
-----------------------------------------------------------
Connection Time:  (Metadata := True)
-----------------------------------------------------------
Start connection: 10:40:40
End connection: 10:40:40
Elapsed time in connection: 239,00 milliseconds.
-----------------------------------------------------------
Query Time:
-----------------------------------------------------------
Start query: 10:40:40
End query: 10:41:18
Elapsed time in query: 37559,00 milliseconds.

-----------------------------------------------------------
SQLDB Lib:
-----------------------------------------------------------
Connection Time:
-----------------------------------------------------------
Start connection: 10:39:39
End connection: 10:39:39
Elapsed time in connection: 159,00 milliseconds.
-----------------------------------------------------------
Query Time:
-----------------------------------------------------------
Start query: 10:39:39
End query: 10:39:39
Elapsed time in query: 83,00 milliseconds.

[rvk]

Code: [Select]

  SQLQuery1.Open;


That 83 milliseconds really sounds fishy.
Does it really get some data?

My guess is that ZeosLib gets the first 5 records while the SQLdb get nothing.

Try adding a reading of the first record.
Or better add Query.Last after the Query.Open.

Or attach a TDBGrid to it (which will make sure a number of records are actually retrieved for view).

[babycode]

rvk

Yes, they both use the same query and I put dbgrid to view the returned data. They returned the same data already expected by the informed query. I limited both to return 10 rows and the time is the same.

[rvk]

Yes, they both use the same query and I put dbgrid to view the returned data. They returned the same data already expected by the informed query. I limited both to return 10 rows and the time is the same.

Ok, that is strange. Especially because I find 83ms really fast.
I take it, both connection are over that VPN?

I have a feeling the SQLdb connects locally.

How fast are the numbers if you run this on the local network (not over VPN)?
(If SQLdb has the same 83ms then this is surely a programming error because that couldn't happen.)

Or TZQuery is set to retrieves all records while SQLQuery does it on demand.
(Adding a Query.Last will show that)

[babycode]

I added Query.Last to both queries, with no limitations on records returned. Below is the memo output on VPN and LOCAL

VPN ----------------------------------------------------------- ZeosLib v8: ----------------------------------------------------------- Connection Time: ----------------------------------------------------------- Start connection: 11:55:35 End connection: 11:55:35 Elapsed time in connection: 338,00 milliseconds. ----------------------------------------------------------- Query Time: ----------------------------------------------------------- Start query: 11:55:35 End query: 11:56:13 Elapsed time in query: 37994,00 milliseconds. ----------------------------------------------------------- SQLDB Lib: ----------------------------------------------------------- Connection Time: ----------------------------------------------------------- Start connection: 11:56:16 End connection: 11:56:16 Elapsed time in connection: 181,00 milliseconds. ----------------------------------------------------------- Query Time: ----------------------------------------------------------- Start query: 11:56:16 End query: 11:56:16 Elapsed time in query: 86,00 milliseconds.

LOCAL ----------------------------------------------------------- ZeosLib v8: ----------------------------------------------------------- Connection Time: ----------------------------------------------------------- Start connection: 11:57:26 End connection: 11:57:26 Elapsed time in connection: 121,00 milliseconds. ----------------------------------------------------------- Query Time: ----------------------------------------------------------- Start query: 11:57:26 End query: 11:57:28 Elapsed time in query: 1250,00 milliseconds. ----------------------------------------------------------- SQLDB Lib: ----------------------------------------------------------- Connection Time: ----------------------------------------------------------- Start connection: 11:57:34 End connection: 11:57:34 Elapsed time in connection: 39,00 milliseconds. ----------------------------------------------------------- Query Time: ----------------------------------------------------------- Start query: 11:57:34 End query: 11:57:34 Elapsed time in query: 15,00 milliseconds.

[rvk]

I added Query.Last to both queries, with no limitations on records returned. Below is the memo output on VPN and LOCAL

Looking at that 37 seconds over VPN for ZeosLib... that seems the same if you don't use Query.Last.

So, the TZQuery probably retrieves all records with and without that Query.Last.
(Unless you have 10 records in your database. How many records does your dataset have?)

Still doesn't explain why SQLdb is so much faster.

Maybe it's time for the actual test project which someone with access to an Oracle server can try.
(Or maybe someone can spot an error in your project1.zip without having access to a server)

[babycode]

A query I used as an example performs some joins between tables to compile information from customers' open invoices. It only returns 3 records because I am using a specific client as an example just to test this query (Using Query.Last). I did another test using a query that returns everything (select * from costumers) and the result was equivalent in both scenarios (VPN/LOCAL). If anyone knows of any settings or parameters needed in ZeosLib to get around this scenario or has a similar use case but doesn't have the same problem, please leave your experience here. I will currently be migrating the modules to SQLDB. Thank you everyone for your contributions.

[Thaddy]

I'm developing some modules using Lazarus and ZeosLIB ( with connection to the Oracle 19 database. The database is networked where the branches use VPNs to connect to it. In the branch where the bank is hosted, the connection is fast, but in the other VPNs, queries take time in the modules that use ZeosLib. I did a test using SQLdb and the query didn't take long. Are there any settings that can be used to optimize this model?

That used to be my work and usually we wrote stored procedures, both for safety - you mention a bank - and speed.
So off-load as much work as you can to Oracle and don't allow any queries other than with defined parameters.

[babycode]

Thanks for the feedback Thaddy I'm not trying hard to accomplish this.