Which of the two is better in performance for Oracle as Databse and using Spring JDBC Template for persistence. Leave aside memory constraint from application point of view assuming that dataset 1, 2, 3, 4 will be within limit for java data structures.

As it currently stands, this question is not a good fit for our Q&A format. We expect answers to be supported by facts, references, or expertise, but this question will likely solicit debate, arguments, polling, or extended discussion. If you feel that this question can be improved and possibly reopened, visit the help center for guidance.
If this question can be reworded to fit the rules in the help center, please edit the question.

I know IN clauses are expensive, so if the range is KNOWN to be sequential, perhaps "select * from Table where ( id >= 1) and (id <= 4)" is better than either of those?
–
Bill JamesMar 2 '13 at 17:40

no, these id are being created by multiple threads and they will not be in order.
–
fortmMar 2 '13 at 17:47

1

@BillJames The predicates section of an explain plan shows that Oracle converts IN to =. For the same amount of data, they should perform the same.
–
Jon HellerMar 3 '13 at 0:05

Here you can see the overhead of sending four times more statements towards the database counts the most. There is some variation possible here and there, depending on index depth and clustering factor, but the one query versus four queries will remain the biggest difference.

So, hopefully, readers of this thread won't say "IN clauses are known to be expensive" anymore :-)

I agree. One single statement is always faster than 4 individual ones (if they retrieve the same total amount of data).
–
a_horse_with_no_nameMar 2 '13 at 17:37

2

I think this fails to take into account anything to do with caching, connection pooling etc. or the fact that IN clauses are much more expensive than a regular equals clause. You may still be right, though, I just don't think it's cut and dried "fewer queries are better than more".
–
Bill JamesMar 2 '13 at 17:39

I got confused over this because I read that Spring Batch framework uses a "Driving Query Pattern" where they go the less intuitive way to querying for each Primary key rather in a list.
–
fortmMar 2 '13 at 17:41

In my case, database is being served from dbcp pooling and is managed in Spring , so I believe reconnection should not be a bottleneck.
–
fortmMar 2 '13 at 17:44

1

@fortm: but re-parsing the query and sending it over to the server can limit scalability. You have much more network roundtrips using four statements than you have with a single statement.
–
a_horse_with_no_nameMar 2 '13 at 18:01

In Oracle, using the PreparedStatement will allow Oracle to cache the execution plan generation for the SQL. So passing ID of 1 or 2 or 1000 will use the same cached Oracle plan.

Oracle will treat

select * from TABLE where ID in (1, 2, 3);

and

select * from TABLE where ID in (4, 8, 15, 16, 23, 42);

as two distinct queries and generate different execution plan for both. Thus type one is preferable from an Oracle standpoint.

In reality, if you place an index on ID, you will not notice a material difference between the two styles.

As always with performance tuning, "Don't guess, do test". Use YourKit or even System.currentTimeMillis() to see the real world numbers. Also do not sacrifice code clarity for negligible performance gains.

I think that this question is a good example of premature optimisation and why it should be avoided.

You are trying to get the rows for a number of IDs from a table. The obvious query to use should be the one that meets the requirements in the most simple manner. Unless there is a very good reason, I would always look at using:

Select * from Table where id in ( 1, 2, 3, 4 );

This makes it obvious to later developers what is going on and why. It also leave to the task of optimisation to the database to handle, which is what it is there for.

If after writing this code you find that there are performance problems then it is the time to look at optimisation.

For any sort of code problem the simplest solution is usually the best.

I do realise that I'm a little off-topic here and don't have a lot of knowledge JDBC but do have a lot of Oracle knowledge