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

.



Relevant Pages

  • Re: Performance problem on initial call of stored procedure
    ... The initial call to a stored procedure that performs 3 sql select ... If you let the session set for around 5 minutes, ... The websphere configuration is running on a Windows NT machine and, ...
    (comp.sys.ibm.as400.misc)
  • Re: Performance problem on initial call of stored procedure
    ... The initial call to a stored procedure that performs 3 sql select ... If you let the session set for around 5 minutes, ... The websphere configuration is running on a Windows NT machine and, ...
    (comp.sys.ibm.as400.misc)
  • SessionID length differs between .NET and SQL Server
    ... I have my Web site set up to use SQL Server session state. ... When a session starts, a row is created in ASPStateTempSessions. ... procedure TempRemoveStateItem to clean up after myself (not sure if this is ... Because the values do not match, the stored procedure ...
    (microsoft.public.inetserver.asp.general)
  • Re: Views vs Stored Procedures, whats the difference?
    ... I hope you are not suggesting you embed SQL queries into the application? ... A stored procedure logic will be exactly as fast as the algorithm you ... I understant that SQL Server supports hints. ... implementations (nestedloop, merge, hash, ..) on decent sized tables, then ...
    (comp.databases.ms-sqlserver)
  • Re: Stored Procedures - Patterns and Practices
    ... >published the reasoning behind its opinions. ... I disagree that the debate in SQL Server related discussion forums ... If the natural key is long or spans too many ... I want to call a stored procedure that adds a customer ...
    (microsoft.public.sqlserver.programming)