× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



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




As an Amazon Associate we earn from qualifying purchases.

This thread ...

Follow-Ups:
Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.