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