Re: Can you do this in SQL

["David Morris" <David.Morris@xxxxxxxxxxxxx>]

Alan,

I was going to sit this one out, but I think people may interpret your

post as implying that OPNQRYF performs better than SQL for the
same task. That would be a shame, a lot of people avoided
OPNQRYF and continued using FMTDTA based on outdated
information. I was one of those people until John Sears took the
time to explain that the likely cause of my bad experience with
OPNQRYF was likely do to an early bug. I think the argument that
OPNQRYF is the right solution because it is faster is a similar
argument.

I did a test of SQL and RPG vs OPNQRYF and RPG. It took exactly
6 CPU seconds for either solution to run 500 iterations over a 72
record
file (QAUOOPT). I ranked the file by the option field and used a UDF
in the SQL version and an RPG eval in the OPNQRYF version. I am
satisfied that both represent the solutions that were discussed on this

list and follow good programming practices. I did not use anything
extreme to boost performance like field select for the SQL version
or a huge block size for the OPNQRYF solution. If anyone wants to
independently verify this let me know and I will send you the source.
The OPNQRYF solution was shorter 26 lines in two programs vs
31 lines in one.

I recall these same types of arguments made for FMTDTA. Over the
years OPNQRY got better and sort didn't. I am suspect a lot of the
claims that OPNQRYF will perform better is based on old information.

David Morris

>>> mida@pressmantoy.com 01/30/02 01:54PM >>>
Alexei:

You are right that your solution was not the best from a performance
point
of view (yes I tried it) but I really enjoyed it, very impressive.  I
use
SQL all the time and it's great to learn something new.  It's
definitely
true that RPG is the right solution here but I these kinds of things
give an
idea of what is possible in other situations where it might be
appropriate.
This is the best tip since I saw the bicycle icon in David Kahn's sig
file!

I've also been taken aback by how the thread developed.  FWIW I'm a big
fan
of OPNQRYF too, also since good old realease 8 of CPF.  Of course, when
I
write the RPG program I will use the RPG cycle and level break
indicators
but I don't want to start a big ruckus on the merits of that style :)

Thanks to all who responded.

Alan Novick
Pressman Toy Corporation
Enjoying the unsual biking weather in New Brunswick, NJ