Stored procedures vs. Dynamic SQL

[varshan09]

Hi,
We came to know the performance of stored procedures and parameterized SQLs(Dynamic queries) are same.

Do we use dynamic sqls mostly in our project? What is the overhead of using stored procedures in DB2?

Thanks
Varsha

 

[MarcLodge]

Stored procedures are quicker because they have had a BIND statement run against them. Dynamic SQL has to prepare the statements at execution time which incurs an runtime overhead. Dynamic SQL is normally used when certain important factors (like table name) are not know when the program is compiled etc.

Marc

 

[sathyarams]

If your application is built around stored procedures, porting to a different database product may become difficult ...

HTH

Sathyaram

For db2 resoruces visit

http://www.db2click.com

More DB2 questions answered at

http://www.dbforums.com/f8

 

[kathi]

I wouldn't assume that using stored procedures would make it more difficult to port your application. We've recently done this and in fact it's the SQL in the application that presented the bigger headache.

 

[cdlvj]

kathi,
I totally agree with you, as SP do make it easier to port. You simply rework that layer and you are ready to go.