Re: Performance problem on initial call of stored procedure
- From: Birgitta.Hauser@xxxxxxxxxxx
- Date: 27 Aug 2006 08:38:16 -0700
Hi,
I don't think you can gain much performance with the first and second
execution. It's the behaviour like SQL works.
The initial and the second executions will be slower (even with the
poper indexes created) than subsequent executions. The first time you
call a SQL-Statement in a job (or connection), the query optimizer
creates or validates access plans. If the SQL-statement is executed
with the SQE (SQL Query Engine) an access plan may be stored in the
plan cache and can be validated. For a static embedded SQL-Statement
access plans get stored in the program objects and can be validated.
For dynamic embedded SQL no access plan is stored in the program object
and must be created from scratch (assumed no access plan in the plan
cache can be validated). With extended dynamic SQL an access plan may
be stored in an SQL package and can be validated.
If no access plan can be validated, it must be created from scratch. An
access plan only describes how to access the data, but does no access.
Based on the access plan, a data path get built (using the optimal
indexes, building temporary objects or what ever is needed). After the
ODP (open data path) is opened, data are returned.
After the first execution the data path will be closed again.
With the second call the access plan get checked and confirmed again
and the will be opened again. After the second execution the ODP stays
open (provided it is reusable) and will be used for subsequent
executions without any access plan checking operations.
If you drop the connection or end your job or activation group or if
you program ends and you specified CLOSQLCSR *ENDMOD the ODP will be
hard closed. That means the complete optimization process must be
executed with the next execution.
When working with SQL you goal should be to reduce hard closes and
subsequent opens to a minumum.
Birgitta
jfinley@xxxxxxxxxxxxxxxxxx schrieb:
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
.
- Follow-Ups:
- References:
- Performance problem on initial call of stored procedure
- From: jfinley@xxxxxxxxxxxxxxxxxx
- Performance problem on initial call of stored procedure
- Prev by Date: Performance problem on initial call of stored procedure
- Next by Date: Re: Performance problem on initial call of stored procedure
- Previous by thread: Performance problem on initial call of stored procedure
- Next by thread: Re: Performance problem on initial call of stored procedure
- Index(es):
Relevant Pages
|
