Way to speed up first invocation of stored procedure?
Can you help? An iSeries user recently wrote the editors of Search400.com with this question:
"One of our stored procedure takes approximately 1 minute for the first invocation when I do a call using RUNSQLSTM. If the same procedure is called repeatedly with different parameters, the results come back within one or two seconds. Is there a way we can reduce the warm up time for the stored procedure?"
-- Michelle Davidson, editor, Search400.com
Software/Hardware used:
Software/Hardware used:
ASKED:
September 13, 2005 1:21 PM
UPDATED:
November 10, 2009 3:55 AM
Answer Wiki:
John Paul Lamontre said this is not an easy question. He writes:
First, run SQL under
STRDBG
(without any parameter).
You will have a complete description of the SQL optimiser process. Inspect in detail each message, checking for a missing index or other requirements of SQL.
If second runs are quicker, SQL has probably delayed the destruction of a temporary index.
I have encountered thie problem sometimes, but was unable to find an explanation.
To see all answers submitted to the Answer Wiki: View Answer History.
Without knowing a lot about the environment and the stored proc, it’s almost random guesswork.
Are the files SQL tables or DDS? What kind of stored proc is it? SQL? (REXX?) What does it do? What does debug show? Are proper indexes defined? What do the tables/files look like? What columns/fields are accessed?
What kind (and how much) of performance tuning has been done to the system overall? Have memory pools been properly defined and categorized? Have sufficient disk arms been given to the system? Does sufficient memory exist?
Tom