Re: Ok i have a question Concerning Arrays and result sets

Eric,


The optimizer only makes suggestion based on what you pass to it.  
Sometimes it is helpful change the statement you pass and/or give 
more information.  It sounds like you have already worked through the 
suggestions in the SQL programming manual.  Whenever I have seen 
a performance problem, I have had to break out the manual and go 
through it step by step.  By chance are these files open for update 
somewhere?  I don't know if it is still true, but I had a similar problem 
which was intermittent because it only happened when the file was 
open for update.  Changed the update to open and close the file as 
required and the problem was solved.  If you can't get it to avoid 
building the access path I would say you are dead in the water.

Another thing to look into are the Encoded Vector Indexes.  I have not 
used them myself, just read about them.  It looks like they help the 
optimizer make better decisions and can speed up record selection.

David Morris

>>> "Eric N. Wilson" <doulos1@home.com> 06/17/99 04:13PM >>>
I am afraid that you are not understanding my question. I HAVE made all the
access paths that the optimizer wanted and it still takes 30 seconds even
with optimize for one row.... The only way out of this problem as far as I
can see is to create the result set manually. The result set is going to be
read only as we have a separate stored proc for update.

Thanks

______________________________________________
Eric N. Wilson
President
Doulos Software & Computer Services
2913 N Alder St
Tacoma WA 98407


----- Original Message -----
From: David Morris <dmorris@plumcreek.com>
To: <RPG400-L@midrange.com>
Sent: Thursday, June 17, 1999 11:06 AM
Subject: Re: Ok i have a question Concerning Arrays and result sets


> Eric,
>
> If you don't want it to build an access path it may help to optimize
> for 1 rows, and specify the correct files using join xxx on xxx syntax.
> If you have a lot of data being returned, you may also be able to
> skip the moving of your data (very slight advantage) by using an
> SQLDA.
>
> David Morris
>
> >>> "Eric N. Wilson" <doulos1@home.com> 06/17/99 10:09AM >>>
> I know that the C SQL preprocessor allows you to return an array of host
> variables as a programaticly created result set. Question how can I make
> this same thing work with ILE???? I have tried everything under the sun
and
> I still am not able to make it work... Is it that the ILE RPG preprocessor
> needs some more work?
>
> The reason that I need to do this is that we need some stored procs that
> return data that is spread across 5 files and the response time needs to
be
> subsecond. Using SQL to create the result set the fastest result I have
been
> able to achieve has been 30 secs. Naturally the files that I am joining on
> have literally millions of records in each one. In addition to the speed
> issue there is a need for me to massage the data to make it presentable to
> the user... Like converting the dates into Ages etc....  Oh by the way
this
> is on a 4 processor S30 103 gb disk, and alot of ram.
>
> I created all the indexes that the diagnostic and trace suggested and
still
> 30 secs. The optimizer is still creating an access path every time. I
would
> belive that this is due to the joins.
>
> I have been able to create a RPG program that returns a single instance
and
> running it X number of times happens in the sub second range. This is due
to
> me using navigational access (which face it we can optimize better than
any
> sql process).
>
> Being able to return an array of Data Structures or something like that
> would be the ticket
>
> Thanks in advance
> Eric
>
> ______________________________________________
> Eric N. Wilson
> President
> Doulos Software & Computer Services
> 2913 N Alder St
> Tacoma WA 98407


* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
* This is the RPG/400 Discussion Mailing List!  To submit a new         *
* message, send your mail to "RPG400-L@midrange.com".  To unsubscribe   *
* from this list send email to MAJORDOMO@midrange.com and specify       *
* 'unsubscribe RPG400-L' in the body of your message.  Questions should *
* be directed to the list owner / operator: david@midrange.com          *
* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *