Let me try to explain with an example:
for each customer:
for each order of customer:
/* do something */
end.
Query tuning is a massive area that it would be hard to do justice with in a forum like this.
In terms of your example above, there is no benefit to the temp table, unless you were doing multiple queries on Customer within your code. You can cache the customer in a temp-table and so you're only reading it from disk in future calls.
Caching of relatively static data in temp tables can be a major performance win if the data is read multiple times, or if you don't have sufficient indexes as you can define additional indexes on the tt.
Network performance of the example you have shown will stink.
That is because you are using share-locks. That will result in individual round-trip messages for each record as well as a one-way message to release the lock.
If you add NO-LOCK then the records will be grouped into "queries" and bundled into messages. The number of records per message will be influenced by -Mm, -prefetchFactor, and -prefetchNumRecs. Using "FIELDS" can also have a substantial impact.
You can measure this stuff with _actServer VST.
Kbase 18342 explains network messages.
Tom, this was just a quick SIMPLE sample and not reflective of actual code.
I have a whole hour long talk on the topic of tuning network messages -- sadly it wasn't ready in time to propose to the PUG.
SIMPLE but catastrophically bad and simple to fix.
You should NEVER code even the simplest example with SHARE-LOCK unless the point is to show something about SHARE-LOCK.
(The people writing examples for the documentation really need to learn this.)
You asked about "number of calls" and "server hits". The most significant factor in figuring that out is whether or not your code can take advantage of "NO-LOCK Queries". If you do not have NO-LOCK then the answer is easy and unpleasant. If you do have NO-LOCK then there are additional considerations (see the kbase) but you are at least on the right road.
> Why would you choose (if ever) to loop through customer first and create a temp table and then use that temp table to loop through the order table?
I do this with the system tables. Joins might significantly slow down a code in client-server mode even if the number of records is relatively small.
Played with _actServer VST but got too much zeroes, like stated here so it might not be reliable.
Btw how can I find pkb #18342 since this entry does not seem to be what you refer to
That is the proper article. It goes into quite a lot of detail on how these things work.
[View:https://knowledgebase.progress.com/articles/Article/18342/:550:50]
I have found _actServer to be reliable. But you might need to make arrangements to isolate your test cases. Or maybe use a reliable OS?
Anyhow... I test with a copy of sports that I am the only user of. That makes my traffic much easier to identify and analyze.
My test harness looks like this:
/* foreach.p */
{actsrv_hdr.i}
{actsrv_init.i}
for each _field no-lock: /* test 1 */
end.
{actsrv_end.i "for each"}
{actsrv_init.i}
for each _field fields( _field-name ) no-lock: /* test 2 */
end.
{actsrv_end.i "for each fields()"}
The include files:
/* actsrv_hdr.i */
define variable msgRecv as integer no-undo.
define variable msgSent as integer no-undo.
define variable recSent as integer no-undo.
define variable qryRecv as integer no-undo.
/* actsrv_init.i */
find _actServer no-lock where _Server-id = 2.
assign
msgRecv = _Server-msgRec
msgSent = _Server-msgSent
recSent = _Server-recSent
qryRecv = _Server-QryRec
.
etime( yes ).
/* actsrv_end.i */
find _actServer no-lock where _Server-id = 2.
assign
msgRecv = _Server-msgRec - msgRecv
msgSent = _Server-msgSent - msgSent
recSent = _Server-recSent - recSent
qryRecv = _Server-QryRec - qryRecv
.
output to value( "nettraffic.txt" ) append.
put
( msgRecv + msgSent ) format ">>>,>>>,>>9"
qryRecv format ">>>,>>>,>>9"
recSent format ">>>,>>>,>>9"
( recSent / qryRecv ) format ">>>,>>>,>>9"
etime format ">>,>>9"
.
put unformatted " " trim( session:parameter + " {1}" ) skip.
output close.