Slow query Zeos 8 and Oracle lazarus / 16-Databases ---------------------------------------------------------------------- babycode · 2024-10-07 12:09 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 · 2024-10-08 17:25 I have absolutely no idea what this could be, but the ZEOS support forum is usually a good source https://zeoslib.sourceforge.io/ ---------------------------------------------------------------------- Hansvb · 2024-10-11 08:31 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][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i 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 · 2024-10-11 09:00 Quote from: babycode on October 07, 2024, 02:09:23 pmI'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 · 2024-10-11 12:42 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 · 2024-10-11 13:05 Quote from: babycode on October 11, 2024, 02:42:41 pmyes, 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 · 2024-10-11 13:49 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][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i procedure TForm1.Button1Click(Sender: TObject);var  beginTime, endTime: TDateTime;  elapsedTime: Double;begin  if ZConnection1.Connected then    ZConnection1.Connected := False;   if ZQuery1.Active then    ZQUery1.Close;   ZConnection1.UseMetadata := chkMetadata.Checked;   beginTime := Now;  ZConnection1.Connected := True;  endTime := Now;   elapsedTime := MillisecondsBetween(beginTime, endTime);   mStatus.Line s.Add('-----------------------------------------------------------');  mStatus.Lines.Add('ZeosLib v8:');  mStatus.Lines.Add('---------------- -------------------------------------------');  mStatus.Lines.Add('Connection Time: ');  mStatus.Lines.Add('---------- -------------------------------------------------');  mStatus.Lines.Add('Start connection: ' + FormatDateTime('hh:mm:ss', beginTime));  mStatus.Lines.Add('End connection: ' + FormatDateTime('hh:mm:ss', endTime));  mStatus.Lines.Add('Elapsed time in connection: ' + FormatFloat('0.00#.##', elapsedTime) + ' milliseconds.');   beginTime := Now;  ZQuery1.Open;  endTime := Now;   elapsedTime := MillisecondsBetween(beginTime, endTime);  mStatus.Lines .Add('-----------------------------------------------------------');  mStatus.Lines.Add('Query Time: ');  mStatus.Lines.Add('--------------- --------------------------------------------');  mStatus.Lines.Add('Start query: ' + FormatDateTime('hh:mm:ss', beginTime));  mStatus.Lines.Add('End query: ' + FormatDateTime('hh:mm:ss', endTime));  mStatus.Lines.Add('Elapsed time in query: ' + FormatFloat('0.00#.##', elapsedTime) + ' milliseconds.');end; procedure TForm1.Button2Click(Sender: TObject);var  beginTime, endTime: TDateTime;  elapsedTime: Double;begin  if OracleConnection1.Connected then    OracleConnection1.Connected := False;   if SQLQuery1.Active then    SQLQuery1.Close;   beginTime := Now;  OracleConnection1.Connected := True;  endTime := Now;   elapsedTime := MillisecondsBetween(beginTime, endTime);   mStatus.Lines.Add('--------------------------------------- --------------------');  mStatus.Lines.Add('SQLDB Lib:');  mStatus.Lin es.Add('----------------------------------------------------------- ');  mStatus.Lines.Add('Connection Time: ');  mStatus.Lines.Add('----- ------------------------------------------------------');  mStatus.Lines.Add('Start connection: ' + FormatDateTime('hh:mm:ss', beginTime));  mStatus.Lines.Add('End connection: ' + FormatDateTime('hh:mm:ss', endTime));  mStatus.Lines.Add('Elapsed time in connection: ' + FormatFloat('0.00#.##', elapsedTime) + ' milliseconds.');   beginTime := Now;  SQLQuery1.Open;  endTime := Now;   elapsedTime := MillisecondsBetween(beginTime, endTime);   mStat us.Lines.Add('-------------------------------------------------------- ---');  mStatus.Lines.Add('Query Time: ');  mStatus.Lines.Add('------- ----------------------------------------------------');  mStatus.Lines.Add('Start query: ' + FormatDateTime('hh:mm:ss', beginTime));  mStatus.Lines.Add('End query: ' + FormatDateTime('hh:mm:ss', endTime));  mStatus.Lines.Add('Elapsed time in query: ' + FormatFloat('0.00#.##', elapsedTime) + ' milliseconds.');end; Quote----------------------------------------------------------- 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 · 2024-10-11 14:02 Quote from: babycode on October 11, 2024, 03:49:06 pmCode: [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 · 2024-10-11 14:05 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 · 2024-10-11 14:14 Quote from: babycode on October 11, 2024, 04:05:54 pmYes, 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 · 2024-10-11 15:06 I added Query.Last to both queries, with no limitations on records returned. Below is the memo output on VPN and LOCAL VPN Quote----------------------------------------------------------- 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 Quote----------------------------------------------------------- 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 · 2024-10-11 15:18 Quote from: babycode on October 11, 2024, 05:06:28 pmI 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 · 2024-10-11 17:18 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 · 2024-10-11 17:24 Quote from: babycode on October 07, 2024, 02:09:23 pmI'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 · 2024-10-11 17:30 Thanks for the feedback Thaddy I'm not trying hard to accomplish this. ---------------------------------------------------------------------- rvk · 2024-10-11 17:30 Quote from: babycode on October 11, 2024, 07:18:43 pmA 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). And ZeosLib takes 37 seconds to retrieve those 3 records? Or did you not test both with the same dataset?  Otherwise you can't compare them. (Still think you are testing SQLdb with the wrong thing but you'll discover that when you have migrated ) ---------------------------------------------------------------------- babycode · 2024-10-11 19:58 RVK The same query in both, the same SQL, the same DB, the same connection configuration without any extra parameters. Practically just changing the library (Zeos/SQLDB). I even did something more generic by returning all costumers (select * from costumers) with no line limit or any other limitation. The numbers obtained (time) were unfortunately equivalent. ---------------------------------------------------------------------- rvk · 2024-10-11 20:33 Yeah... if you get 37 seconds with Zeos for just 3 records then there is definitely something wrong, regardless over what kind of connection. (Glad I'm not using ZeosLib  ) ---------------------------------------------------------------------- babycode · 2024-10-12 19:40 Are you using SQLDB or something else? ---------------------------------------------------------------------- rvk · 2024-10-12 20:36 Quote from: babycode on October 12, 2024, 09:40:23 pmAre you using SQLDB or something else? I normally use the Firebird database so I'm using IBX. (But my main program is in Delphi/IBX. On Lazarus I normally dabble with SQLdb and IBX.) Before version 8 ZeosLib didn't support multiple transactions per connection. So I never considered ZeosLib just because of that. Since version 8 you can use multiple transactions per connection on drivers that support it, but that's only Firebird. Oracle itself doesn't support multiple transaction over one connection and if you do use more in ZeosLib, for each transaction, a separate connection is opened !!