Recent

Author Topic: SELECT * FROM takes forever in Firebird, after windows reboot  (Read 4317 times)

fjabouley

  • Jr. Member
  • **
  • Posts: 81
Re: SELECT * FROM takes forever in Firebird, after windows reboot
« Reply #60 on: January 24, 2020, 11:53:09 am »
@EgonHugeist
Thanks for your answers !
Actually the trick seems to work. (the transactions increases in gstat).
Is it enough to make the sweep perform or have I to invoke it sometimes(I set auto sweep every 10000 transactions)?


In fact I got a function that checks the database every few seconds, inserting connection.starttransaction and commit would work too ?


Code: Pascal  [Select]
  1.  
  2.  
  3. function getlastlogid(typeid: string; extra: string = ''): logid;
  4. var
  5.   jth_misc: TZquery;
  6. begin
  7.   Result.id := -1;
  8.   jth_misc := TZquery.Create(nil);
  9.   with jth_misc do
  10.   begin
  11.     Connection := modules.jconnect;
  12.     Connection.starttransaction;
  13.     SQL.Clear;
  14.     if length(extra) = 0 then
  15.     begin
  16.       SQL.add('SELECT FIRST 1 * FROM LOGS WHERE TYPE=:TYPE ORDER BY ID DESC');
  17.       ParamByName('TYPE').AsString := typeid;
  18.     end
  19.     else
  20.     begin
  21.       SQL.add('SELECT FIRST 1 * FROM LOGS WHERE (TYPE=:TYPE) OR (TYPE=:TYPE2) ORDER BY ID DESC');
  22.       ParamByName('TYPE').AsString := typeid;
  23.       ParamByName('TYPE2').AsString := extra;
  24.     end;
  25.     Open;
  26.     if not EOF then
  27.       with Result do
  28.       begin
  29.         id := FieldByName('ID').AsInteger;
  30.         type_id := FieldByName('TYPE').AsString;
  31.         date_log := FieldByName('DATE_LOG').AsDateTime;
  32.         heure_log := FieldByName('HEURE_LOG').AsDateTime;
  33.         valeur := FieldByName('VALEUR').AsString;
  34.         texte := FieldByName('TEXTE').AsString;
  35.       end;
  36.     Close;
  37.     Connection.commit;
  38.     Free;
  39.   end;
  40. end;      
  41.  


Best regards
« Last Edit: January 24, 2020, 11:57:03 am by fjabouley »

devEric69

  • Full Member
  • ***
  • Posts: 247
Re: SELECT * FROM takes forever in Firebird, after windows reboot
« Reply #61 on: January 24, 2020, 02:42:31 pm »
Hi @EgonHugeist
Is there any change/planning to have active connection and not started transaction in ZeosDbo? This is old post in Synopse/mORMot forum about bad transaction handling in mORMot app. I don't like to have started transaction on server if nobody is connected to my mORMot server.
https://synopse.info/forum/viewtopic.php?pid=14105#p14105

@tttomas: thank you for the link above. Very, very informative your trick for retrieving the essential of transactional informations, summarized in your xls sheet. And it's a Lazarus \ Free Pascal topic, so not at all off-topic. 

@fjabouley: I'm finally glad your Firebird\Zeos project is going ahead: I know it's been hard work :) .
Nevertheless, I post here the message sent at your request, because the timer idea could be to benefit other people, and it is already integrated in the IBX components. It may be of interest to those who will make this choice of components:

[message /on]>>>>>
Quote
Are IBX components easy to use,
 Best regards

I can't answer yes or no.
What I can say is that if I use Firebird, it's because there are the IBX components.
Originally, I had an OMT development training on 3FN SQL databases. I also had an IBX training: via the old Delphi developer's guides (I have the paper ones from 1 to 3), and also by a freelance trainer. And the discovery of the TIB{X}Dataset component "resonated" fully with "OMT over SQL databases". Simply put, this component perfectly fits the "active record" pattern in the different client-server configurations.

Quote
or at least easy enough to switch from zeos?

I don't know how you coded the application: if you created a datamodule as a business object, i.e. one datamodule per table, I can already answer that this is possible.
But the properties of TIB{X}Dataset are more numerous than those of a similar Zeos component (for example, you have to define in it 5 queries managing the record (SELECT, UPDATE, INSERT, DELETE and REFRESH), it has an integrated timer to say what to do when nothing happens transactionally anymore, it can tell whether it takes into account simple auto-commit or commit-retaining, caching (writing to a client-side transaction log, which can then be "ApplyUpdates", with resolving possibilities). So I can't answer yes, it's easy, since the *.dfm will be quite different.

On the other hand, it's worth installing the IBX package, first of all because there's an administration application included, which compiles and can connect to any database. It has a tab where it is possible to monitor connections with an auto-refresh option. It uses, and queries on the database via the "$my..." tables, ..., and calling SQL server services via a component encapsulating the server APIs.
In addition there are sample applications, which is instructive. Concerning the IBX suite, there are components that I find "smart", like a component to pass differential scripts in series to make an automatic update of the database (an upgrade), a SQL parser to easily modify a query, etc..

As far as your "old" transaction problems are concerned, I can only advise you to fix them using the "watchdog" idea:
- Basically, it consists of opening a timer on the opening of a record (which opens a dataset, I guess);
- the watchdog timer goes off on an OnIdle event and counts down the time;
- after x minutes (it can be in the *.ini file, so configurable by role), let's say 5, if nothing happened, the idea is to call a serialized code (the same for all your records) which displays a message like "Save your modifications and refresh the data modified by your colleagues...": it checks the (?) oTransaction components.InTransaction; it calls COMMIT; if it fails, it calls ROLLBACK; it must be handled silently (capturing a possible logged exception); it writes in a record the primary keys of the current bookmarks; it closes the detail datasets, then their master dataset; it closes the connection; it re-opens the connection; it opens a transaction, the detail dataset and then its master within that transaction; it bookmarks the current records again.

I really have the impression that there might be a "disable index" option somewhere in your backup: I know it exists, but ask your problem on the Yahoo-firebird mailing list: it's the Firebird forum.
<<<<<[message /off]

Where did the idea for the timer, the so-called "watchdog" come from? I'll repeat myself, but in the HTML page posted here ( https://forum.lazarus.freepascal.org/index.php/topic,48159.msg346926.html#msg346926 ),
there is in it, an explanation on the "Study of the "watchdog making regular rounds" (in french:
"Étude du chien de garde faisant régulièrement sa ronde"): it's just a TTimer integrated in the TIB{X}Transaction's property) using its associated property, TIB{X}Transaction.DefaultAction".
This integrated timer allows an automatic Commit or Rollback to each OnTimer event.
But, the timer is already cleverly integrated in the IB{X} transaction component ;) .

use: Linux 64 bits (Ubuntu 18.04 LTS).
Lazarus version: 2.0.4 (svn revision: 62502M) compiled with fpc 3.0.4 - fpDebug \ Dwarf3.

fjabouley

  • Jr. Member
  • **
  • Posts: 81
Re: SELECT * FROM takes forever in Firebird, after windows reboot
« Reply #62 on: January 24, 2020, 03:17:39 pm »
I just wanted to thank you all again, I'm so glad you all helped me to better understand what was happening and to be less ignorant with firebird and zeos' behaviours, and I wish I could be as helpful as you were for me (but I can barely help because I'm a noob :) ) This forum is really cool !

fjabouley

  • Jr. Member
  • **
  • Posts: 81
Re: SELECT * FROM takes forever in Firebird, after windows reboot
« Reply #63 on: January 30, 2020, 01:53:52 pm »
Just to give some news, the timer trick definitely seems to work, I tried to kill firebird server, on a few days running database (all clients with the timer) and the request on a freshly started firebird server is OK. I know it's only a workaround and that I'll have to, in long term, do something to make better transaction management, but once again thank you all for your replies ! Best regards