Stored procedure versus Java Selects


Performance and scalability: Stored procedure versus Java Selects

  1. Stored procedure versus Java Selects (15 messages)

    There seems to be a growning trend to use Stored procedures calls for any database activity. That way the Java code will always call SP for any update/insert/deletes/selects.

    Perhaps its understandable for updates/deletes/inserts for audit purpose. But why Selects? The argument against it is from maintainbility standpoint.

    1. Using static binding in Stored procedure leads to repetition of SQL query in SP. A typical case could be because results need to displayed ASC and DESC order. Thats leads to duplicating SQL query except for "ORDER BY" clause.

    How does this compare in terms of Performance against firing SQL from Java program using PreparedStatement and Statement. Does anyone have numbers ?

    2. One can argue that using dynamic binding in SPs will avoid SQL repetition problem.

    But then how does it perform against firing SQLs from Java program ?

    Any thoughts ?

    Threaded Messages (15)

  2. Abstraction[ Go to top ]

    One reason i can think of why you would use stored procedures for select statements is abstraction of the database structure.

    For example, If you want to select the id, name and salary of an employee you would put the SQL statement "SELECT ID,NAME,SAL from EMP" in your code.

    The table and column names are now hard coded in your application. If in the future the database structure changes, say column SAL becomes SALARY or the table name changes to employee, then you must change all the select statements in your application.

    If you use a stored procedure on the database you only call the procedure and you don't have to worry about the underlying database structure. The only thing that matters is that the procedure returns records containing id, name and salary.

    If you got a couple of SQL statement in your code, no problem. But applications grow bigger in time, and it can get complex to maintain al the SQL statements in your code.

    But it always stays a matter of opinion. My experience is that if you talk to a database oriented person they will say stored procedure an if you ask a java/oo oriented person they are more likely to say in the java code.

    What RDBMS do you use? Performance is largely an RDBM (and hardware of course) issue i think. I am currently developing for an Oracle 9i database on an IBM RS6000 platform, with all the bells and whistles Oracle developed over the years. It doesn't even break a sweat for a query over 100.000 records with multiple joins. It uses al kind's of optimizers and caches to boost performance. I have some experience with MySQL (3.something i believe) and you can't expect the same performance.

    But if someone else can say more about the difference in performance between java SQL statements & stored procedures, i am also very interested in the numbers.

    Hope my comment is of some help

    PS. A nice piece about business logic & PL/SQL vs Java is found on Oracle's Guru page Ask TOM:
  3. Abstraction[ Go to top ]

    One reason i can think of why you would use stored procedures for select statements is abstraction of the database structure.
    You can use views for abstraction, too.
  4. Abstraction[ Go to top ]

    One reason i can think of why you would use stored procedures for select statements is abstraction of the database structure.
    You can use views for abstraction, too.

    Yes, view can abstract in some extends. However, abstract is only one of the benefits.

    Here are other key benefits of using stored procefures:

    1) Separation of concerns.
    Using a stored procedure is like programming based on contracts via APIs. Given that the DB developers know more about the database than other non-db developers, these DB developers can write better SQLs. (Believe me not every developers can write a high performancing SQLs. DB developers should be able to write better SQL than most non-DB developers.) Also, the DB developers can work with the DBA to tune the SQL, DML, DDL, etc, using every database tuning techniques.

    2) performance & scaleability
    Stored procedures (well, at least for PLSQL) are MUCH more faster than simple SQL. It is a BIG myth that stored procedures are slower than regular SQL statement.

    It is true that stored prodecures have overhead. However, they only occur on initialization. Once they are initialized, they are much more faster than similar simple SQL or calleable statements. You can prove this by following these steps:

    1) start up the SQL statement or stored procedures
    2) execute step#1 statement (i.e. to warm it up to have soft parse later)
    2) start timer
    3) execute step#1 statement in a loop (e.g. 1,000 times)
    4) stop timer

    If you use simple SQL statement, you will have at least 1,000 soft parses.

    If you use stored procdures, you will no parse, not even a soft parse, because Oracle cache the cursor in PLSQL (i.e. cursor sharing).

    Are more explanation:

    a) PLSQL reduces parses. For static statements (i.e. bind variables that don't have SQL string concatenation), Oracle parses these statements once and cached them. Even if you close the cursor in PLSQL, Oracle will automatically management the cusrsor for you. So, the next time you call the stored procedures, Oracle does not have to parse the statements again.

    b) reduce multiple network calls to the database (like a facade design).
    Stored procedures can bundle multiple statements together. Instead of calling multiple statements causing many network round-trips, you can just call the stored procedures with only one network call.

  5. Well[ Go to top ]

    Sir, I fundamentally disagree with your assumptions
    The table and column names are now hard coded in your application.
    Its good practice to place SQL in config files (usually) rather than hardcode them. In terms of abstraction, config files or stored procs are equally effective.
    If in the future the database structure changes, say column SAL becomes SALARY or the table name changes to employee, then you must change all the select statements in your application.
    Presuming an application actually uses the data it gets, the application code will have to make reference to column names (or indexes) at some point:

  6. Regarding the abstraction thing - one can argue that select statements would be abstracted using Data Access Object patterns. That way they remain in only one layer and are not "proliferated".

    We use DB2 as backend and use cobol stored procedure.

    I'd really be interested in getting some performance numbers if someone has them.
  7. Performance[ Go to top ]

    Maybee this article is of some help:

    It is an oracle bases article with some metrics on the oracle JDBC drivers. I can't tell you if the same principle applies toe DB2 and cobol. It states that for simple SQL statements stored procedurs are slower in java then regular SQL due the overhead of stored procedures.

    A simple way to find out the performance of your sql code can be:

    Date before = new date();

    // Your code

    Date after = new date();

    long lbefore= before.getTime();
    long lafter= after.getTime();
    long extime= lafter-lbefore;
    System.out.println("Miliseconds: " + extime);
  8. If I recall correctly, MS SQL Server could not cache SQL statements (parse tree and execution plans) unless they were in a stored procedure - even if you used a PreparedStatement. So encapsulating them in a stored procedure was a great performance boost. But that was 2-3 versions ago and is certainly not needed anymore...
  9. Kind of a silly one but, perhaps, consistency, and not abstraction, is the answer. If you are already keeping insert/update/delete activity in sprocs, why not keeps select statements there as well? Why have two separate places for SQL code? In a way, this might help offset the other costs to maintainability by keeping things in one place and, well, consistent.

    Just being rhetorical...
  10. Think about having to repeat that query for every condition for every sort order. Add to the mix that I cant parameterize "fetch" size in DB2 with static SQL SPs.

    Also generally, selects could get really complex unlike update/inserts/deletes. Pages of SPs with repeated SQL code. Want to add/update/remove a "where" clause - change 10 places.

    Does "consistency" really offset maintainbility there ?

    To me, "performance" may be (depending on gains) but definately not consistency.
  11. To convince you it's going to be hard. You probably write very good SQL and you know how to tune with SQL hints. That was me 6 months ago. I'm a firm believer now in dumping the SQL queries because the stored pocedure queries are effective performance-wise after about 65 executions using callable statements and bind variables.
    What happens is that Oracle will create and remember this execution plan and that increases performance. If you hard code statements, the plan is not remembered.
    Then there's the maintenance issue. Should the java developer be contacted everytime a schema is revised? No. But when packages and procedures are created, the Oracle developers can inform users (java developers) that the database API has changed. Who knows who's running what SQL. And SQL is not just run, it's hand-coded all over.
    I was a hard nut to crack on this but I firmly believe that the stored procedures and functions are more of a binding contract. I am an Oracle developer as well as a J2EE one, and it's hard to give up what you know.

    Piratepete aka. David L. Whitehurst
  12. Your tone is extremely patronizing.

    I'd like to see some evidence for your '65 executions' claim.

    Normally its Java developers who are responsible for coding queries, whether as Stored Procs or app code, so I'm not convinced by our second point.
  13. I had a chance to perform a study for a client who (I kid you not) had about 600 Oracle stored procs that did nothing but running single select statement. I was not able to detect any performance/memory differences between running one of those procs and running a prepared statement with bind variables provided you use proper (matching) versions of JDBC drivers, etc.
    Whether to use SPs really depends on environment, customs and history of the app.

    The arguments AGAINST stored procs in J2EE project would be:

    It is easy to let business logic creep into the stored procedure and you are going to have to maintain the logic in both SPs and Java code;

    Stored procedures language (even as good as PL/SQL which I did for 12 years) is not nearly as powerful and "expressive" as Java and SP logic tends to grow and become clogged up and hard to maintain very quickly;

    Stored procs are "Stored" in the database so they always exist in two places: file on the file system and in version control and the copy actually running in the database where it can be altered at will. It takes great deal of discipline and coordination with DBA(s) to absolutely make sure what the hell runs in the given environment at the given time. It is much easier with Java when what you have in version control is what you get;

    Even if you keep all selects in SPs, your java code is still "polluted" with stuff that has to deal with ResultSets, Connections, Statements, etc. In properly designed apps it still has to be abstracted out and hidden so you still going to have database specific classes, fixtures, etc.
  14. Stored Procedures have a couple of advantages for read-only queries:

    - Data access logic can be maintained in one place and re-used across multiple client applications (these could be Java apps or other languages)

    - There are often performance improvements from using Stored Procedures as the SQL is pre-compiled


    Andy Grove
  15. I would imagine some performance gains using Stored procedures. Someone on this thread referred to a nice article that provides numbers for SQL SPs on Oracle.

    Can I safely assume the same numbers for COBOL Stored procedures ? Am thinking they'd be slower than SQL stored procedure. Does that nullify or reduce the gains ?
  16. SP[ Go to top ]

    IMO the biggest problem with SP is that they can move the bussines logic from app server to DB

    app servers have very good horizontal scalability and relatinal DBs still not (maybe Oracle 10grid has; any comments?)

    therefore we are trying to move as much work as possible from DB machine to app server machines