RE: Chaining with a very large file

I'd have to see the entire program, but from what you've given it's
likely you could have done much, much better.  I'm going to guess that
you used:
c/exec SQL
c+  EXECUTE IMMEDIATE :SQLCommand
c/end-exec

If so, then that would explain it as EXECUTE IMMEDIATE is about the
worst way to do anything with SQL.  


The fact of the matter is SQL _IS_ faster when updating multiple rows.
But you have to code it correctly.

I'd be willing to make a <small> wager that SQL could have done it
faster, quite possible fast enough to do all the employees in one
weekend.  (What size box and what OS release are we talking about here?)

However, I will say that it's an interesting problem.  The 600 different
files are what is posing the difficulty.  The best SQL would have
hardcode the 600 file names.  Actually, 600 different files would have
been a problem for RPG also, how did you handle it there?

Another way to have handled this might have been to setup a special
master file.  Then go through and add foreign keys with ON UPDATE
CASCADE to all 600 tables.  Then, to change an employee, you update one
row in the new master and cascade the change to all 600 files.


Charles Wilt
--
iSeries Systems Administrator / Developer
Mitsubishi Electric Automotive America
ph: 513-573-4343
fax: 513-398-1121
  

> -----Original Message-----
> From: rpg400-l-bounces@xxxxxxxxxxxx 
> [mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Tom Huff
> Sent: Wednesday, February 01, 2006 11:38 PM
> To: 'RPG programming on the AS400 / iSeries'
> Subject: RE: Chaining with a very large file
> 
> I didn't create special indexes. Almost(98%) of the files had a LF by
> Employer and Employee, which is the key needed to process by. 
> I have to
> process all records for an employee within an Employer.
> Here is the SQL statement:
> SQLCommand = 'update ' + dbFILE
> 
>                  + ' set ' + dbEN1 + ' = ''' + ##EN + ''''
> 
>                  + ' where ' + dber + ' = ''' + ##er + ''' and '
> 
>                  + dben1 + ' = ''' + ##ss + '''';  
> Please let me know if anyone knows a better way to write 
> this. I am fairly
> new to using embedded SQL
> Thanks
> Tom
> 
> -----Original Message-----
> From: rpg400-l-bounces@xxxxxxxxxxxx 
> [mailto:rpg400-l-bounces@xxxxxxxxxxxx]
> On Behalf Of Aaron Bartell
> Sent: Wednesday, February 01, 2006 8:03 PM
> To: 'RPG programming on the AS400 / iSeries'
> Subject: RE: Chaining with a very large file
> 
> >Sorry to burst SQL's bubble, but it can not hold a candle to RPG.
> 
> I'd be curious to see your SQL statements and then the steps 
> taken to make
> sure there were indexes to optimize the SQL.  The reason I say that is
> because I was doing SQL in an application and thought I was 
> optimizing it
> well with the indexes I created from my own thought process, 
> but when I took
> it through the Visual Explain tooling in iSeries Nav it turns 
> out I was off
> on many of my indexes.
> 
> If I remember correctly you can turn on journaling and see what SQL
> statements are being run against a file and see exactly what 
> the optimizer
> chose for access paths and what it dynamically created as it went.
> 
> Might be worth taking a look into if you ever need to do SQL again.
> 
> Aaron Bartell
> 
> 
> -----Original Message-----
> From: rpg400-l-bounces@xxxxxxxxxxxx 
> [mailto:rpg400-l-bounces@xxxxxxxxxxxx]
> On Behalf Of Tom Huff
> Sent: Wednesday, February 01, 2006 9:50 PM
> To: 'RPG programming on the AS400 / iSeries'
> Subject: RE: Chaining with a very large file
> 
> The last time I tested this, RPG using SETLL & read loop with 
> a test in the
> loop beat SQL by a factor of 10. I am changing all the 
> records in an HR
> system one employee at a time. The employee number is being 
> changed from
> SocSec to a generated number. There are about 600 files and 
> each employee
> has anywhere from 25 to 25000 records. SQL takes an average 
> of 8.5 minutes
> and RPG takes an average of 1 minute. 
> The total number of records to change was too large to run 
> over a weekend so
> we broke it down to one employee submitted when the new 
> employee badge is
> made.
> The programming in SQL was shorter but the RPG was MUCH FASTER.
> I wrote a log file of the number of records changed and the 
> elapsed time
> using both methods. We are changing about 250000 employees. 
> Sorry to burst SQL's bubble, but it can not hold a candle to RPG.
> Thanks
> Tom
> 
> -----Original Message-----
> From: rpg400-l-bounces@xxxxxxxxxxxx 
> [mailto:rpg400-l-bounces@xxxxxxxxxxxx]
> On Behalf Of Wilt, Charles
> Sent: Wednesday, February 01, 2006 12:28 PM
> To: RPG programming on the AS400 / iSeries
> Subject: RE: Chaining with a very large file
> 
> > -----Original Message-----
> > From: rpg400-l-bounces@xxxxxxxxxxxx
> > [mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Douglas W. Palme
> > Sent: Wednesday, February 01, 2006 12:15 PM
> > To: RPG programming on the AS400 / iSeries
> > Subject: RE: Chaining with a very large file
> > 
> > 
> > Thanks for the tip, I know sql is out of the question for a 
> file this 
> > size and the machine we have.
> > 
> 
> Nonsense, the logical file you need for native I/O would 
> provide benefits
> SQL would use too.
> 
> If all you are doing is reading one record, then native I/O should be
> faster.
> 
> On the other hand, if you what to read 100s or 1000s of 
> records with the
> same key you'd find SQL faster.
> 
> 
> 
> Charles
> 
> 
> --
> This is the RPG programming on the AS400 / iSeries (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.
> 
> 
> 
> -- 
> This is the RPG programming on the AS400 / iSeries (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.
> 
> -- 
> This is the RPG programming on the AS400 / iSeries (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.
> 
> 
> 
> -- 
> This is the RPG programming on the AS400 / iSeries (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.
> 
>