Need more Indexing on the LOG table
Please consider adding more indexing to the LOG table. It would be much better to try and implement this early in the school year, before the logs grow too large.
NOTE: The original request has been modified to be a bit more generic, as we cannot commit to doing this exact solution without considering the other effects on the system. However the overal ldea is the same, to improve the functioning of the log table to allow better searching of the results.
------------------Original request------------------
Please consider adding an index to the LOG table for the OB column.
Your article on "How to Query the LOG table" (https://support.aeries.com/support/solutions/articles/14000152189-how-to-query-the-log-table) seems to encourage users to filter on the OB column, but doing so usually results in the query optimizer using an execution plan that does a clustered index scan rather than an index seek (with a key lookup).
There is an existing index on CD, which usually performs better (as much as 50x faster), but the search arguments required to leverage this index are not always intuitive. While most CD values follow the "{table_code}chg" pattern (e.g.: STUchg, ATTchg, MSTchg, etc.), this convention is not implemented consistently. For example, we've seen LOG records with
an OB value of "MST" with 2 CD values--MSTchg or NetQryChgCmd--and an OB value of "STU" with 5 CD values--STUchg, SGMchg, NetQryChgCmd, STUadd, or TCH.
To emphasize the impact, say we wanted to find the most-recent LOG entries for the the MST table where the SM field was updated. We might try running one of the following Aeries Queries (or their SQL equivalent)--the first filters on the more-intuitive OB field and the second filters on the CD field. NOTE: The statistics reported are from mid-July, under minimal load, and with over 130M records in the LOG table
Filtering using OB:
LIST LOG BY TID ^ IF OB = 'MST' AND OD ; '"CN":""' AND ND : '"SM":'
Aeries Query elapsed time: 19 seconds to render results
SQL IO stats: 9 scans and 9,938,462 logical reads
SQL Time stats: 9.095 seconds elapsed execution time (66.844 seconds of CPU combined over 8 threads; approx. 8.356 seconds per thread)
Filtering using CD:
LIST LOG BY TID ^ IF CD = 'MSTCHG' AND OD ; '"CN":""' AND ND : '"SM":'
Aeries Query elapsed time: 4 seconds to render results
SQL IO stats: 7 scans and 394,582 logical reads
SQL Time stats: 0.170 seconds elapsed execution time (0.720 seconds of CPU combined over 8 threads; approx. 0.090 seconds per thread)
Having an index on the LOG.OB column would enable users to filter on the more intuitive OB column while providing increased query performance similar to that of the CD column.
Ernest
shared this idea
