MVSFORUMS.com

[Jiten]

I have a program having many Single Select SQL queries, all having Fetch First Row. The Response time for the online program was near to 40 seconds. Someone suggested to Remove all of the Fetch First Row statements and instead handle multiple rows found in the program itself.
Doing that the Response time decreased to one-third (13 seconds).
Can someone throw some light, why with many Fetch first Row queries the program had poor performance.

[kolusu]

Jiten,


Do you have an ORDER BY clause along with FETCH FIRST? If you specify the FETCH FIRST n ROWS ONLY clause with the ORDER BY clause, the ordering is performed on the entire result table before the first n rows are returned.

also I would suggest that you use OPTIMIZE FOR 1 ROW only along with FETCH first

[Jiten]

Hi Kolusu,

I do not have an Order By Clause. I am simply checking the exisiting of record in the table.
To add to the previous post, for one particular query, The COST reduced from 14564 to 4485 with a change from Fetch First Row to handling -811 in the program.
No order by clause is there atleast in this query.

[dbzTHEdinosauer]

deleted by dbzTHEdinosauer, duplicated Kolusu's Post.
_________________
Dick Brenholtz
American living in Varel, Germany

[dbzTHEdinosauer]

Jiten,

I would guess that your WHERE predicates were not indexed.

You should spend some time with EXPLAIN before you code your program.

Kolusu's suggestion will be best when the WHERE predicates are indexed.

if they are not indexed and you don't care about howmany, just 0 or > 0,
you can SELECT 1 WHERE EXISTS which would stop the table scan after 1 find.

There are a lot of articles that can be found thru google about this subject, written between 2001 and 2005.

What version of DB2 are you using?

Next time 'someone suggests', ask them why.
_________________
Dick Brenholtz
American living in Varel, Germany

[Jiten]

hi dbzTHEdinosauer,

I am using DB2 Version 8. If you could explain the usage of SELECT 1 WHERE EXISTS....actually I did not get it. Usage of Exists I know, it will stop after getting the first find. But my actual question was why after removing the Fetch First Row the cost reduced.

[dbzTHEdinosauer]

Jiten,
I would guess that your WHERE predicates were not indexed, thus table scans.

Instead of copying the sql, I will give you the link of an article that really goes into a detailed explanation. This article is dated 2005, so it is not too out-of-date.
_________________
Dick Brenholtz
American living in Varel, Germany

[CICS Guy]

Thanks Dick,

Always an adventure following your bread crumbs........

[dbzTHEdinosauer]

CICS Guy,

i am only sharing what others have left for me. glad they are helpful.
_________________
Dick Brenholtz
American living in Varel, Germany