Num records in table

lord_icon

Member
Windows,
OpenEdge 10.1

Is there a function in the 4GL to return the number of records in a table, to save doing the manual for each table , increment integer?
TIA
 
You can do it in SQL in the editor:

SELECT COUNT(*) FROM table-A

for immediate display.


if you want it in a variable:

DEF VAR i AS INTEGER NO-UNDO.

SELECT COUNT(*) INTO i FROM table-A .

MESSAGE i VIEW-AS ALERT-BOX.



in 4GL, you can use ACCUMULATE, I think, but I've never used it.
 
ACCUMULATE example from KB P5496

http://tinyurl.com/mljoe


ETIME(YES).
FOR EACH CUSTOMER NO-LOCK:
ACCUMULATE custNum (COUNT).
END.

MESSAGE "TOTAL OF RECORDS = " (ACCUM COUNT BALANCE) SKIP(1)
"TIME FOR PROCESSING = " ETIME VIEW-AS ALERT-BOX.
 

Casper

ProgressTalk.com Moderator
Staff member
Or faster:

define query q1 for customer scrolling.
etime(yes)
open query q1 preselect each customer.
display num-results("q1") etime.
 
I am not interested in how to perform this operation in SQL - this is a Progress / Progress OpenEdge forum.
All the serious responses indicate that I will have to manually perform a FOR EACH ... The query approach mentioned, is this exactly.
Thanks.
 

Casper

ProgressTalk.com Moderator
Staff member
I am not interested in how to perform this operation in SQL

In 10.1A SQL is (supposed to be,haven't been able to test yet)the fastest way to do a table scan (provided you use type II storage area's)

Casper.
 

TomBascom

Curmudgeon
mpowell_esq said:
Again with the refference to SQL. This is Progress talk.
I am running Progress OpenEdge 10.1 on my box NOT SQL

Actually you probably are running SQL. Unless you go out of your way to prevent it you get both when you install Progress. Whether you use it or not is up to you but it is there for you.

In any event... you might also use proutil like so:

proutil sports2000 -C tabanalys Order

in order to get per area stats that include record counts. If the table in question is in an isolated storage area ("Order" is a storage area above) then you can get a record count fairly quickly (you'll have to parse the output though).
 
Actually you probably are running SQL.

Just because it is installed, does not mean I am running SQL. I ONLY execute Progress 4GL src. I have M$ Office 200 installed, though I am not executing the pile of brown stuff also known as Access.
Like wise I also have the Sonic products installed they are part of Progress OpenEdge 10.1.
 

joey.jeremiah

ProgressTalk Moderator
Staff member
this is one of these, maybe the ideal case for satisfying a query thru index/es. since indices are thousands of times smaller in size then the data.

cool, counting millions and millions of records in seconds, well, a minute tops. zooooom :)

afaik, we have no util documented or undocumented that does that, and any query in any way has to go thru the data.
 
10.1a

In 10.1A SQL is supported. I have 10.1 on my box and SQL IS supported. Likewise with 10.0B, I have both installed on my WorkStation. I have had the Hands On V10 training. I am also scheduled to attend the 10.1 training in Slough. The training is being tested next week in USA first.
 

TomBascom

Curmudgeon
joey.jeremiah said:
this is one of these, maybe the ideal case for satisfying a query thru index/es. since indices are thousands of times smaller in size then the data.

cool, counting millions and millions of records in seconds, well, a minute tops. zooooom :)

afaik, we have no util documented or undocumented that does that, and any query in any way has to go thru the data.

Table scans can be more effective without an index. So depending on the query he might benefit from the (SQL only) ability of the database to scan a type 2 area without using an index.

The general rule of thumb from the Oracle world is that if you are reading more than 10 or 20% of the records you are better off reading them without an index.
 

joey.jeremiah

ProgressTalk Moderator
Staff member
TomBascom said:
Table scans can be more effective without an index. So depending on the query he might benefit from the (SQL only) ability of the database to scan a type 2 area without using an index.

The general rule of thumb from the Oracle world is that if you are reading more than 10 or 20% of the records you are better off reading them without an index.
hi Tom

i meant index only, without passing thru the records at all, just
counting the index keys.

and in a few very unique cases some queries with 2-3 fields can be
satisfied by the index alone.

bottom line is that the data needed to be read will be several thousand
times smaller in size.


when is the 4bl going to do some catching up with the sql engine even
for features like fast table scans ?

you'd know, come on, tell us :)
 

TomBascom

Curmudgeon
joey.jeremiah said:
hi Tom

i meant index only, without passing thru the records at all, just
counting the index keys.

and in a few very unique cases some queries with 2-3 fields can be
satisfied by the index alone.

bottom line is that the data needed to be read will be several thousand
times smaller in size.

I understood that. I was highlighting the opposite scenario. Which is all too common.

when is the 4bl going to do some catching up with the sql engine even for features like fast table scans ?

you'd know, come on, tell us :)

The current word is "no plan". For the last 3 or 4 years at Exchange the engine crew crows about fast table scans (and maybe mentions "SQL only" in the fine print) and the rabble at the 4GL info exchange then dutifully ask 4GL product management when it will be supported by the 4GL. Every year product management professes astonishment at the request, having never heard it before, and then duly records it on the write-only powerpoint slide. Last year Gus helpfully explained the request for product management's benefit since a great deal of confusion at this startling new need was expressed by the speaker but 10.1 has come and there is no sign of anything having changed.

This year they will probably request a "use case" (that's their latest universal product management customer control strategy) after they profess shock and amazement at this puzzling and oh so new request.
 

joey.jeremiah

ProgressTalk Moderator
Staff member
TomBascom said:
I understood that. I was highlighting the opposite scenario. Which is all too common.



The current word is "no plan". For the last 3 or 4 years at Exchange the engine crew crows about fast table scans (and maybe mentions "SQL only" in the fine print) and the rabble at the 4GL info exchange then dutifully ask 4GL product management when it will be supported by the 4GL. Every year product management professes astonishment at the request, having never heard it before, and then duly records it on the write-only powerpoint slide. Last year Gus helpfully explained the request for product management's benefit since a great deal of confusion at this startling new need was expressed by the speaker but 10.1 has come and there is no sign of anything having changed.

This year they will probably request a "use case" (that's their latest universal product management customer control strategy) after they profess shock and amazement at this puzzling and oh so new request.

yeah. i didn't buy that story either or even took it seriously.

it's like there's a need to argue for high'er performance features ?
which are pretty common place.

and the 4gl/sql diff "mind set" doesn't apply here either.


my take on it is that they've got alot on their plate at the moment
and they're just buying time or dodging these questions.

maybe sql support for reporting tools among others are the reason
and are mostly in demand for these high end "read" features.
it's probably not easy designing for two query engines support either.

for now most of the struct enhancements are mostly for sql,
we could have gotten alot of the benefits years ago with d&l, right ?
it's worth a shot but maybe a freshly d&l v8 can compete with a type 2 db.


maybe, and if god is willing :), things will pickup
after they'll complete most of their sql engine task list.

i can think of many cases where a query that has no optimizer can go bad
and run thru half the database or atleast be very, very inefficient.

it'd be nice if the tech could take care of that for us
without us even having to know anything about it.


appreciate the info. thanks
 

joey.jeremiah

ProgressTalk Moderator
Staff member
how cool would this be ...

if we could write a static "for each order, each orderline, each item"


the entire where clause would be parsed as a whole and simplified

an optimizer will change the join order and indexes to use.
for example if we were searching for a single itemnum
the join order would be for each item, each orderline, each order

and the where clause would be appropriatly div across the buffers.


and even in a remote connection a database server will process
the entire query as a whole and not broken into single-table queries.
so remote connections even on a lan wouldn't be such a handicap

i never understood why multi and single-table queries
were so impossible to bridge ?


kinda like select but still keep the navigation features of 4bl.

sorry for the out burst, just some things that would be way up on my list
and would be a great, fundamental feature to have.
 

Serj HAMMER

Junior Racer
some tuning...

Casper said:
Or faster:

define query q1 for customer scrolling.
etime(yes)
open query q1 preselect each customer.
display num-results("q1") etime.
DEFINE QUERY q1 FOR customer FIELDS ( ).
- it is not faster, but may be it is more compact?..
 
Top