Re: SQL vs native I/O round 19 (long)

[Vern Hamberg <vhamberg@xxxxxxxxxxxxxxxxxxxxxxxxx>]

Loyd

Try running the SQL queries with STRDBG and CHGQRYA QRYTIMLMT(0). Then, 
when the message comes up, take a 'C' to cancel. The job log will contain 
optimizer messages that tell you what it decided, and what it recommends, 
esp. in the way of indexes.

There ARE ways to influence the optimizer - like OPTIMIZE FOR 1000000 ROWS 
to nudge it in the direction that handles lots of records.

HTH

Vern

At 01:06 PM 6/12/2003 -0500, you wrote:
> Let me start by saying I'm not trying to argue for or against SQL (there are
> others who do this better than I), but am relating "real world" experience
> in using SQL versus native IO for AS/400 database access.

-snip-

> I don't really have a question about this, just observations. But I was
> wondering why the query optimizer didn't see or do the second SQL with the
> temp table. Are there any other SQL things I could do (without creating
> views, etc) to improve this query? 15 minutes is good, and this will be an
> "occasional" report, but for other live lookup type inquiries, 15 minutes is
> too long. Two minutes is too long, but a couple of seconds would be OK to
> populate a page-at-a-time subfile (or browser window). I'm also wondering
> whether the query optimizer can take "shortcuts" through the data like I did
> in the RPG (once I processed satisfied the DBDMREP criteria, I don't need to
> read the rest of the records for the same item).
>
> Loyd
>
> --
> Loyd Goodbar
> Programmer/analyst
> BorgWarner Incorporated
> ETS/Water Valley
> 662-473-5713
> lgoodbar@xxxxxxxxxxxxxx