Are JDBC select queries through a single connection less efficient that a proc containing those queries?


New Member
If I open a single JDBC connection (for Oracle), and execute multiple select queries, will it be less efficient than calling a procedure that executes those queries, and returns the result in cursors?

Edit: Sample queries are:
select id, name from animals;
select * from animal_reservoir where animal_id=id;
(The actual first query would be quite complicated, and the id returned would be used as an input multiple times in the second query. As such, the first query will be inefficient to use as subquery in the second query. Also, the queries can't be combined.)


Thành viên BQT
The two main differences are

  • fewer roundtrips (important if there are many small queries, otherwise not so much)
  • no need to send "intermediate" results (that are only needed for the next query, but not in the end) back to the client
How much of an impact this has completely depends on the application.

And often, there may be other alternatives (such as issuing different kind of queries in the first place; someone mentioned a JOIN in the comments -- or caching -- or indexing -- or data denormalization -- or ... ) to consider as well.

As usual, do what feels most natural first and optimize when you find there is an issue.