Why does FETCH FIRST slow down query?

[Evan Smith]

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

[Mark A]

"Evan Smith" <es********@hot mail.com> wrote in message
news:23******** *************** ***@posting.goo gle.com...

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?

[Philip Nelson]

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

[Fan Ruo Xin]

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********@hot mail.com> wrote in message
news:23******** *************** ***@posting.goo gle.com...

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 Smith]

"Fan Ruo Xin" <fa*****@sbcglo bal.net> wrote in message news:<YS******* **********@news svr15.news.prod igy.com>...

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

[Mark A]

> 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

[Fan Ruo Xin]

"Mark A" <no****@nowhere .com> wrote in message
news:4c******** *******@news.us west.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.

[Evan Smith]

>

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.0 40726155050
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_CA SE 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.0 40726155050
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_CA SE
-------------------------------------------
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

[Passcore]

es********@hotm ail.com (Evan Smith) wrote in message news:<23******* *************** *@posting.googl e.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.0 40726155050
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_CA SE 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.0 40726155050
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_CA SE
-------------------------------------------
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..