I was surprised that Regular Expressions didn't fare better in this
admittedly simple example. Disappointingly, I was not surprised at the SQL
performance.
Unfortunately SQL is slow because:
1. SQL-Statements get translated into dynamic program calls by the SQL
precompiler
2. SET statements get translated into dummy select statements Select
SetClause from Q(I forgot the dummy table name) and for each SQL statement a
query optimization must be performed. With Release V5R4 for SQL programming
an "Expression evaluator" was introduced, that means SET Statements get
directly translated into C instructions instead of being converted into
SELECT-Statements, but there is AKAIK nothing comparable for embedded SQL.
On the other hand, I've often seen source codes, where programmer try to get
better performance for example by using MOVE instead of EVAL or other
things, but read all records of a table where only a few records are needed
or ignore thousands of records using the OPCode ITER.
Mit freundlichen Grüßen / Best regards
Birgitta Hauser
"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"
-----Ursprüngliche Nachricht-----
Von: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx] Im
Auftrag von Dennis Lovelady
Gesendet: Sunday, 07. March 2010 15:10
An: 'RPG programming on the IBM i / System i'
Betreff: RE: AW: More on RPG style
Boy, I wonder if it weren't possible to write and IN() function in
RPG.
Why not just using the SQL predicate IN?
Exec SQL
Set :isFound = Case When :MyField in ('AB', 'BB', 'XX')
Then '1' else '0' End;
Here's one reason. I put together a timing test program to test the
differences (in pure run time) between three approaches to an SQL LIKE
function. One approach is the SQL LIKE function itself, imbedded in the
source as shown above. The next approach is to use Regular Expressions to
the same end. Finally, a %SCAN built-in function approach was used. The
complexity of the test itself was also varied, so that a "simple" test (like
'%mytext%') and a more complex test (like '%my%text%') could be evaluated.
I was surprised that Regular Expressions didn't fare better in this
admittedly simple example. Disappointingly, I was not surprised at the SQL
performance. The code is at
http://code.midrange.com/315d1ba179.html. The
results for 1 million iterations were:
SQL "contains" . . . : 72.201 seconds
RegEx "contains" . . : 6.766 seconds
%Scan "contains" . . : 1.020 seconds
SQL "complex" . . . : 72.097 seconds
RegEx "complex" . . : 41.286 seconds
%Scan "complex" . . : .987 seconds
I had made the comment in a prior message that the developers of SQL should
be ashamed. Note that while these numbers still support that statement
somewhat, the expression came from performing these tests in DEBUG mode. In
that mode, the SQL numbers were off the chart (minutes, not seconds), and it
turned out to be due, at least in part, to the SQL
throw-everything-into-the-joblog approach used during debug mode. I'd have
been less inclined (but no less justified) to make such a statement if these
were the numbers I had seen at the time.
Yes, I do realize that it is not always possible to hand-build an expression
so that it can be handled with %SCAN. To that end, I do wish Regular
Expressions had fared better on the more complex test. This was more a
personal exercise to determine whether it's worth my time to code something
manually or (as Birgitta suggests) to just let the SQL compiler do it. For
me, the coding effort seems very much worthwhile.
One can take the "stand on a chair to get closer to the moon" stance all one
wants, but please remember that analogies only go so far. There is real
performance gain here. That's especially important when you consider that
most programs don't do only one thing, and that multi-million-iteration is
more the norm as databases grow and business needs get increasingly complex.
Finally on that thought, if I stand on a million chairs, that's going to put
me 315 miles closer to the moon. I still can't touch it, but it'll only
take about 850 more steps like that before I can walk on it.
Dennis Lovelady
http://www.linkedin.com/in/dennislovelady
--
"When a man says he approves of something in principle, it means he hasn't
the slightest intention of putting it into practice."
-- Prince Otto von Bismark
midrange.com
