Re: JDBC and Stored procedure performance problems

Hi Mark,

this is the normal behaviour of SQL. First time an SQL-statement is
executed, the query optimizer validates the access plan and searches
through all indexes (access path) for the used tables to determine the
optimal access path. After the SQL statement is executed.

The second execution may be faster, but the access plan and the access
paths are checked again, to confirm the decision of the choosen access
path or to determine an other one.

After the second execution the data path stays open (Open Data Path),
if it's reusable and gets reused by all subsequent executions.

I had no problem to explain our clients that the first execution will
take a little longer (30 seconds are not as much! Half an hour would be
an other problem).

Further the wait time may depend on the query engine that is used. I
assume that your SQL statements are executed with the CQE (Classic
Query Engine). With SQE (SQL query engine) the access plans for any SQL
statements are stored in system wide plan cache. For identical SQL
statements in different jobs the access plans in the plan cache are
found and used to create the open data paths.

Yet an other point that may slow down your performance. Never specify a
DDS described logical file in a SQL statement. The query optimizer will
not be forced to use its access path. All SQL-statements where DDS
described logical files are specified are rerouted to the CQE. This
rerouting may cost 10 - 15 % performance.

Birgitta

.

Relevant Pages

  • Re: SP runs slower than same query in Query analyzer. Why??
    ... Andrew your reply was useful but I'm quite new to sql server and I'm ... query gives me the results in 3-4 seconds. ... > Keep in mind that the server has to compile a complete execution plan ... > when compiling a plan. ...
    (microsoft.public.sqlserver.clients)
  • Re: Current SQL being processed for a job
    ... SQL statement that has been parsed by the query engine. ... It turned out that the query access plan was ... senza sapere, senza potere, senza con amor volere" ...
    (comp.sys.ibm.as400.misc)
  • Re: Performance problem on initial call of stored procedure
    ... It's the behaviour like SQL works. ... creates or validates access plans. ... For dynamic embedded SQL no access plan is stored in the program object ... After the first execution the data path will be closed again. ...
    (comp.sys.ibm.as400.misc)
  • Re: Performance problem on initial call of stored procedure
    ... It's the behaviour like SQL works. ... creates or validates access plans. ... For dynamic embedded SQL no access plan is stored in the program object ... After the first execution the data path will be closed again. ...
    (comp.sys.ibm.as400.misc)
  • Re: Using Function Table ? using Sql Server 2008
    ... however I have heard that performance can be evaluated using Execution ... Hm, to evalutate performance as such, run the query with production-size ... look at the query plan, to get an idea of what the problem might be. ... Links for SQL Server Books Online: ...
    (microsoft.public.sqlserver.programming)