Nice analogy, however in the analogy the tipping only takes a moment while everything else takes longer. In this situation it's quite the opposite.
Although if you can avoid unnecessary rinse & repeat then I'd say do your best to do so.
-Kurt
-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of CRPence
Sent: Wednesday, September 14, 2011 11:34 AM
To: rpg400-l@xxxxxxxxxxxx
Subject: Re: Embedded SQL Performance Question
On 13-Sep-2011 14:13 , Robert Munday 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.
Imagine a janitor pushing a 50-gallon trash barrel down a hallway, stopping, then entering an office. The janitor grabs the waste-paper basket and carries the basket out into the hallway. At this point the janitor lifts, tips, and shakes the basket to empty the contents into the barrel, then places the basket just inside the office.
Now imagine the janitor repeating four more times each of lifting [invocation and initializations], tipping [allocations, query evaluation, and query start], shaking [data selection and retrieval], and returning of the basket [return from invocation, freeing storage and allocations]. If while tipping there is sufficient light exposing that the basket is already empty [an index with no matching entries], then the actual shaking of the basket to empty the contents can easily be skipped, but the actions of lifting, tipping, and replacing of the basket are still going to take time.
Unless something had been placed in the basket since the most recent emptying [new rows added that match the selection], each repeated attempt to empty the basket is a waste of time. So if truly "every nanosecond counts", then the time for all of that unnecessary work can easily add-up, especially if no index exists to easily identify that no actual data selection is required.
As I recall, the database monitor shows the time those extra DELETE requests spent; a job trace would as well.
Regards, Chuck
--
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.
midrange.com
