× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.


  • Subject: Re: Ok i have a question Concerning Arrays and result sets
  • From: "David Morris" <dmorris@xxxxxxxxxxxxx>
  • Date: Fri, 18 Jun 1999 08:53:58 -0600

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


As an Amazon Associate we earn from qualifying purchases.

This thread ...


Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.