Re: CQE changes between v5r2 and v5r4? Query/400 Performance.

Charles Wilt wrote:

I was under the impression that the CQE had no changes between v5r2
and v5r4; that all the enhancements (ie. changes) went into the SQE.

However, now that we've upgrade to v5r4, I've had a few complaints
from both users and IT Business Analysts saying that some Query/400
queries are performing much poorer now than they did in the past.

I expected to see changes in the performance of (some) SQL queries
that used the SQE. But since Query/400 queries use the CQE, I didn't
expect any performance differences.

Has anybody else had this issue?

I'm trying to take a look at some of the offenders, but thought I'd
post here at the same time.

The CQE is not static. There are changes to the CQE code in all releases, most obviously, to incorporate PTF changes, but very possibly various /enhancements/ as well. At a minimum the code is recompiled on every release to incorporate any underlying code changes, so any shared code for general & related [e.g. database *FILE and component] changes will be manifest in the existing CQE code. Plus as an /optimizer/, any functional changes as corrective for performance may be indistinguishable from an enhancement, and corrective for function or compatibility for new features may have impacts to existing and shared code paths.

Additionally consider: Unlike SQL access plans which are automatically updated and stored [e.g. in a program or package], the access plan of the *QRYDFN object is never replaced in the object *except* when the object is /recompiled/ using the only supported interface of 2=Change and 1=Save in the WRKQRY interactive interface. If the query definition objects have not been changed & saved on the current release, the access plan is being rebuilt [which includes optimization] every time the query is run.

Regards, Chuck