Consultor Eletrônico

Status: Unverified

GOAL:

Is there any way to extract index information when using Dynamic Queries?

GOAL:

Is there any parameter in WebSpeed that can reference and/or display the index usage within Dynamic queries?

FIX:

The easiest way to extract the index information when using Dynamic queries is to use either the INDEX-INFORMATION attribute and/or by using the QUERY-PREPARE within the code. Once either or both of these two attributes are referenced, you must set the Logging level to 4 and Log entry types to QryInfo within the ubroker file. There is no WebSpeed parameter per se that show this information. However, this can be used against WebSpeed applications using Dynamic queries.

Take a look at an example using QUERY-PREPARE & INDEX-INFORMATION:
def query q for customer,order,order-line scrolling.
def var x as handle.
def var i as integer.
def var j as integer.
x = query q:handle.
x:query-prepare("for each customer where cust-num < 3,
each order of customer, each order-line").
x:query-open.
message "prepare string is" x:prepare-string.
repeat i = 1 to x:num-buffers:
j = lookup("WHOLE-INDEX",x:index-information(i)).
if (j > 0)
then message "inefficient index"
entry(j + 1,x:index-information(i)).
else message "bracketed index use of" x:index-information(i).
end.

Take a look at an example using QUERY-PREPARE:
DEFINE VARIABLE qh AS WIDGET-HANDLE.
DEFINE VARIABLE numvar AS INTEGER INITIAL 10.

CREATE QUERY qh.

qh:SET-BUFFERS(BUFFER customer:HANDLE).
qh:QUERY-PREPARE("FOR EACH customer WHERE integer( custnum ) < " + string(numvar)).
qh:QUERY-OPEN.

REPEAT WITH FRAME y:

qh:GET-NEXT().
IF qh:QUERY-OFF-END THEN LEAVE.
DISPLAY custnum
name FORMAT "x(30)"
city FORMAT "X(20)"
.
END.

qh:QUERY-CLOSE().
DELETE OBJECT qh.

Here is a snippet of the log file that has date/time stamps removed when using QUERY-PREPARE only.
P-002516 T-001976 1 4GL -- Logging level set to = 4
P-002516 T-001976 1 4GL -- Log entry types activated: QryInfo
P-002516 T-001976 2 4GL QRYINFO Query Plan: qinfo.p line 7
P-002516 T-001976 2 4GL QRYINFO QueryId: 0xb915c0
P-002516 T-001976 2 4GL QRYINFO Query Handle: 1000
P-002516 T-001976 2 4GL QRYINFO Type: Dynamically Opened Query
P-002516 T-001976 2 4GL QRYINFO PREPARE-STRING: FOR EACH customer WHERE integer( custnum ) < 10
P-002516 T-001976 2 4GL QRYINFO Prepared at Runtime
P-002516 T-001976 2 4GL QRYINFO Client Sort: N
P-002516 T-001976 2 4GL QRYINFO Scrolling: Y
P-002516 T-001976 2 4GL QRYINFO dbs/sp2k.Customer WHOLE-INDEX,CustNum
P-002516 T-001976 2 4GL QRYINFO Query Statistics: qinfo.p line 22
P-002516 T-001976 2 4GL QRYINFO QueryId: 0xb915c0
P-002516 T-001976 2 4GL QRYINFO Query Handle: 1000
P-002516 T-001976 2 4GL QRYINFO Times prepared: 1
P-002516 T-001976 2 4GL QRYINFO Time to prepare (ms): 0
P-002516 T-001976 2 4GL QRYINFO DB Blocks accessed to prepare:
P-002516 T-001976 2 4GL QRYINFO dbs/sp2k : 0
P-002516 T-001976 2 4GL QRYINFO Times opened: 1
P-002516 T-001976 2 4GL QRYINFO Used REPOSITION: N
P-002516 T-001976 2 4GL QRYINFO DB Blocks accessed:
P-002516 T-001976 2 4GL Q.RYINFO dbs/sp2k : 0
P-002516 T-001976 2 4GL QRYINFO dbs/sp2k.Customer Table:
P-002516 T-001976 2 4GL QRYINFO 4GL Records: 9
P-002516 T-001976 2 4GL QRYINFO Records from server: 9
P-002516 T-001976 2 4GL QRYINFO Useful: 9
P-002516 T-001976 2 4GL QRYINFO Failed: 0
Please note that no index information will be displayed when using a workfile. Here is a snippet of the log file showing references to a workfile.
P-004562 T-000001 2 WS QRYINFO <WORKFILE>.t-expense <Index Info Not Available>
P-004562 T-000001 2 WS QRYINFO Query Statistics: shr/voucher/whldexp.p line 204
P-004562 T-000001 2 WS QRYINFO QueryId: 0x6b17a4
P-004562 T-000001 2 WS QRYINFO <WORKFILE>.t-expense Table:
P-004562 T-000001 2 WS QRYINFO 4GL Records: 0
P-004562 T-000001 2 WS QRYINFO Records from server: 0
P-004562 T-000001 2 WS QRYINFO Useful: 0
P-004562 T-000001 2 WS QRYINFO Failed: 0
P-004562 T-000001 2 WS QRYINFO Select By Client: N
P-004562 T-000001 2 WS QRYINFO Query Plan: shr/voucher/whldexp.p line 207
P-004562 T-000001 2 WS QRYINFO QueryId: 0x6b17a4
P-004562 T-000001 2 WS QRYINFO Type: FOR Statement
P-004562 T-000001 2 WS QRYINFO Client Sort: N
.