SELECT without COMMIT in Request-level-logging-file

[former_SQLA_member326303]

There is a simple query that is executed at regular intervals (5 seconds) without COMMIT.
When I look at the Request-level-logging-file, I see that the execution time of the query is almost equal to this interval:

-- 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?
Do not worsen the a large number COMMIT-operators performance of the system?

[johnsmirnios · Accepted solution]

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.