472,958 Members | 2,274 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,958 software developers and data experts.

Exorbitant execution time

Hi there,

lately, I experienced a strange thing on my DB2 V8.1 on Windows: Some
queries took a very long time. A snapshot discovered the following:

Number of executions = 47
Number of compilations = 1
Worst preparation time (ms) = 2
Best preparation time (ms) = 2
Internal rows deleted = 0
Internal rows inserted = 0
Rows read = 192277
Internal rows updated = 0
Rows written = 0
Statement sorts = 47
Buffer pool data logical reads = 76306
Buffer pool data physical reads = 155
Buffer pool temporary data logical reads = 0
Buffer pool temporary data physical reads = 0
Buffer pool index logical reads = 2995
Buffer pool index physical reads = 16
Buffer pool temporary index logical reads = 0
Buffer pool temporary index physical reads = 0
Total execution time (sec.ms) = 123.065931
Total user cpu time (sec.ms) = 0.546875
Total system cpu time (sec.ms) = 0.062500
Statement text = SELECT COL1, COL2, COL3, COL4,
COL5, COL6, COL7, COL8, COL9, COL10, COL11, COL12, COL13 FROM TABLE1
WHERE (COL6 IN (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
?, ?, ?, ?, ?, ?, ?)) AND COL12 = 'N'

Total execution time was 123 seconds, although user and system cpu time
had been reasonable values.
There is an index on COL6 and COL12 (in this order; maybe, (COL12, COL6)
is faster).
155 physical reads is not that bad (0.2%). Assumed these are all random
reads, is 120 seconds a reasonable number on a Windows machine? This
equals 775 ms per random read. I learned random reads take about 10 to
20 ms on a z/OS box.
However, I cannot imagine that all physical reads are random reads. If
there are sequential reads, the execution time of random reads must be
even longer.

What are your experiences? Do you think, this execution time is caused
by the physical reads? If not, what are your assumptions?
Thanks for your help,

Johannes
Nov 12 '05 #1
5 2393
"Johannes Lebek" <re****************@domain.com> wrote in message
news:ca**********@sp15en20.hursley.ibm.com...
Hi there,

lately, I experienced a strange thing on my DB2 V8.1 on Windows: Some
queries took a very long time. A snapshot discovered the following:

Number of executions = 47
Number of compilations = 1
Worst preparation time (ms) = 2
Best preparation time (ms) = 2
Internal rows deleted = 0
Internal rows inserted = 0
Rows read = 192277
Internal rows updated = 0
Rows written = 0
Statement sorts = 47
Buffer pool data logical reads = 76306
Buffer pool data physical reads = 155
Buffer pool temporary data logical reads = 0
Buffer pool temporary data physical reads = 0
Buffer pool index logical reads = 2995
Buffer pool index physical reads = 16
Buffer pool temporary index logical reads = 0
Buffer pool temporary index physical reads = 0
Total execution time (sec.ms) = 123.065931
Total user cpu time (sec.ms) = 0.546875
Total system cpu time (sec.ms) = 0.062500
Statement text = SELECT COL1, COL2, COL3, COL4,
COL5, COL6, COL7, COL8, COL9, COL10, COL11, COL12, COL13 FROM TABLE1
WHERE (COL6 IN (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
?, ?, ?, ?, ?, ?, ?)) AND COL12 = 'N'

Total execution time was 123 seconds, although user and system cpu time
had been reasonable values.
There is an index on COL6 and COL12 (in this order; maybe, (COL12, COL6)
is faster).
155 physical reads is not that bad (0.2%). Assumed these are all random
reads, is 120 seconds a reasonable number on a Windows machine? This
equals 775 ms per random read. I learned random reads take about 10 to
20 ms on a z/OS box.
However, I cannot imagine that all physical reads are random reads. If
there are sequential reads, the execution time of random reads must be
even longer.

What are your experiences? Do you think, this execution time is caused
by the physical reads? If not, what are your assumptions?
Thanks for your help,

Johannes


First you should do an explain and determine which indexes are being used or
if a tablespace scan is used.

I don't understand what you are saying about the existing indexes. Is their
one composite index on (COL12, COL6) or are their 2 indexes, one on COL12
and one on COL6?

Did you execute runstats with distribution on key columns and indexes all?

How many rows in the table?
What is the cardinality of COL12?
What is the cardinality of the IN values for COL6?
Nov 12 '05 #2
Total execution time = time cursor was closed - time cursor was opened

So if your app does a lot of work with the cursor open, that would
explain it.

Johannes Lebek wrote:
Hi there,

lately, I experienced a strange thing on my DB2 V8.1 on Windows: Some
queries took a very long time. A snapshot discovered the following:

Number of executions = 47
Number of compilations = 1
Worst preparation time (ms) = 2
Best preparation time (ms) = 2
Internal rows deleted = 0
Internal rows inserted = 0
Rows read = 192277
Internal rows updated = 0
Rows written = 0
Statement sorts = 47
Buffer pool data logical reads = 76306
Buffer pool data physical reads = 155
Buffer pool temporary data logical reads = 0
Buffer pool temporary data physical reads = 0
Buffer pool index logical reads = 2995
Buffer pool index physical reads = 16
Buffer pool temporary index logical reads = 0
Buffer pool temporary index physical reads = 0
Total execution time (sec.ms) = 123.065931
Total user cpu time (sec.ms) = 0.546875
Total system cpu time (sec.ms) = 0.062500
Statement text = SELECT COL1, COL2, COL3, COL4,
COL5, COL6, COL7, COL8, COL9, COL10, COL11, COL12, COL13 FROM TABLE1
WHERE (COL6 IN (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
?, ?, ?, ?, ?, ?, ?)) AND COL12 = 'N'

Total execution time was 123 seconds, although user and system cpu time
had been reasonable values.
There is an index on COL6 and COL12 (in this order; maybe, (COL12, COL6)
is faster).
155 physical reads is not that bad (0.2%). Assumed these are all random
reads, is 120 seconds a reasonable number on a Windows machine? This
equals 775 ms per random read. I learned random reads take about 10 to
20 ms on a z/OS box.
However, I cannot imagine that all physical reads are random reads. If
there are sequential reads, the execution time of random reads must be
even longer.

What are your experiences? Do you think, this execution time is caused
by the physical reads? If not, what are your assumptions?
Thanks for your help,

Johannes

Nov 12 '05 #3
Ian
Johannes Lebek wrote:
Hi there,

lately, I experienced a strange thing on my DB2 V8.1 on Windows: Some
queries took a very long time. A snapshot discovered the following:

Number of executions = 47


Also be aware that the total execution time is cumulative for the number of
executions (47). So, that equates to an average of 2.62 seconds per
execution.

-----= Posted via Newsfeeds.Com, Uncensored Usenet News =-----
http://www.newsfeeds.com - The #1 Newsgroup Service in the World!
-----== Over 100,000 Newsgroups - 19 Different Servers! =-----
Nov 12 '05 #4
Hey;
that was my line...

;-)

PM

"Ian" <ia*****@mobileaudio.com> a écrit dans le message de
news:40**********@corp.newsgroups.com...
Johannes Lebek wrote:
Hi there,

lately, I experienced a strange thing on my DB2 V8.1 on Windows: Some
queries took a very long time. A snapshot discovered the following:

Number of executions = 47
Also be aware that the total execution time is cumulative for the number

of executions (47). So, that equates to an average of 2.62 seconds per
execution.

-----= Posted via Newsfeeds.Com, Uncensored Usenet News =-----
http://www.newsfeeds.com - The #1 Newsgroup Service in the World!
-----== Over 100,000 Newsgroups - 19 Different Servers! =-----

Nov 12 '05 #5
> Total execution time (sec.ms) = 123.065931
Total user cpu time (sec.ms) = 0.546875
Total system cpu time (sec.ms) = 0.062500 Means you have something such as
- > 2 minutes in network (e.g. server in Australia, client in Switzerland,
using non-batched reads),
- > 2 minutes in client code (you have a 10MHz AT doing a bubble sort on
each fetch),
- > you are displaying the results to the user (GUI) before closing the
cursor (human thinking time) (==> locking !!!).

"Johannes Lebek" <re****************@domain.com> wrote in message
news:ca**********@sp15en20.hursley.ibm.com... Hi there,

lately, I experienced a strange thing on my DB2 V8.1 on Windows: Some
queries took a very long time. A snapshot discovered the following:

Number of executions = 47
Number of compilations = 1
Worst preparation time (ms) = 2
Best preparation time (ms) = 2
Internal rows deleted = 0
Internal rows inserted = 0
Rows read = 192277
Internal rows updated = 0
Rows written = 0
Statement sorts = 47
Buffer pool data logical reads = 76306
Buffer pool data physical reads = 155
Buffer pool temporary data logical reads = 0
Buffer pool temporary data physical reads = 0
Buffer pool index logical reads = 2995
Buffer pool index physical reads = 16
Buffer pool temporary index logical reads = 0
Buffer pool temporary index physical reads = 0
Total execution time (sec.ms) = 123.065931
Total user cpu time (sec.ms) = 0.546875
Total system cpu time (sec.ms) = 0.062500
Statement text = SELECT COL1, COL2, COL3, COL4,
COL5, COL6, COL7, COL8, COL9, COL10, COL11, COL12, COL13 FROM TABLE1
WHERE (COL6 IN (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
?, ?, ?, ?, ?, ?, ?)) AND COL12 = 'N'

Total execution time was 123 seconds, although user and system cpu time
had been reasonable values.
There is an index on COL6 and COL12 (in this order; maybe, (COL12, COL6)
is faster).
155 physical reads is not that bad (0.2%). Assumed these are all random
reads, is 120 seconds a reasonable number on a Windows machine? This
equals 775 ms per random read. I learned random reads take about 10 to
20 ms on a z/OS box.
However, I cannot imagine that all physical reads are random reads. If
there are sequential reads, the execution time of random reads must be
even longer.

What are your experiences? Do you think, this execution time is caused
by the physical reads? If not, what are your assumptions?
Thanks for your help,

Johannes

Nov 12 '05 #6

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

6
by: cournape | last post by:
Hi there, I have some scientific application written in python. There is a good deal of list processing, but also some "simple" computation such as basic linear algebra involved. I would like to...
7
by: Tim Quon | last post by:
Hi Is there any function to get the current time so I can calculate the execution time of my code? What all is in the time.h and sys/times.h? Thanks Tim
38
by: vashwath | last post by:
Might be off topic but I don't know where to post this question.Hope some body clears my doubt. The coding standard of the project which I am working on say's not to use malloc.When I asked my...
3
by: iam980 | last post by:
Hello All. We have tested following SQL script from query analyzer: -- Script begin DECLARE @I int; SET @I = 1; WHILE @I < 10000000 BEGIN SET @I = @I + 1; END -- Script end
3
by: vashwath | last post by:
Hi all, I have written program for calculating the execution time of a function.Any critics (on the method of calculating execution time) are welcome. #include <stdio.h> #include <time.h>...
2
by: Ina Schmitz | last post by:
Hi NG, does IBM Universal Database 8.2 make any difference between actual and estimated execution plans like in SQL Server ("set showplan_all on" for estimated execution plan and "set statistics...
17
by: romixnews | last post by:
Hi, I'm facing the problem of analyzing a memory allocation dynamic and object creation dynamics of a very big C++ application with a goal of optimizing its performance and eventually also...
40
by: kavi | last post by:
Hello friends, Could any one tell the way of calculating the speed of c program execution?
6
by: Mikhail Kovalev | last post by:
I'm using set_time_limit() to set maximum execution time. Is there a way to check how much time is left at any time during the execution itself?
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 4 Oct 2023 starting at 18:00 UK time (6PM UTC+1) and finishing at about 19:15 (7.15PM) The start time is equivalent to 19:00 (7PM) in Central...
4
NeoPa
by: NeoPa | last post by:
Hello everyone. I find myself stuck trying to find the VBA way to get Access to create a PDF of the currently-selected (and open) object (Form or Report). I know it can be done by selecting :...
3
NeoPa
by: NeoPa | last post by:
Introduction For this article I'll be using a very simple database which has Form (clsForm) & Report (clsReport) classes that simply handle making the calling Form invisible until the Form, or all...
1
by: Teri B | last post by:
Hi, I have created a sub-form Roles. In my course form the user selects the roles assigned to the course. 0ne-to-many. One course many roles. Then I created a report based on the Course form and...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 1 Nov 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM) Please note that the UK and Europe revert to winter time on...
3
by: nia12 | last post by:
Hi there, I am very new to Access so apologies if any of this is obvious/not clear. I am creating a data collection tool for health care employees to complete. It consists of a number of...
0
NeoPa
by: NeoPa | last post by:
Introduction For this article I'll be focusing on the Report (clsReport) class. This simply handles making the calling Form invisible until all of the Reports opened by it have been closed, when it...
0
isladogs
by: isladogs | last post by:
The next online meeting of the Access Europe User Group will be on Wednesday 6 Dec 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, Mike...
2
by: GKJR | last post by:
Does anyone have a recommendation to build a standalone application to replace an Access database? I have my bookkeeping software I developed in Access that I would like to make available to other...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.