Re: SQL efficiency vs. OPNQRYF

On Tue, 2005-09-13 at 15:50 -0700, James H H Lampert wrote:
> Does anybody have any sort of educated opinion on relative 
> efficiency of SQL vs. OPNQRYF, when doing complex (and 
> mostly joined) queries on physical files?
> 

Well, in the past, OPNQRYF and SQL used the exact same engine.

Now, (since at least V5R2) SQL can use the new SQL Query Engine under
more and more circumstances with performance benefits. I don't recall if
OPNQRYF is specifically left out of the new SQE though, for some reason
I want to sat that it still uses the CQE. 

> In the project I'm working on that involves taking a 
> rather Rube Goldberg contrivance of OPNQRYF calls, and 
> re-implementing it in SQL, part of the complexity of the 
> old OPNQRYF implementation was that with a complex query 
> involving 2 levels of AND and OR across a bunch of files, 
> trying to do it in a single OPNQRYF would take far longer 
> (order of FOREVER) than breaking it down into a bunch of 
> simpler OPNQRYFs. Am I likely to encounter the same 
> problem with SQL?

Not sure what you mean, but if you mean "can I write a huge SQL
statement" the answer is yes.

Not only that, but if the logic is complex, you can use blank lines and
comments throughout ( the -- starts a comment that ends with the end of
the line, much like // in c, c++ or java) so that the "convolutions" can
be deciphered later.

And as far as performance is concerned, after the statement is working,
you don't have to write code to tune it. You create the right index or
indexes. But more importantly, OPNQRYF runs dynamic SQL only. If you
embed SQL, it runs static. It's faster.

> 
> --
> JHHL