Donna
I've been trying to figure out how to answer your question. And have
been busy with related and other tasks.
The statement was something like this--
insert into ourfile (field1, field2, ... , field12)
select distinct trim(:hostvar1), trim(:hostvar2), ... , trim(:hostvar12)
from theirfile
where char(theirfield1) = :hostval1 and char(theirfield2) = :hostval2
and trim(theirfield3) = :hostval3
I had some reasons for the way I wrote this, although I'll say below
that they probably needed to be looked at and drastically revised.
For one, hostval1 was a value from our document index file and is
always character - theirfield1 is numeric - unfortunately our value1
could have non-numeric data, so the comparison is problematic. I
chose to make the numeric always into a character value, therefore no
possible error. However - and this is maybe the main reason for the
poor performance - this means that the use of an index was impossible
- you can't have functions on fields and then use an index that has
those fields, because the index does not know about the function.
There IS a logical file over theirfields 1-3, but it is not usable
because of the char() and trim() functions.
So it had to do a table scan against theirtable - which is a very
good-sized table. And this accounts for the long time to perform this
insert - it had to read that entire table every time, to find ALL
matching occurrences. I got this information by running the DB
monitor over a job that was running this statement. Then used Visual
Explain in Navigator to see what was happening - fantastic tool!
Just imagine several jobs doing full table scans on a large table,
just to find a record or two - and all taking lots of CPU - nightmare
time - and the bigger that table, the worse - it would never scale!!
Now it is possible that this could be better written in SQL - DOH! I,
however, chose to rewrite it in native RPG with F-specs and the exact
logical in mind. I was pressed for time and really could not afford
another poor performing SQL statement - although I am now pretty
convinced that I could have a good SELECT statement if I were able to
get rid of the functions in the WHERE clause.
So this is an example of a pretty poorly written statement - folks -
at least give me points for admitting my failures!!!
I'm NOT convinced I could do better or the same with SQL. as the
result I got with native I/O - this is an example of something where
an exact match in a logical file is advantageous - where it is
looking for maybe 2 or not many more records in "theirfile" - and
that should be lightning quick, with the right logical file.
Hope that helps a little - heh!
Regards
Vern
At 01:44 PM 3/14/2008, you wrote:
Vern, Thanks a lot for big reply.
---------
Now I have a program I wrote originally with SQL, chosen over native
IO for various reasons that seemed good at the time. It has turned
out to be a royal dog in performance. I just replaced it with a new
logical - one not in our product but over a file IN our product - and
the runtime of that program went down by a factor of 50. And there
WERE indexes for each of the components of the INSERT based on a
subSELECT, yet they did not help. It would go from 50 seconds or more
to even subsecond for some of these jobs.
--------
Refer to above phrase, can you explain me in detail how the
performance (run time) improved by a factor of 50 and what were the reasons ?
midrange.com
