Better option to fetch results from database tables
Are there any performance improvement in calling a procedure which returns SYS_RECURSOR or call a query?
CREATE OR REPLACE PROCEDURE my_proc ( p_id number, emp_cursor IN OUT SYS_REFCURSOR ) AS BEGIN OPEN emp_cursor for select * from emp where emp_number=p_id end; /
and call the above from Java by registering OUT parameter,pass IN parameter and fetch the results.
From Java get the results from emp table by
preparedStatement = prepareStatement(connection, "select * from emp where emp_number=?", values); resultSet = preparedStatement.executeQuery();
Which one of the above is a better option to call from Java?
There is no performance difference assuming your prepareStatement method is using the appropriate type for all bind variables. That is, you would need to ensure that you are calling setLong, setDate, setString, etc. depending on the data type of the parameter. If you bind the data incorrectly (i.e. calling setString to bind a numeric value), you may force Oracle to do data type conversion which may prevent the optimizer from using an index that would improve performance.
From a code organization and maintenance standpoint, however, I would rather have the queries in the database rather than in the Java application. If you find that a query is using a poor plan, for example, it's likely to be much easier for a DBA to address the problem if the query is in a stored procedure than if the query is embedded in a Java application. If the query is stored in the database, you can also use the database's dependency tracking functions to more easily do an impact analysis if you need to do something like determine what would be impacted if the emp table needs to change.
Well, I don't think there is major significant difference from the Java invocation standpoint.
Some differencesI can think of are:
- You will now have to maintain two different code bases: your Java code and your stored procedures. In case of errors, you will have to debug in two different places, and fix problems in two different places.
- Once production-ready, making changes to the database is probably going to require some additional formalisms besides those required to change the Java code deployed.
- Another important matter to take into account is database-independence, if you are building a product to work with different kinds of databases, you would be forced to write different versions of your stored procedures and you will have more code to maintain (debug, bugfix, change, etc).
- This very important if you're building a product that you intend to deploy in different environments of different (possible yet unknown) clients, wich you cannot predict what RDBMS will be using.
- If you want to use an ORM framework i.e. Hibernate, EclipseLink) it will generate pretty optimized queries for you. Plus, it would be more difficult to integrate it later on if you use stored-procedures.
- With proper amount of logging is easy to analyze your queries for optimization purposes. You could use JDBC logging or the logging provided by your ORM provider and actually see how the query is being used by the application, how many times, how often, etc, and optimize where it matters.