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?

asked 11 Mar '13, 07:39

Ilia63's gravatar image

Ilia63
1.2k495578
accept rate: 44%

edited 15 Mar '13, 22:19

Mark%20Culp's gravatar image

Mark Culp
24.8k9139295


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.

permanent link

answered 16 Mar '13, 09:21

John%20Smirnios's gravatar image

John Smirnios
11.4k394154
accept rate: 38%

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×438
×23
×11

question asked: 11 Mar '13, 07:39

question was seen: 1,698 times

last updated: 16 Mar '13, 09:21