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]

Re: Why does FETCH FIRST slow down query?

"Evan Smith" <esmith2112@hot mail.com> wrote in message
news:23658335.0 407221145.5d38d eae@posting.goo gle.com...[color=blue]
> 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[/color]

Did you try a visual explain to see if the access path is different?

[Philip Nelson]

Re: Why does FETCH FIRST slow down query?

Evan Smith wrote:
[color=blue]
> 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[/color]

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]

Re: Why does FETCH FIRST slow down query?

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" <esmith2112@hot mail.com> wrote in message
news:23658335.0 407221145.5d38d eae@posting.goo gle.com...[color=blue]
> 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[/color]

[Evan Smith]

Re: Why does FETCH FIRST slow down query?

"Fan Ruo Xin" <fanruox@sbcglo bal.net> wrote in message news:<YS%Lc.270 3$od2.1076@news svr15.news.prod igy.com>...[color=blue]
> 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.
>[/color]


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]

Re: Why does FETCH FIRST slow down query?

> After more investigation I can offer the following:[color=blue]
>
> 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
>[/color]

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]

Re: Why does FETCH FIRST slow down query?


"Mark A" <nobody@nowhere .com> wrote in message
news:4cdMc.90$w x3.3591@news.us west.net...[color=blue][color=green]
> > 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
> >[/color]
>
> 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[/color]
is[color=blue]
>
> SELECT * FROM EWM_CASE FOR FETCH ONLY fetch first 1 rows only
>
>[/color]


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]

Re: Why does FETCH FIRST slow down query?

>[color=blue]
> 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.[/color]

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]

Re: Why does FETCH FIRST slow down query?

esmith2112@hotm ail.com (Evan Smith) wrote in message news:<23658335. 0407260752.9295 1@posting.googl e.com>...[color=blue][color=green]
> >
> > 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.[/color]
>
> 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[/color]

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..