Query Performance - How can you determine the number of calls and impact for a given query? - Forum - OpenEdge General - Progress Community

Query Performance - How can you determine the number of calls and impact for a given query?

 Forum

Query Performance - How can you determine the number of calls and impact for a given query?

This question is not answered

Let me try to explain with an example:

for each customer:

  for each order of customer:

  /* do something */

end.

  • How do we know the number of server hits it would take to complete to return the query results?
  • 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?
  • Note: in one sample case, customer would have less than 100 records while order could have thousands.
  • My big question is would it matter if the customer records are better gathered into a temp table first.
All Replies
  • 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 Bascom
    tom@wss.com

  • 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.

    --
    Tom Bascom
    tom@wss.com

  • SIMPLE but catastrophically bad and simple to fix.

    --
    Tom Bascom
    tom@wss.com

  • 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.

    --
    Tom Bascom
    tom@wss.com

  • > 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.

    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.

     

     

    --
    Tom Bascom
    tom@wss.com