1

i'm using Delphi-7 and zeos 6.6.4 (and just now upgrading to zeos 7.1.4) upon bigger rows of a table, things become very very slow on query. Not so much rows either (on salesdetail my table got around 200k rows). after I backup and clean the table, it's very fast.

I'm pretty sure the problem is in ZQuery1.Open. I copy paste the "search/select query" and run it on mysql and it only took 1sec or so. -> So I'm sure it's not my query's fault. But if i execute it on Delphi7, it took around 15secs.

I did some tracing, and i found out the problem is on ZQuery1.Open, after pass this code, things run at normal speed.

tried upgraded zeos to newest version, but problem persist.

any solutions?

thank you.

EDIT: I read similar case on this:

"guyvdb wrote: Hello, we did also some tests and came to the same conclusion. 'qry.open' is very slow with ZEOS. Speed is significantly better with 'ADO' and 'SQLDirect' Disabling the debug define in zeos.inc doesn't change anything."

http://zeoslib.sourceforge.net/viewtopic.php?t=120

i'm pretty much prefer keep using zeos, not changed to ADOquery,etc because the app is finished already, the problem discovered months after used, after bigger data on table.and it will be much work to change all the zquery into something else. thank you

Stevanus Lianto
  • 13
  • 5
  • Is your query bound to any data-aware controls? If so, these may react to data events. So the 15 seconds you measured for the `Open` might include e.g. a grid loading the 200000 records and not just the querying of the data. – nil Aug 31 '17 at 14:51
  • should be not, because in this case my zquery1 created privately only to execute this query, after zquery created, zquery.sql filled, then zquery.open. No other uses/grid loading, etc Same query works fast under 1sec on mysql. I found similar case, pls refer to my Edited OP – Stevanus Lianto Aug 31 '17 at 15:08
  • Do you really need to fetch that many rows? Any blob fields in the table that could have been fecthed along with another data? Do you have any indices defined in the component? The difference may well come from the fact that ADO fetched only live window (200k rows in 1 second is suspiciously fast), not all the tuples (as they may be fetched later when scrolling the dataset) whilst Zeos fetched them all (15 seconds sounds quite much though). – Victoria Aug 31 '17 at 15:50
  • Hi Victoria, No.. 200k is total rows on salesdetail table, but I fetch only 2-10 rows on each query. My query is simply like: select sales and joint with salesdetail where id=xxx. With less than 100k rows total on the salesdetail table, speed is no problem with zeos, but once my table grow bigger (around 200k++), it's also getting slower. – Stevanus Lianto Aug 31 '17 at 15:54
  • What do you mean by _"bigger rows"_? Can you reproduce the same without join (if you run `WHERE ID IN (...)`)? Are you running your server on local machine? Cannot reproduce so far (Zeos 7.1.4).. – Victoria Aug 31 '17 at 16:35
  • How much memory is on Delphi ? Zeos ? Please add the 'query', SHOW CREATE TABLE table_name(s), SHOW INDEX FROM table_name(s) to your question for documentation. – Wilson Hauck Aug 31 '17 at 17:33
  • I will add more details asap, my laptop is in the office.. But as I said, the query itself is okay, since it run fast if test in on mysqlyog, on localserver. 'bigger rows' i mean if data is more than 100k+ on the table. I did simple test, I put showmessage 1,2,3,4,5 on delphi. '1' after I fill the zquery.sql,text then '2' right before zquery.open and '3' right after zquery.open, 4 and 5 later. showmessage 1,and 2 coming fast.. then 3 need 15sec to show up, then 4,5, also fast..So it's kinda stuck at zquery.open. – Stevanus Lianto Aug 31 '17 at 18:03
  • Well, I did a quick test with more than 100k tuples with no performance problem. I would immediately profile an application suffering something like this to find a bottleneck. – Victoria Aug 31 '17 at 23:54
  • I tried to create a test application, simple button to print 'my print query' to virtual printer/cute pdf. I found that with low recods on my salesdetail tablem it print very fast, under 1secs or so. But with 200k records, it took 15secs. then I tried switch printer to TMU-220 printer/raw print. It took even longer, 30secs or so. It send the spooling jobs but then stuck for a long time, before it continues to print. With low records on salesdetail table, i never found this issues.( i reset db twice a year. this slow problem appears at months 8++, i want to reset at least once a year). – Stevanus Lianto Oct 14 '17 at 13:32

1 Answers1

0

Setting to TZConnection.SQLHourGlass := false

András Babar
  • 1
  • 1