Kbase 21216: Diagnose 4GL Query Problems: Use Parameters -zqil and -zqilv
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
10/16/2008 |
|
Status: Verified
GOAL:
How to Diagnose Problems with 4GL Queries; Use Parameters -zqil and -zqilv
FIX:
The client startup parameters, -zqil and -zqilv, have not been formally documented since they might at some point be removed from the Progress package. Designed for use in testing and improving the behavior of 4GL queries, their normal use would typically be limited to test environments.
This Knowledge Base describes the purpose of these client startup parameters as well as some of their useful aspects.
Originally created by Progress Development, -zqil and -zqilv were designed to assist in reviewing 4GL code to verify the behavior of queries.
-zqil and -zqilv can be used with any Character or GUI Progress client startup. The purpose of the parameters is to give additional Query Diagnostic statements which will be output to every database log file the client is connected to when performing a query. Every query made by a client that starts up with this parameter will be logged and some analysis of each query made.
This will have a side effect of slowing down all work done by that client, but the information gathered might be of some benefit. It is therefore suggested that the use of the -zqil and -zqilv be limited to testing and analysis phases, especially with a production database since this can quickly fill up a database log.
-- What to look for in the log files
From the beginning of the client startup, each query performed by the client will be numbered Query No. 1, Query No. 2, etc.
For each Query, a statement will be given of what index is being used to satisfy the query. Depending on the type of query, additional information will be given. If it is a range match for instance, it might list the high and low components that match. It might show that the query type is an EQUALITY match or a QBW (Query by Word-Index), etc.
The -zqilv is slightly more detailed than the -zqil; the 'v' signifying verbose. It gives details like whether the query is performing a WHOLE-INDEX (also known as a 'full table scan') or what leaf level of the query is being enacted.
One of the important benefits of using the -zqilv startup parameter is the ability to spot points within your application where a WHOLE-INDEX query is being performed. This is often very useful when you don't have access to the source code but you believe there may be a query which is not optimized as well as it could be.
However, be aware that just because there is a WHOLE-INDEX query within your application does not mean that the code is not written well. There may be very few records in a table and for expediency sake the code will look through each record of a very small table. WHOLE-INDEX scans are more detrimental if a scan is being frequently performed on a table with a large number of records.
There can also be instances where you truly wish an action to be performed against all records of a table which would also equate to an WHOLE-INDEX query.
Using the -zqilv startup, a simple query against any database will be analyzed and that extra information will be posted to the database log(s).
The following 6 examples use the sports2000 Database.
Example 1:
PAUSE 0 BEFORE-HIDE.
FOR EACH customer:
DISPLAY customer EXCEPT comments.
END.
This yields this in the database log file:
23:13:11 Usr 5:
==INTERNAL Server Query execution Method Query No. 1== (6136)
23:13:11 Usr 5: LEAF (1848)WHOLE-INDEX (1848)G (1848)B (1848)0 (1848) (1848)INDEX 12 (6141)
Example 2:
PAUSE 0 BEFORE-HIDE.
FOR EACH customer WHERE custnum = 15.
DISPLAY customer EXCEPT comments.
END.
Yields this in the log:
23:13:11 Usr 5:
==INTERNAL Compiled Query Resolution Method: Query No. 3== (6135)
23:38:17 Usr 5: LEAF (1848)FGE (1848)INDEX 12 1 1 EQUALITY (6154)
This is classified as a Find Greater than or Equal to (FGE) query.
Index 12 is used (Custnum)BR>1 component was used to match the low end
1 component was used to match the high end of the query
This is an EQUALITY query.
Example 3:
PAUSE 0 BEFORE-HIDE.
FOR EACH customer WHERE custnum LT 15.
DISPLAY customer EXCEPT comments.
END.
Yields this in the log:
00:00:23 Usr 5:
==INTERNAL Compiled Query Resolution Method: Query No. 4== (6135)
00:00:23 Usr 5: LEAF (1848)INDEX 12 0 1 (6157)
00:00:23 Usr 5:
This is using index 12
0 components matched the low end of the search
1 component matched the high end of the search
Example 4:
PAUSE 0 BEFORE-HIDE.
FOR EACH customer WHERE custnum GT 2000.
DISPLAY customer EXCEPT comments.
END.
Yields this in the log:
==INTERNAL Compiled Query Resolution Method: Query No. 5== (6135)
00:02:39 Usr 5: LEAF (1848)INDEX 12 1 0 (6157)
00:02:39 Usr 5:
This is using index 12
1 component matched the low end of the search
0 components matched the high end of the search
Example 5:
PAUSE 0 BEFORE-HIDE.
FOR EACH customer WHERE custnum GT 2000 AND custnum LT 2002.
DISPLAY customer EXCEPT comments.
END.
Yields this in the log:
==INTERNAL Compiled Query Resolution Method: Query No. 6== (6135)
00:04:24 Usr 5: LEAF (1848)INDEX 12 1 1 (6157)
00:04:24 Usr 5:
Index 12 is used
1 component was used to match the low end
1 component was used to match the high end of the query
Example 6:
FOR EACH feedback WHERE Comments CONTAINS "enjoy".
DISP feedback.
END.
Yields this in the log:
==INTERNAL Compiled Query Resolution Method: Query No. 99== (6135)
11:16:32 LEAF (1848)QBW (1848)FGE (1848)INDEX 33 1 1 (6157)
11:16:32
==INTERNAL Server Query execution Method Query No. 99== (6136)
11:16:32 QBW (1848)G (1848)B (1848)F (1848) (1848)QUERY-BY-WORD (6164)
11:16:32 LEAF (1848)QBW (1848)G (1848)B (1848)F (1848) 1848)INDEX 33 CONTAINS ENJOY (6148)
This example shows some of the extra information given when -zqilv is used. In this case there is a second section of the query information logged which actually shows the CONTAINS phrase used for this query.
One of the major benefits gained by knowing and using these parameters is understanding what possible performance gains or losses might be accrued. Though not always a clear answer to every performance problem, it can prove helpful in understanding how the many components of a query are being used and what index is being used. Couple this with knowledge of the schema and it might provide you with sufficient information to restructure your queries.
The restructuring of the queries might pick a better index. That is, it might use a different index which has more component matches, or it might stop using an index (for example, possibly improper use of a USE-INDEX statement which can limit which index is used in a query)..