Re: Query Optimization ... Is this all I can get??
Thanks for everyone's advice.
Unfortunately, I am using a combination of Java and I4GL (moving
everything to Jasper Reports at the moment), and was just using the
"FOREACH" as an example. In reality, it is a for loop.
I first added the (type, type_id) index, and then did an update
statistics as suggested by Frank and Andy, and did a similar operation
on the address table.
I also made sure each of the query's are being executed on separate
connections, as I've had problems using a single connection when doing
multiple queries and looping on a resultset. IE:
while (loop result1 on connection 1) {
while (loop result2 on connection 1) {
while (loop result3 on connection 1) {
}
}
}
Seems to be more problematic than:
while (loop result1 on connection 1) {
while (loop result1 on connection 2) {
while (loop result1 on connection 3) {
}
}
}
My time increase went from 1700 milliseconds to approximately 2 - 3
milliseconds per user, and under (or around) 1 millisecond per phone
and address query. All told, the system pulls all 1500 user's in
about 10 - 15 seconds. Excellent speed increase.
Although your first option would work in my case, it would be a mess,
as they would have to be OUTER joins of the address and phone table,
and the joining of some 100K with 300K with another 150K takes some
time [although, this was my original solution].
I think I understood your second solution -- but I'm happy with the
speed increases from the index, so I'll wait for the next problem to
try it out.
Thanks everyone for the help!
"Art S. Kagel" <kagel@bloomberg.net> wrote in message news:<pan.2003.12.23.09.47.14.636507.12806@bloomberg.net>...
> On Tue, 23 Dec 2003 02:04:37 -0500, Anthony Presley wrote:
>
> Frank and Andy have given excellent advice. I would add that a redesign of the
> app loop would help tremedously. There are two options.
>
> Since you do not state what you are writting these queries in as far as a
> front-end language I do not know if both are possible for you (ex #2 is not
> doable in SPL, but 4GL which also has a FOREACH construct can do either and if
> you are using ESQL/C or Java, etc and are using the FOREACH as an example of
> structure, well...), but FWIW:
>
> 1 - Replace the two dependent queries, which must be executed for each user for
> address lines and phone numbers, with a join of these two tables in the main
> foreach loop query. When the userid changes print user level info and the first
> address line and/or phone number, etc.
>
> 2 - Replace the two dependent queries, which must be executed for each user,
> with three parallel cursors all opened before entering the main loop. Make sure
> all of the queries include an equivalent ORDER BY clause so the rows are in the
> correct order. Then the pseudocode looks like:
>
> - Open phone number cursor (p_c), fetch first row into p
> - Open address cursor (a_c), fetch first row into a
> - FOREACH userid cursor into u
> - process start of user level data
> - if u.userid > p.userid then fetch p_c into p until p.userid >= u.userid
> - if u.userid > a.userid then fetch a_c into a until a.userid >= u.userid
> - do
> - process user phone numbers
> - fetch next p_c into p
> - while u.userid == p.userid && NOT EOD
> - do
> - process user address lines
> - fetch a_c into a
> - while u.userid == a.userid && NOT EOD
> - process end of user level data
> - END FOREACH
>
> Art S. Kagel
>
> > Hello all ....
> >
> > Always with the query questions .... so I come to you once again.
> >
> > 1st -- my textbooks on this subject are rather sparse, and a bit ...
> > un-useful. Anyone have any good links / articles / books on query
> > optimization?
> >
> > 2nd -- the actual problem ....
> >
> > I have a SIMPLE table. Here it is <pseudo>:
> >
> > create table phone (
> > id serial primary key,
> > areacode integer,
> > phone varchar(20),
> > phone_id integer,
> > type_id integer,
> > type varchar(20),
> > phone_id references phoneDef
> > ) lock mode row;> >
> > phone_id is an external reference to a table containing phone number
> > definitions (ie, Primary Phone, Cell Phone, etc...) and type is something like
> > "Office", "User", "Company" and type_id is manually updated by the software to
> > ensure that it is the id of the user / company / office. It has about 300K
> > rows, but each user only has (at most) 3 phone numbers.
> >
> > When I want to do a:
> >
> > SELECT areacode, phone FROM phone WHERE type = 'User' and type_id => > ?
> >
> > It takes a little too long for my liking. About 1300 milliseconds. This is
> > fine for ONE, but when I need to fetch 1500 or more, using another looping
> > query, I end up with .... it taking 42 minutes.
> >
> > IE, I do:
> >
> > FOREACH user [42 minutes]
> > FETCH addresses (if exist) [About 400 milliseconds] FETCH phones (if
> > exist) [About 1300 milliseconds]
> > END FOREACH
> >
> > At about 1700 milliseconds per user, that takes some time. My SQL Explain is
> > saying:
> >
> > QUERY:
> > ------
> > select areacode, phone
> > from> > phone where
> > type = 'User' and type_id = '75543'
> >
> > Estimated Cost: 7571
> > Estimated # of Rows Returned: 2
> >
> > 1) root.phone: INDEX PATH
> >
> > Filters: root.phone.type_id = 75543
> >
> > (1) Index Keys: type
> > Lower Index Filter: root.phone.type = 'User'
> >
> > How in the heck does one speed this up? An OUTER join MAY be feasible, but
> > takes the cost from 7571 to over 46,000. The FOREACH query has a cost of 43.
> >
> > Not really sure how to speed this one up .... seems it doesn't get much more
> > simplistic than following an INDEX PATH. What am I missing?
> > Figure there must be some Informix query to help this along. The
> > table is under 10MB in size.
> >
> > Any ideas? I just updated the statistics [low].
> >
> > Yawn. Night.
> >
> > --Anthony