poor select performance

[AC Slater]

I have 1 table (out of many) that has very poor performance when performing
a select into on. The select statement is called multiple times. We've
found each call to take almost 1 second... we were expecting less than .1
seconds.

The select stmt looks like:

select key,data into key_buff, data_buff from tablename where key > curr_key
AND key < max_key ORDER by key FETCH FIRST 1 ROW ONLY;

Any idea to troubleshoot performance ?

Thanks

[Mark A]

"AC Slater" <no***@yahoo.co m> wrote in message
news:on******** *************@n ews4.srv.hcvlny .cv.net...

As Returned by the db2expln the SQL is:

SELECT LFKEY, CDATA Into :H0004 :H0005, :H00010 :H00011
FROM RITABLE
WHERE LFKEY > :H0004 :H0005 AND LFKEY <= :H00012 :H00013
ORDER BY LFKEY
FETCH FIRST 1 ONLY

That portion of the db2explan is the same on both (the good and bad system). The access plan below it is what differs.

/Frank

I noticed that the index was clustered in the first one and not clustered in
the second one. If the table and clustered index is dis-organized, this
could be a problem, but not sure. Do reorg, runstats, and rebind.