× 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.



A issue you could be having is that the join is creating way too large of
a result set to work with quickly. How does the outer Select distinct
perform without the join? If it is very quick, just create a cursor using
that select, and then process the join records in a separate cursor. When
you fill up your subfile, stop processing the primary cursor, and you will
significantly reduce the amount of data you are processing. I know
Charles will disagree with me, but I know, based on experience, that
sometimes you have to break up your query into separate statements to get
the best performance. This is particularly true if you are filling a
subfile and your query contains one or more serious joins. In deference
to the SQL can do anything crowd, I have only had this problem once or
twice where I couldn't optimize the query some other way and had to break
it up.

Mark Murphy
STAR BASE Consulting, Inc.
mmurphy@xxxxxxxxxxxxxxx



From:
"RWMunday" <rwmunday@xxxxxxxxxxxxx>
To:
"'Midrange Systems Technical Discussion'" <midrange-l@xxxxxxxxxxxx>
Date:
06/01/2009 10:38 PM
Subject:
Speeding Up A Slow SQL
Sent by:
midrange-l-bounces@xxxxxxxxxxxx



Greetings from sunny Florida.


I have an RPG /Free program gathering data using embedded SQL. It's
slooooow. I have 13+ million records in a file of purchase orders and I'm
assembling a list of eligible vendors in a subfile window from which to
select to display the respective PO data for that vendor. I'm using a
Select Distinct on the vendor number and joining to the vendor file to get
the vendor name. I only need to allow selection of vendors who actually
have data in this file. Once a user selects a vendor, the individual PO
records are displayed in a subfile. Both are page-at-a-time subfiles. No
need to wait for thousands of records to load in a subfile. I am using
indexed logical views of both files (PO and Vendor) to gather the records
but the initial load, understandably, is slow and takes about one minute
to
load the first page. Of course, subsequent pages load instantaneously
when
the page key is keyed.

How can I speed up performance of my SQL? Would doing a Select Distinct
on
the single PO file, then chaining to get the vendor name as needed speed
things up? I will probably try that in the morning just to see what it
does. But if you have any tips to improve my speed, send them on, please.

Thanks,



Robert Munday
Munday Software Consultants
Montgomery, AL
On assignment in Jacksonville, FL


As an Amazon Associate we earn from qualifying purchases.

This thread ...

Follow-Ups:
Replies:

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.