Performance problem on initial call of stored procedure



It's been about 4 years since I've worked with the AS/400, so I'm a
little rusty.

The problem statement:
The initial call to a stored procedure that performs 3 sql select
statements and returns 3 cursors is incredibly slow. The 2 tables that
are referenced in the statements are pretty large, with 900k records in
one and 1.8 million records in the second one. The initial execution
can take upwards of 90 seconds with subsequent calls being less than 2
seconds. The span of the date selection, with a range that can go back
to 2000, does not seem to significantly affect the execution time. If
you stop and restart the websphere server, it will present the initial
performance problem. If you let the session set for around 5 minutes,
you get the initial performance problem again.

The websphere configuration is running on a Windows NT machine and,
tbph, responds AWESOME, except for the calls to the 400.

The datasources are configured to hold the java calls in a package.

Investigation so far:
Well the performance was much worse but so far I've built the suggested
indexes from doing the STRDBG and prototyping the statements in the
interactive SQL sessions on the 400. I've chopped about 50% of the
time from the initial problem and have gotten the subsequent calls to
the DB, for the session, to sub-second response. But the first call
performance is still above 30 seconds.

The question:
What can be done on the 400 to increase the initial performance to the
initial stored procedure call?

What can be done in the SQL procedure to increase the call, I'll try
rewriting for logic/design on Monday, but is there a coding
"trick"?

What can be done in the Websphere configuration to increase the
performance?


Thanks,

Jeff

.