open stock_list
do until(stock_list.end_of_cursor) {
fetch from
stock_list cursor
into
symbol
select
new_price = LT_PRICE
from
LAST_TRADE
where
LT_S_SYMB = symbol
We spend a lot of time trying to optimize these types of OLTP transactions and in certain cases you may be better off using this structure than a more esoteric, less optimized construct.Edit: I guess I should mention that the cursor in there is going to hurt. Just an example -- don't always actually do that and rely on us to speed up your transaction.
What is more amazing, is I think a number of projects I worked on as a performance tester had no developer on staff or team for a large enterprise software product who could make a determination that the type 2 error had occurred. I think this was mainly the result of having only background in a specific tech stack (I saw lots of low-hanging opportunities for doing set operations in the database rather than pulling stuff in with J2EE or even in db code written in an imperative style . . . <shudders when thinking of T-SQL in legacy product> ).
Optimizations should really only be made at the Type 1 level. I'm all for cleaning up and refactoring code. Your Type 2 example is really a Type 1 problem in disguise, since it's not the simplest way to do it! If you want to do a Type 2 change, measure it and prove to yourself it's worthy of your time. Just solve the problem simply and cleanly and hope for the best. The huge performance hogs are almost never where you expect to see them anyways.
1. Things that need to be "cleaned up".
2. Things that never should have been written in the first place.
Simple example of Type 1: You rush to get something up and running, and in your first code review, you find the exact same code multiple times. So you write a function, parameterize a few variables, tighten it up, and reference it all over the place. Cool.
Simple example of Type 2: You have an SQL SELECT inside an iteration. At 500 iterations it runs smoothly. At 50,000 iterations, it becomes non-functional. Your only hope to scale this thing is to rethink the whole process to run with one SQL SELECT (and maybe a database redesign) outside the iteration. You basically have to start over. What were you thinking?
You need to trust your "process" that Type 1 things will rise to the surface in due time, thus avoiding premature optimization.
For Type 2 things, there is no such thing as "premature optimization". They need to be designed and written properly in the first place.