Re: Dynamic SQL Performance Question

in our system using trace and tkprof we were able to see that 80-90% of the elapsed/cpu time for executing dynamic sql is taken up in the parsing phses. if the query is executed a few times a day its no big deal - if executed
10 times a second it would be prudent to use a stored procedure. IMO.

billmil_at_my-deja.com wrote:

> 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.

--

Tony Johnson
The Straford Group
PO Box 2875
Chandler, AZ 85244

Email : tonyj_at_primenet.com
Phone : (480)497-9685