473,320 Members | 2,104 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

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

Why does FETCH FIRST slow down query?

During a routine performance check using an event monitor, I
discovered a class of query whose performance has me baffled.

The monitor captured:

SELECT * FROM EWM_CASE fetch first 1 rows only

It took 14 seconds of CPU time to execute. After looking up the
documentation on the FETCH FIRST notation I find "Limiting the result
table to the first integer rows can improve performance. The database
manager will cease
processing the query once it has determined the first integer rows."

I did some validation tests. From UNIX command line, I issued:

db2 -x "SELECT * FROM EWM_CASE fetch first 1 rows only"

and

db2 -x "SELECT * FROM EWM_CASE"|head -1

I got the same row back in both cases but times were drastically
different (13.86 secs vs. .002 sec).

When asked to explain this behavior, I was at a loss. Any clues from
the group?

(AIX 4.3, DB2 7.2 FP11)

Thanks,
Evan
Nov 12 '05 #1
8 21020
"Evan Smith" <es********@hotmail.com> wrote in message
news:23**************************@posting.google.c om...
During a routine performance check using an event monitor, I
discovered a class of query whose performance has me baffled.

The monitor captured:

SELECT * FROM EWM_CASE fetch first 1 rows only

It took 14 seconds of CPU time to execute. After looking up the
documentation on the FETCH FIRST notation I find "Limiting the result
table to the first integer rows can improve performance. The database
manager will cease
processing the query once it has determined the first integer rows."

I did some validation tests. From UNIX command line, I issued:

db2 -x "SELECT * FROM EWM_CASE fetch first 1 rows only"

and

db2 -x "SELECT * FROM EWM_CASE"|head -1

I got the same row back in both cases but times were drastically
different (13.86 secs vs. .002 sec).

When asked to explain this behavior, I was at a loss. Any clues from
the group?

(AIX 4.3, DB2 7.2 FP11)

Thanks,
Evan


Did you try a visual explain to see if the access path is different?
Nov 12 '05 #2
Evan Smith wrote:
During a routine performance check using an event monitor, I
discovered a class of query whose performance has me baffled.

The monitor captured:

SELECT * FROM EWM_CASE fetch first 1 rows only

It took 14 seconds of CPU time to execute. After looking up the
documentation on the FETCH FIRST notation I find "Limiting the result
table to the first integer rows can improve performance. The database
manager will cease
processing the query once it has determined the first integer rows."

I did some validation tests. From UNIX command line, I issued:

db2 -x "SELECT * FROM EWM_CASE fetch first 1 rows only"

and

db2 -x "SELECT * FROM EWM_CASE"|head -1

I got the same row back in both cases but times were drastically
different (13.86 secs vs. .002 sec).

When asked to explain this behavior, I was at a loss. Any clues from
the group?

(AIX 4.3, DB2 7.2 FP11)

Thanks,
Evan


Evan,

You want to also add the clause "OPTIMIZE FOR 1 ROW". For some strange
reason the UNIX optimizer doesn't work out that for itself, unlike the z/OS
optimizer.

Phil
Nov 12 '05 #3
No clue. But how big is the table EWM_CASE. I suppose it is a small table.
I did see some cases - fetch first N rows is very helpful and run faster
when I perform top N queries.

"Evan Smith" <es********@hotmail.com> wrote in message
news:23**************************@posting.google.c om...
During a routine performance check using an event monitor, I
discovered a class of query whose performance has me baffled.

The monitor captured:

SELECT * FROM EWM_CASE fetch first 1 rows only

It took 14 seconds of CPU time to execute. After looking up the
documentation on the FETCH FIRST notation I find "Limiting the result
table to the first integer rows can improve performance. The database
manager will cease
processing the query once it has determined the first integer rows."

I did some validation tests. From UNIX command line, I issued:

db2 -x "SELECT * FROM EWM_CASE fetch first 1 rows only"

and

db2 -x "SELECT * FROM EWM_CASE"|head -1

I got the same row back in both cases but times were drastically
different (13.86 secs vs. .002 sec).

When asked to explain this behavior, I was at a loss. Any clues from
the group?

(AIX 4.3, DB2 7.2 FP11)

Thanks,
Evan

Nov 12 '05 #4
"Fan Ruo Xin" <fa*****@sbcglobal.net> wrote in message news:<YS*****************@newssvr15.news.prodigy.c om>...
No clue. But how big is the table EWM_CASE. I suppose it is a small table.
I did see some cases - fetch first N rows is very helpful and run faster
when I perform top N queries.

After more investigation I can offer the following:

1. Table has 630K rows.
2. Explain Plans for query with and without fetch are same access path
and cost.
3. The output from the event monitor for the "FETCH" query offered
that sorting appears to be going on. Of the 14 CPU seconds consumed,
over 7 are being spent in sort phase.

This has to be a bug, right?
Regards,
Evan
Nov 12 '05 #5
> After more investigation I can offer the following:

1. Table has 630K rows.
2. Explain Plans for query with and without fetch are same access path
and cost.
3. The output from the event monitor for the "FETCH" query offered
that sorting appears to be going on. Of the 14 CPU seconds consumed,
over 7 are being spent in sort phase.

This has to be a bug, right?
Regards,
Evan


Are you sure this is the exact statement? There is no where clause and the
is no order by, etc?

SELECT * FROM EWM_CASE fetch first 1 rows only

Is this inside a cursor? Try adding FOR FETCH ONLY so they new statement is

SELECT * FROM EWM_CASE FOR FETCH ONLY fetch first 1 rows only
Nov 12 '05 #6

"Mark A" <no****@nowhere.com> wrote in message
news:4c***************@news.uswest.net...
After more investigation I can offer the following:

1. Table has 630K rows.
2. Explain Plans for query with and without fetch are same access path
and cost.
3. The output from the event monitor for the "FETCH" query offered
that sorting appears to be going on. Of the 14 CPU seconds consumed,
over 7 are being spent in sort phase.

This has to be a bug, right?
Regards,
Evan

Are you sure this is the exact statement? There is no where clause and the
is no order by, etc?

SELECT * FROM EWM_CASE fetch first 1 rows only

Is this inside a cursor? Try adding FOR FETCH ONLY so they new statement

is
SELECT * FROM EWM_CASE FOR FETCH ONLY fetch first 1 rows only

Well, where does the SORTING come from? Both sql stmts should use TABLE
SCAN. Anyway it is very interesting, so I try to reproduce it. But failed. I
got the same exec time either using EE or EEE.
Nov 12 '05 #7
>
Well, where does the SORTING come from? Both sql stmts should use TABLE
SCAN. Anyway it is very interesting, so I try to reproduce it. But failed. I
got the same exec time either using EE or EEE.


I'm just as confused as you are. I am including the output from the
DB2 event monitor for the two statements. These were both invoked from
the CLP. One of them shows sorting, the other doesn't. Perhaps one of
the more knowing event monitor gurus out there can see something that
I can't. (Caching doesn't seem to be in effect here either, as I get
similar results when I reverse the order of the operations.)

Here are the two entries:

126) Statement Event ...
Appl Handle: 239
Appl Id: 0A550712.F9CB.040726155050
Appl Seq number: 0001

Record is the result of a flush: FALSE
-------------------------------------------
Type : Dynamic
Operation: Close
Section : 201
Creator : NULLID
Package : SQLC2D03
Cursor : SQLCUR201
Cursor was blocking: FALSE
Text : SELECT * FROM db2admin.EWM_CASE fetch first 1 rows only
-------------------------------------------
Start Time: 07-26-2004 11:51:30.019546
Stop Time: 07-26-2004 11:51:45.155254
Exec Time: 15.135708 seconds
Number of Agents created: 1
User CPU: 13.690000 seconds
System CPU: 0.010000 seconds
Fetch Count: 1
Sorts: 1
Total sort time: 7979
Sort overflows: 1
Rows read: 1877
Rows written: 1991954
Internal rows deleted: 0
Internal rows updated: 0
Internal rows inserted: 0
SQLCA:
sqlcode: 0
sqlstate: 00000

144) Statement Event ...
Appl Handle: 239
Appl Id: 0A550712.F9CB.040726155050
Appl Seq number: 0001

Record is the result of a flush: FALSE
-------------------------------------------
Type : Dynamic
Operation: Close
Section : 201
Creator : NULLID
Package : SQLC2D03
Cursor : SQLCUR201
Cursor was blocking: FALSE
Text : SELECT * FROM db2admin.EWM_CASE
-------------------------------------------
Start Time: 07-26-2004 11:51:45.243032
Stop Time: 07-26-2004 11:51:45.297224
Exec Time: 0.054192 seconds
Number of Agents created: 1
User CPU: 0.000000 seconds
System CPU: 0.000000 seconds
Fetch Count: 208
Sorts: 0
Total sort time: 0
Sort overflows: 0
Rows read: 208
Rows written: 0
Internal rows deleted: 0
Internal rows updated: 0
Internal rows inserted: 0
SQLCA:
sqlcode: 0
sqlstate: 00000
Nov 12 '05 #8
es********@hotmail.com (Evan Smith) wrote in message news:<23***********************@posting.google.com >...

Well, where does the SORTING come from? Both sql stmts should use TABLE
SCAN. Anyway it is very interesting, so I try to reproduce it. But failed. I
got the same exec time either using EE or EEE.


I'm just as confused as you are. I am including the output from the
DB2 event monitor for the two statements. These were both invoked from
the CLP. One of them shows sorting, the other doesn't. Perhaps one of
the more knowing event monitor gurus out there can see something that
I can't. (Caching doesn't seem to be in effect here either, as I get
similar results when I reverse the order of the operations.)

Here are the two entries:

126) Statement Event ...
Appl Handle: 239
Appl Id: 0A550712.F9CB.040726155050
Appl Seq number: 0001

Record is the result of a flush: FALSE
-------------------------------------------
Type : Dynamic
Operation: Close
Section : 201
Creator : NULLID
Package : SQLC2D03
Cursor : SQLCUR201
Cursor was blocking: FALSE
Text : SELECT * FROM db2admin.EWM_CASE fetch first 1 rows only
-------------------------------------------
Start Time: 07-26-2004 11:51:30.019546
Stop Time: 07-26-2004 11:51:45.155254
Exec Time: 15.135708 seconds
Number of Agents created: 1
User CPU: 13.690000 seconds
System CPU: 0.010000 seconds
Fetch Count: 1
Sorts: 1
Total sort time: 7979
Sort overflows: 1
Rows read: 1877
Rows written: 1991954
Internal rows deleted: 0
Internal rows updated: 0
Internal rows inserted: 0
SQLCA:
sqlcode: 0
sqlstate: 00000

144) Statement Event ...
Appl Handle: 239
Appl Id: 0A550712.F9CB.040726155050
Appl Seq number: 0001

Record is the result of a flush: FALSE
-------------------------------------------
Type : Dynamic
Operation: Close
Section : 201
Creator : NULLID
Package : SQLC2D03
Cursor : SQLCUR201
Cursor was blocking: FALSE
Text : SELECT * FROM db2admin.EWM_CASE
-------------------------------------------
Start Time: 07-26-2004 11:51:45.243032
Stop Time: 07-26-2004 11:51:45.297224
Exec Time: 0.054192 seconds
Number of Agents created: 1
User CPU: 0.000000 seconds
System CPU: 0.000000 seconds
Fetch Count: 208
Sorts: 0
Total sort time: 0
Sort overflows: 0
Rows read: 208
Rows written: 0
Internal rows deleted: 0
Internal rows updated: 0
Internal rows inserted: 0
SQLCA:
sqlcode: 0
sqlstate: 00000


The first query taking (14 seconds) seems to have a sort which is
spilling to the disk. I would suggest looking at the explains
(db2exfmt) and have a look at the cost associated with the sort
operator and the one ABOVE that. if the cost increases for the
operator ABOVE the sort, I would suggest increasing the sortheapsz.
....howevever...let me know if it worked cause I am still not sure why
the sort is taking place..
Nov 12 '05 #9

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

Similar topics

8
by: Jo | last post by:
Hi, I am using a OleDB provider that connects to ANY datasource - I do not know if these have some nice ID that I can page on. Using the OleDataAdapter FILL method. I then fill the dataset...
3
by: Franz | last post by:
Hi, If my ProjectTable contains a lot of rows, each time I call the following code, will all the rows be fetched? Or only those 5 records for that page are fetched only? /* -- code start --...
26
by: GreatAlterEgo | last post by:
Hi, This is my query which is embedded in a COBOL program. EXEC SQL SELECT DATE, AGE, DURATION, AMT INTO :LDATE, :L.AGE, :L.DURATION, :L.AMT FROM TAB1 WHERE CODE = :KEY.CODE AND...
5
by: cdtsly | last post by:
Hi i have a table with all value at 4 i select all lines in a fetch i update one with a value of 7 i update all the row in the fetch with a value 5 the result is that all my row are at 5 and...
9
by: Emin | last post by:
Dear Experts, I have a fairly simple query in which adding a where clause slows things down by at least a factor of 100. The following is the slow version of the query ...
23
by: PeterOut | last post by:
If I had code like this. unsigned short usLimit=10 int a, i; for (i=0; i<(int)usLimit; ++i) { a=(int)usLimit; }
3
by: salad | last post by:
I have an A97 application that is NOT split on a network. It is used by 15+ folks continually. It is quick and fast. I split it several years ago and had to merge it together again after the...
15
by: jim | last post by:
Maybe I'm missing something, but it doesn't look like Microsoft writes a lot of apps in .Net (although they certainly push it for others). What does MS write using pure .Net? If applications...
5
by: Lennart | last post by:
Here is a problem I would like some opinions on. I have a stored procedure (I suspect that the problem exists for other procedures as well, but I haven't verified it). First time it runs (each day)...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

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.