Dynamic SQL Performance Question

What are the performance benefits of using stored procedures versus dynamic sql?

Developers here initially put SQL directly into our Java Server Pages (JSP) and use JDBC statements to access the database. Example:

  String SQL = "select HANDLE from USERS where USER_ID='" + userID + "'";
  myResultSet = stmt.executeQuery(SQL);

For performance reasons, I'm looking to replace this dynamic sql with stored procedures. As we're in development now, it's hard to tell how the cost of this dynamic SQL (i.e. small number of records, limited number of users, etc.).

I understand the main cost comes from a) increased parsing and b) inefficient use of the library cache.

How big a benefit can one expect when moving from dynamic sql to stored procedures?

bill milbratz

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Wed Apr 12 2000 - 00:00:00 CDT