MVSFORUMS.com

[s_shivaraj]

Board,
One of the online program currently has several insert stmts (say around 20 columns) for differnt table, this program is called around 10K or more times daily. We are thinking of using stored procedure for doing the above inserts, to improve the performance.

Like to know how much stored proc can really help us in improving the performance. What are the factors i need to work before going for stored procedures.

Note :- Each insert is happening in a seperate transaction , which has totally differnt set of values.
_________________
Cheers
Sivaraj S

'Technical Skill is the Master of complexity, while Creativity is the Master of Simplicity'

[vivek1983]

s_shivaraj,

The below link might be useful in getting information about stored procedures:

http://publib.boulder.ibm.com/iseries/v5r1/ic2924/index.htm?info/sqlp/rbafymst02.htm

Regards,
Vivek G
_________________
Vivek G
--------------------------------------
A dream is just a dream. A goal is a dream with a plan and a deadline. (Harvey Mackay)

[vivek1983]

s_shivaraj,

You can also check the below link for more information:

http://publibz.boulder.ibm.com/cgi-bin/bookmgr_OS390/BOOKS/DSNAPH11/6.2?DT=20010710165542

Vivek G
_________________
Vivek G
--------------------------------------
A dream is just a dream. A goal is a dream with a plan and a deadline. (Harvey Mackay)

[jsharon1248]

I don't see how a stored proc is going to improve the performance. There is a cost to invoking a stored proc. I'd be very careful and take measurements before I'd convert an existing program with INSERTs to a stored proc.

[Manas Biswal]

Agree with jsharon1248. Don't see a big performance improvement unless the INSERTS are invoked from a remote client.

Regards,
Manas
_________________
There is no path to peace. Peace is the path.
- Mahatma Gandhi (1869-1948)