Kbase P39930: Which is faster; when executed from a the Procedure Editor;
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
03/09/2003 |
|
Status: Unverified
GOAL:
Which is faster; when executed from a the Procedure Editor; the "SELECT COUNT(*) FROM TableName." statement or the "FOR EACH TableName." statement ?
GOAL:
Why does the FOR EACH with the WHERE on a non-indexed field performs faster than the vanilla FOR EACH?
FIX:
1. The SELECT versions listed in tests 1A and 2A perform the same as their FOR EACH counterparts because they have the same underlying code when executed from a 4GL client like the procedure editor.
2. The FOR EACH with the WHERE on a non-indexed field performs faster than the vanilla FOR EACH because not all the records are retrieved into the buffer and only those satisfying the given condition are actually retrieved while the vanilla FOR EACH without the WHERE clause will retrieve all the records.
Executing the following 4 tests against the Sports2000 database confirms the above facts. Note that the times listed may vary depending on the platform configuration used.
/1. SELECT COUNT (*) FROM order. Time = 80 milliseconds*/
ETIME(yes).
SELECT COUNT (*) FROM order.
MESSAGE ETIME
VIEW-AS ALERT-BOX INFO BUTTONS OK.
===================
/*1A. FOR EACH order. Time = 80 milliseconds*/
ETIME(yes).
FOR EACH order.
END.
MESSAGE ETIME
VIEW-AS ALERT-BOX INFO BUTTONS OK.
======================================================
/* 2. FOR EACH order WHERE ShipDate = 01/01/2004. Time = 30 milliseconds*/
ETIME(yes).
FOR EACH order WHERE ShipDate = 01/01/2004.
END.
MESSAGE ETIME
VIEW-AS ALERT-BOX INFO BUTTONS OK.
===================
/* 2A. SELECT COUNT (*) FROM order WHERE ShipDate = 01/01/2004. Time = 30 milliseconds*/
ETIME(yes).
SELECT COUNT (*) FROM order WHERE ShipDate = 01/01/2004.
MESSAGE ETIME
VIEW-AS ALERT-BOX INFO BUTTONS OK.
====================