SQL for performance (overnight batch jobs)

I'm currently working on a suite of reports for a BPCS user and using SQL is
really simplifiying the RPG coding because with some careful thought its
possible to get a result set with one admittedly quite complex sql statement
that would have required pages of SQL code.

However, I have seen SQL cripple the BPCS G/L where huge datasets are
involved because of poorly coded SQL or simply because its being used where
its not suited to the task required and Native I/O is much quicker.

If I'm looking for the existence of a given record in a file and that record
can be identified by a logical key then I'll use chain over a SQL select
approach any day.  There will always be occaisions - however a database has
been designed: SQL fully relational from the ground up or not - that you
will need to check the existence of a record as a one off.  In these cases
on iSeries uitilisng  RPG400/IV/ILE a Native I/O can be the more effecient
solution.

I always have a simplistic view of SQL selects that runs as follows: SQL
will look at the whole contents of the file and throw away the ones that
don't match the select whereas with Native I/O my simplistic view of the
chain on a logical file is that its like looking at the Index page of a
book, it can position rapidly to the area its interested in and search from
there  eg Q then read from Quaint to Queer and stop as soon as it finds a
matching record I want.

Joining multiple files with SQL is great if you code the statements
correctly but I've seen some real dogs breakfasts in the past and with some
judicuous/obvious corrections made massive improvements in performance of a
given statement.  I think its perhaps easier to write awful SQL based code
than Native IO code.

I've not played with RI or cascading deletes other than in simple MS Access
based systems and wouldn't like to comment on its application.  

I'll use a mix and match approach to SQL/Native IO as the need arises based
on the data volumes, means of identifying the recordsets etc.  They're both
techniques to have in my toolbox and and to discard Native IO and use solely
SQL would be throwing out the baby with the dishwater.

Finally, if building a new system from the ground up then an SQL based
database with RI would be worth a serious look but when amending/adding
functionality to a DDS based system I think that using SQL tables, views,
indexes and creating a hybrid system has the makings of a maintenance
nightmare.   

When I code programs I may sacrifice possible performance advantages for
ease of maintainablity - ie don't do something the smart-arse way just
because you can because it may not be you that comes in later to make
amendments (or heaven forfend fix a bug ;-) )  and I think that approach
applies system wide.

my two pence worth

Graham