A few thoughts about the query execution time:
1. It seems to me, only a really dumb DB engine should care that the index has a secondary column... the column specified in the WHERE clause is first in the index, which means the index (an ordered set of row pointers) is ordered first on that column, then (where the first column gives duplicates) on another column.
2. If there are not an appreciable number of rows in the database, the engine may (rightly) not bother with the index.... it is sometimes faster to do a sequential table scan than to hop back and forth from a small index to a small table.
3. The same point applies as in #2 if the database thinks
there are not many rows in the table... in order for it to make an informed decision about the use (or not) of the index, it has to have up-to-date statistics. For Oracle, that means you need to analyze the table. For Informix, it's "update statistics". For MS SQL, it's probably control-F1-right-click-the-pretty-button.
4. Sometimes, the "value in (val1, val2, val3)" can be enough to convince an engine to skip the index - I'm not clear as to why, if the list is small, the engine doesn't just translate it to the equivalent "(value = val1 or value = val2 or value = val3)", which has sometimes (anecdotaly) seemed to use an index when the IN clause has not.
5. Some (most? I don't know...) DBs have an option that will give you some debugging info about how the DB engine will process the statement, along with an estimated "cost". The decision for a certain approach over another is based on the cost, and the cost is based on the accuracy of the statistics.... see #3. Then there's the other point, based on the original post:
... then, why are we all discussing query execution time?
There is no difference in Query Execution Time for both queries.
Business Logic Time is huge in second case as compared to first one (ratio - 1:20).
Rows returned are more in first case as compared to second case.(ratio - 1:4)
6. Unless the problem is truly in the business logic (whatever the real code is in place of STORE_ARRAY[x][y] = rs.getObject(i)), I can't see any reason why returning fewer rows should result in longer operation iterating through the ResultSet. If anything, the query execution time
should differ, and the larger dataset should result in more "pages" from the DB, but the smaller should run faster in the java code
. Can you verify that is really what you meant to say? That the smaller result set is actually taking 20 times as long to be processed in the java code
Hope any part of this helps...