• This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn more.

Count Records in each table of a Database

Smud

New Member
#1
Saw this question recently, this is better than "for each ...: i = i + 1. end. display i." type of code;

Code:
CREATE WIDGET-POOL.
DEF VAR h_predicate AS CHAR     NO-UNDO.
DEF VAR h_qry       AS HANDLE   NO-UNDO.
DEF VAR h_buffer    AS HANDLE   NO-UNDO.
FOR EACH _file NO-LOCK:
    if _file._file-name begins "_" then next.
    if _file._file-name begins "sys" then next.
    h_predicate = "PRESELECT EACH " + _file._file-name + " NO-LOCK".
    CREATE BUFFER h_buffer FOR TABLE _file._file-name .
    CREATE QUERY h_qry.
    h_qry:SET-BUFFERS( h_buffer ).
    h_qry:QUERY-PREPARE( h_predicate ).
    h_qry:QUERY-OPEN().
    DISPLAY _file._file-name h_qry:NUM-RESULTS.
    DELETE OBJECT h_qry.
    DELETE OBJECT h_buffer.
END.
Try it in the dojo
 

Cringer

ProgressTalk.com Moderator
Staff member
#2
Probably makes no difference but I would h_qry:query-close before deleting.
Also, query-prepare returns a logical as to the success. So you can
Code:
if not h_qry:QUERY-PREPARE( h_predicate ) then next.
Or something along those lines (you still have to delete the query object of course) to avoid the query open and subsequent lines throwing runtime errors if the prepare string fails.
 

Rob Fitzpatrick

ProgressTalk.com Sponsor
#3
However you count records from ABL, it is slow for large tables. In my testing (several years ago) it was faster to code-gen and then execute a SQL script to count the records in each table.

Another alternative is table analysis: proutil db -C tabanalys > db.tba. Since it can be run on a per-area basis, it can be useful to count the records in a particularly large table that is in its own area. It might also just be faster to run it on the whole database and then parse its output to get the record counts. The CSV output option in 11.4+ makes this much easier.
 

Smud

New Member
#4
Probably makes no difference but I would h_qry:query-close before deleting.
Also, query-prepare returns a logical as to the success. So you can
Code:
if not h_qry:QUERY-PREPARE( h_predicate ) then next.
Or something along those lines (you still have to delete the query object of course) to avoid the query open and subsequent lines throwing runtime errors if the prepare string fails.
Yep that works! I have a few large conversions to run over the next few months and it will help knowing how many rows I'm dealing with etc.

I'll try the proutil suggestion next week as well - thanks Rob - that will help in planning.
 

Smud

New Member
#6
slightly restructured, its not too bad for speed;

Code:
CREATE WIDGET-POOL.
DEF VAR h_predicate AS CHAR     NO-UNDO.
DEF VAR h_qry       AS HANDLE   NO-UNDO.
DEF VAR h_buffer    AS HANDLE   NO-UNDO.

/* predicate with working database */
FOR EACH _file NO-LOCK:
    if _file._file-name begins "_" then next.
    if _file._file-name begins "sys" then next.
    h_predicate = "PRESELECT EACH " + _file._file-name + " NO-LOCK".
    CREATE BUFFER h_buffer FOR TABLE _file._file-name.
    CREATE QUERY h_qry.
    h_qry:SET-BUFFERS( h_buffer ).
    if h_qry:QUERY-PREPARE( h_predicate ) then do:
      h_qry:QUERY-OPEN().
      display _file._file-name h_qry:NUM-RESULTS.
    end.
    h_qry:query-close.
    DELETE OBJECT h_qry.
    DELETE OBJECT h_buffer.
END.