RE: SQL Performance Question

["Joe Pluta" <joepluta@xxxxxxxxxxxxxxxxx>]

> From: Jay Himes
>
> The answer is not a simple yes or no.

The answer to the question of Zak's case was relatively simple, Jay.
Interestingly enough, the answer was "yes". it was fiscally responsible,
somewhat to my surprise.  Since he was doing it on his own nickel, Zak
pretty much can do what he wants.


> In the example I gave, the query is being run by a cold fusion server; so
> SQL is by far the easiest way to access data stored in the database.

I purposely did not ask the question of your situation, since it seemed you
were addressing an honest work problem that had already been translated into
(or originally defined in) SQL.  Almost by definition, then, everything you
do performance-wise can be justified.


(...)
> Thirdly, RPG code; while it may have been faster to code in this
> particular
> instance; in my experience it is not faster to code (at least in the cold
> fusion or net.data arenas) for the vast majority of queries
> performed by the
> web site.

SQL quite often outperforms RPG in terms of time to market and flexibility,
especially when used for what it was intended: queries.


> In conclusion of this very long message (sorry) I would say that
> I use both
> tools and IMO it is very short sighted to exclude either one from
> your tool
> box.

Some people seem to get the idea that I don't like or don't use SQL.
Nothing could be further from the truth.  Here's one of my favorite uses,
and it's not even a query:

C/Exec SQL
C+ DELETE FROM SCCC1 where C1DFID = :ID
C/End-exec

I absolutely LOVE using SQL to clear a set of data from a file.  And here's
another one where I could probably write it in RPG, but I prefer the SQL
version:

C/Exec SQL
C+ declare C cursor for
C+ select a.c1sseq, substr(a.c1source,1,3), a.c1kwid
C+  from sccc1 a, sccc1 b
C+  where a.c1flag3 = 'V' and a.c1dfid = :ID
C+  and b.c1dfid = a.c1dfid and b.c1prnt = a.c1kwid
C+  and a.c1source in ('REWRITE', 'WRITE', 'READ', 'OPEN', 'CLOSE')
C+  and b.c1source in (:WSF, :WSR)
C+ order by a.c1kwid
C/End-exec

This particular statement is used to find all I/O operations in a parsed
COBOL source member.  As I said, I could do it in RPG, but the code would be
much larger and possibly slower, since a lot of this can be done "under the
covers" in the SQL query.

I'm not averse to SQL, I just hate hearing about people blindly replacing
all native I/O with SQL.

Joe