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

You may try to force optimizer to use existing access path, adding ORDER BY with
fields from existing access path.
The key thing is WHY it wants to build an access path ?
There could be many reasons - data type conversion, different CCSID, using
mapped fields in a search criteria, others.
You may try to guess and eliminate the reason, then it will use existing access
path.

Best regards
    Alexei Pytel



"Eric N. Wilson" <doulos1@home.com> on 06/17/99 05:13:13 PM

Please respond to RPG400-L@midrange.com

To:   RPG400-L@midrange.com
cc:    (bcc: Alexei Pytel/Rochester/IBM)
Subject:  Re: Ok i have a question Concerning Arrays and result sets





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          *
> * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *

* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
* 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          *
* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *



* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
* 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          *
* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *