Re: SQL Performance

["R. Bruce Hoffman, Jr." <rbruceh@xxxxxxxxxxxxx>]

-----Original Message-----
From: Contractor1@Parkdalemills.com <Contractor1@Parkdalemills.com>
To: MIDRANGE-L@midrange.com <MIDRANGE-L@midrange.com>
Date: Tuesday, August 01, 2000 9:44 AM
Subject: SQL Performance


>
>We are using RUNSQLSTM to run a script that is chalked full of DELETE
>statements and it is taking forever. We noticed that the delete statement
>appears to be reading one record at a time and then deleting it. I say
>appears because that is what I am assuming looking at the open files when
>working with the job. The I/O count is double the relative record.
>

Yup. The RUNSQLSTM is a dynamic execution environment.

>The files we are deleting have referential constraints. QQryDegree is set
>to *Optimize (now). Hmmmm, that's all. If you need more information, just
>ask.
>
>So, any suggestions on how to speed this thing up? Write RPG program or
>what?


Why separate delete statements? If you code just one or two, and select the
records as a group to be deleted...

Writing a program would allow you to compile the statement and execute
static, but each delete statement would still execute as a "get and delete".
Until you can group your deletes, you will see the same execution style as a
program... read the record, if this record to be deleted, then delete...

===========================================================
R. Bruce Hoffman, Jr.
 -- IBM Certified Specialist - AS/400 Administrator
 -- IBM Certified Specialist - RPG IV Developer

"Those who would give up essential Liberty,
  to purchase a little temporary Safety,
  deserve neither Liberty nor Safety."

     - The Papers of Ben Franklin



+---
| This is the Midrange System Mailing List!
| To submit a new message, send your mail to MIDRANGE-L@midrange.com.
| To subscribe to this list send email to MIDRANGE-L-SUB@midrange.com.
| To unsubscribe from this list send email to MIDRANGE-L-UNSUB@midrange.com.
| Questions should be directed to the list owner/operator: david@midrange.com
+---