Re: Embedded SQL - performance question

On 26-May-2011 11:35 , Jeff Crosby wrote:

I have a procedure in a service program to retrieve a single record
field (an email address) from a file. The relevant statement in the
RPG is:

Exec SQL
Select CEMLADS Into :EmlAds From CUSEMLAD
Where CCUSNR = :PxCust and CEMLPPS = :PxEmlPurpose;

I then return :EmlAds to the calling program.

To do this for about 2000 records takes upwards of a minute. If
instead I define the file in the calling program and retrieve it via
a simple chain, these 2000 records are handled in 2-3 seconds. Why
the big discrepancy? If I run it in debug mode, it is using the
access path that already exists. Is my SQL statement flaky? Or is it
the overhead of calling the service program? Combination?

If it makes a difference, about 80% of the time no record will be
found.

I expect that SQL should be much faster even on a poorly performing system; almost as fast as the RLA, esp. if the same index is utilized for both the SQL and RLA. My guess is that the query is starting fresh, a full-open performed each time the query statement runs. Perhaps the activation group is not named or not *CALLER, such that the ODP is not being left open. For that statement a pseudo-closed cursor should be capable of reuse [a reusable ODP], to remain open, to be utilized again on the next invocation [since\after the second invocation], if the activation group remains active. I suppose the CLOSQLCSR(*ENDMOD) versus *ENDACTGRP could be an issue. Less likely, but there could also be issues for waiting on record lock(s); for example if the SQL defaulted to COMMIT(*CHG), but where declaring the file for RLA does not, and there is any conflicting update activity.? The output from a trace showing program flow should give a good indication where time is spent; though I would reduce the tests to ~200 records hoping to get closer to some 6 versus .3 seconds of processing.

Regards, Chuck