Any ideas while I research would be appreciated.
Have a developer asked me to help him out on a performance issue. He has this query, he wants the data sorted on a column and to only fetch the first 10 rows. Simple right?
Now he has his query:
from this_table, that_table
where this_col = that_col
order by my_sort_col
fetch first 10 rows only
This runs in about 8 seconds elapsed. He then runs the exact same query without the FETCH FIRST and the results are instantaneous. I say, "oh, its because the data has already been fetched so its still in the bufferpools. He then runs the first query with the FETCH FIRST again, another 8 seconds elapsed.
I still have to do the explains on both, but thought I'd throw the question out here while I look at it closer.
Alrighty then,,, I ran the explains and there is a difference that I am really surprised by. I tried on the fetch first query to use OPTIMIZE for 2201, the number of rows that are actually in the resultset, but no difference in the access path. This is with an ODBC connection using AQT or DS, comes across the same.
The explains are even accessing the tables in a different order and using different indexes. I was just surprised as I had not seen(maybe noticed is a better term) this behavior previously.
I would check what kind of runstats are present (assess via section actuals), and check the impact of different queryopt values on the plan. Docs say the 'fetch first' clause 'can improve performance' which I understand as performance would be same or better, not the opposite.
yeah that's what I'm doing right now. These tables live on z/os so running runstats now with keycard and freqval statements
See if that will result in a difference. The other thing I've found this 2201 rows is almost all of the data in the main table and there are two other tables that have parental type relationship and he is joining to them to get descriptions of the values. Without the FETCH FIRST we are starting with one of the parent tables as a tablespace scan, then hitting the child using an index, with the fetch first its the reverse TS on child then hit the parent via index only. Still surprised the FETCH FIRST would result in a different access path.