RE: SQL Performance Question

Joe, et. al.,

My situation is not the norm, so allow me to explain a bit. I do have a day job 
where I code in RPG, some C, etc. but at home I run a music review website, and 
that is the project I speak of. I report to no one and the only cost is my 
time. A big part of running the website is that it gives me a chance to try out 
the clever schtuff that wouldn't go over so well at the day job where I have to 
be very concerned about the person behind me being able to maintain my code, 
and nobody here is into SQL. I understand that most of you are not in that 
situation. All in all, it's a learning experience, and I'm nerd enough to think 
it's fun. I'm lucky enough to have time to play--at nobody's expense.

However, Joe makes good points, so I'll take a stab at a reply--but bear in 
mind that my debate skills are quite lacking...

The project is written in Net.Data. It's another debate, but I believe Net.Data 
is the quickest way to get a dynamic website online. I may be wrong; I haven't 
tried everything. Net.Data wraps SQL very nicely, so at least for a first run, 
SQL is the obvious solution. I have been using SQL heavily for about eight 
years, in fact, I used to test iSQL in pre-release OS/400 for IBM. So yes, I 
find complex SQL statements pretty easy to read, but I still have trouble 
trying to "see" what's really happening in a series of reads and chains, 
although I've been doing RPG for quite a while, too.

Knowing my database layout intimately, creating the SQL statement was a very 
quick process, no more than five minutes to create a working version, albeit a 
slow one. Optimizing the query as documented in my post took around a half 
hour, including writing the post. Total time of about 35 minutes or so.

Now, I agree that if you're not familiar with SQL and don't understand when you 
need an index that this could take much longer, but I don't think it's out of 
sync with the learning curve of other "languages." The SQL performance issues 
I've seen are generally related to lack of skill, not system deficiencies. As I 
said, this query is now running in around a second on a 510-2143. I think 
that's pretty good for that old beast, and is "good enough" for this 
application. It's not always necessary to tweak every cycle. At least not right 
away.

I believe that where this really shines is maintenance and flexibility. I can 
change this view any time without affecting the programs that use it (because 
they use SQL to get at it). I can add or alter fields, include another table, 
or whatever, and I won't need to alter any existing SQL statements (and no 
level checks!). It's true that I may need to revisit indexes, so there's a 
tradeoff. I can sort the data any way I choose. I can include or exclude 
records any way I choose. I don't know of any easy way to do that in RPG 
without having a logical file for every possible option, or doing a sort in my 
program, which quickly makes RPG a more complex solution--now I'm writing DDS 
and RPG. But I'd be interested to hear of ways to do this. I can add any sort 
of additional SQL logic, perhaps summing a column or adding a complex where 
clause, in one fell swoop. I know I can write any and all of that faster in SQL 
than RPG. I know that from experience. YMMV.

Joe, I'm very interested in continuing this discussion, and I'm the last person 
you have to worry about offending. As an exercise, I've thought about rewriting 
the SQL in RPG so we can once and for all see exactly what would be required to 
translate it--and if it would be possible to do the things I mentioned, like 
dynamic sorting and selecting.

Z

-----Original Message-----
From: Joe Pluta [mailto:joepluta@PlutaBrothers.com]

> From: Metz, Zak
>
> You can debate whether or not this would have been "better" done
> in RPG or whatever, but I find SQL to be much simpler to read
> than a series of chains, and much easier to code, so although I
> agree that I could probably get better performance in RPG, I'd
> hate to have to make changes to it. But in SQL...no problem!

I wasn't going to say anything, but you're the one who brought it up.  This
SQL statement is basically the equivalent of eight CHAINs.  It would have
taken what, ten or fifteen minutes to write in RPG?  If it would have taken
more than fifteen minutes, it's not RPG's fault, but let's be generous and
say an hour.

How long have you been working on the SQL?  How much did your employer
actually pay for this solution that, in your own estimation, doesn't even
perform as well as the native RPG would?  And what changes do you foresee
that will be easier to make in SQL than in RPG?  How does your company make
its money back for the decision you made to use SQL?