Re: Embedded SQL Performance Question

[Charles Wilt <charles.wilt@xxxxxxxxx>]

I'd also agree your taking a hit, how much of one depends...for
example if you don't have a needed index you're performing multiple
tables scans when only one is really needed.

We had a RLA process that worked on a single "location" and was invoke
one location at a time; took hours to run it for 80+ locations.  I
rewrote it to use SQL and to process all locations at once...the new
job took minutes.

It's never a good idea to make multiple passes, especially with SQL.

Even with your company specific stuff, you should be able to do it in
one pass...

Delete from myfile
Where company in (select company from cmptoprc)
 And ...



On 9/13/11, Robert Munday <rwmunday@xxxxxxxxxxxxx> wrote:
>    I am looking for efficiencies and identifying where some processes can be
>    streamlined.
>
>
>
>    I have a number of RPG programs using SQL statements in purge and delete
>    programs which are being called five times, once for each company.  Many
>    of the SQL DELETEs are being performed on non-company specific fields
> such
>    as dates.  Deleting by dates would cross all company records.
>
>
>
>    Are there any performance hits for attempting to do SQL DELETEs multiple
>    times where the first call of the program has already deleted the records
>    and subsequent calls are redundant?  I would think not but every
>    nanosecond counts.
>
>
>
>    Thanks,
>
>
>
>
>
>
>
>    Robert Munday
>
>    Munday Software Consultants
>
>    Montgomery, AL
>
>    on assignment in New Albany, OH
> --
> This is the RPG programming on the IBM i / System i (RPG400-L) mailing list
> To post a message email: RPG400-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
> or email: RPG400-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/rpg400-l.