Just calling on your collective wisdom here. I've noticed a query on one of our Prod systems which is taking up around 30-40% of the total execution time during the online day. It runs 3-500 times per hour and it seems to be one that comes from a web application of some sort. It sounds worse than it is, but as we've tuned other stuff, it's now become more obvious.
I've done explains, and had a play around looking at indexing, and there is an index which helps slightly (maybe 5% improvement in the cost estimate), but I think the chief issue is that it appears that the query has two optional parameters which are only used in the WHERE clause.
WHERE ( ? LIKE '' OR ITEM_KEY >= ? )
AND ( ? = 0 OR ITEM_NO = ? )
ORDER BY ITEM_KEY ASC, ITEM_NO ASC
FETCH FIRST 100 ROWS ONLY
ITEM_KEY is CHAR(20) NOT NULL
ITEM_NO is INTEGER NOT NULL
There is no ITEM_NO=0, and ITEM_KEY >= '' would just return the first 100 rows.
Traditionally, we could look at doing a dynamic query, or having logic in the code to decide which version of the query to run depending on which fields had values, but coding for performance is old-fashioned (aka too hard) so I'm not optimistic of getting code logic rewritten to deal with it.
But, just looking at the WHERE clause, and the data, it seems to me it could be simplified to just:
WHERE ( ITEM_KEY >= ? OR ITEM_NO = ? )
Have I missed something in the logic here? It's making my head hurt :)
I'll still have to run it past the Dev team, but I don't want to suggest the change if I've missed something obvious.
Testing the query explain with that change to the WHERE clause reduces the estimated cost by 20%, which I will call a win if we can do it.