RE: SQL Stored Procedure

We have a 3rd party designing applications using Visual Studio and are
experiencing some performance issues.

Well, then you need Centerfield tooling of course :) <Disclaimer: I work
for Centerfield>

They suggested that creating stored procedures might resolve the
performance issues.

It may help, depending on where the problem is. Doing it the way you started
with dynamic SQL will buy you nothing in terms of performance.
Having (multiple) static SQL statements in a stored procedure is where
you'll see performance gains (fewer trips over the network and statically
optimized SQL statements).

They suggested passing the SQL Statement as a parameter to the
procedure.

Bad idea all around, for performance and security reasons (SQL injection).

Based on your first statement, I think you may be able to get away with some
basic SQL performance tuning (i.e. build the 'perfect' SQL indexes, rewrite
some of the 'bad boy' SQL statements and like). Analyzing SQL performance
metrics may also point to poor client-side application design (i.e. an SQL
statement being invoked umpteen million times with the same selection
criteria by the Visual Studio developer).

Others have pointed you to some good redbooks on the subject. I'll add one
more, "OnDemand SQL Performance Analysis Simplified on DB2 for i5/OS in
V5R4":
http://www.redbooks.ibm.com/abstracts/sg247326.html?Open

Elvis

Celebrating 11-Years of SQL Performance Excellence on IBM i, i5/OS and
OS/400
www.centerfieldtechnology.com


-----Original Message-----
Subject: RE: SQL Stored Procedure

Thanks Charles for the input.

We have a 3rd party designing applications using Visual Studio and are
experiencing some performance issues.

They suggested that creating stored procedures might resolve the
performance issues.

They suggested passing the SQL Statement as a parameter to the
procedure.

If you have a snippet that you could provide, that would be great.

I have looked and continue to look at the redbook for guidance.

Thanks.
James Salter