There is a simple query that is executed at regular intervals (5 seconds) without COMMIT.
-- Statement 4: 1478 uses, 3889729 ms total, 2632 ms average, 6533 ms maximum time.
In fact (in ISQL), the query runs in the 20 milliseconds:
Execution time: 0.02 seconds ( Plan [ Total Cost Estimate: 0.00048296,
Hence, the Request-level-logging-file does not show the time of the query, but it show the time from the beginning of the query to the COMMIT:
=,<,1,PREPARE,select appbookid ... =,>,1,PREPARE,589884 =,<,1,OPEN,589884 +1,>,1,OPEN,262205 +2,<,1,CLOSE_BY_NAME =,>,1,CLOSE_BY_NAME,262205 =,<,1,DROP_STMT,589884 =,>,1,DROP_STMT =,<,1,PREPARE,commit
or to the start of the same query:
=,<,1,PREPARE,select appbookid ... +1,>,1,PREPARE,589880 +1,<,1,OPEN,589880 +23,>,1,OPEN,262201 +17910,<,1,CLOSE_BY_NAME =,>,1,CLOSE_BY_NAME,262201 =,<,1,DROP_STMT,589880 =,>,1,DROP_STMT =,<,1,PREPARE,select appbookid ...
So it is very difficult to find statements that stress the system.
Do I have (for further analysis of the "right" Request-level-logging-file) to execute a COMMIT immediately after the "select appbookid ..."-query?
The RLL doesn't show any fetches. That seems odd.
Is it possible that your app is fetching all rows? ISQL will only fetch the first screen-full of rows.
Also, you should compare the plans. ISQL probably opens the cursor for read-only access, your app might not. ISQL might also change the optimization goal to first-row rather than all rows (not sure on that one). It's worth comparing the plans.
answered 16 Mar '13, 09:21