Repeating query not running faster

[SnowCrash7]

Running DB2 101 fp2 on Red Hat Linux.

We have a query that repeats several times consecutively. The issue is that the subsequent execution times are not running faster than the first execution. I would think that the explain plan, once in the sql cache, would enable quicker times on subsequent executions.

Is there anything I can do to help subsequent executions run quicker?

Thanks in advance.

 

[fredericofonseca]

creating and caching the plan takes just a few miliseconds - unless your query runs under a second you won't really notice any difference.

In any case post the sql here, alongside the number of records of each table used and all the indexes each table has and maybe we can give some hints

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[SnowCrash7]

Thanks for the reply. The sql I was provided by the app team is not correct. If I can get them to provide me with the right sql I will post.

Thanks

 

[blom0344]

I recall that DB2 uses a setting that allows the db to take less or more time to spend on creating the most optimized explain. I forgot the name of this parameter, but setting it to a higher value would only be beneficial for complex queries. The range I recall went from 1 to 9

Ties Blom

 

[papadba]

If the repeated query uses different predicate info and returns differing rows, why would better improvement be anticipated?

If the repeated query processes the same input rows and produces the same results, some improvement might be seen due to a reduced number of I/O - data still in the buffers and not re-read.