Query Optimization ... Is this all I can get??

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

Anthony Presley wrote: > > 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]. > I would create an index with fields type_id and type in this order. Field type has very few values, but type_id will have many different values. Then update statistics again according to the manuals (dostats: table phone (MEDIUM), column id (HIGH), column phone_id(HIGH)) and let us know the performance increase. Regards Frank

The giveaway line is: Filters: root.phone.type_id = 75543 This means that, having located the rows with type='User' through the index on type, it has to read all the data pages for those rows to establish which ones have type_id = 75543. Index paths can be misleading as the index might not be the slightest bit selective and all the real work is being done in the "Filters" bit. Replace the index on (type) with one on (type, type_id). This will allow the whole criteria to be avaluated through the index, only touching the data pages for the rows you're actually interested in. You haven't said whether the two fetches use the same cursor / filter criteria as the foreach, but I'd hazard a guess that similar principles apply. Andy anthony@zoraptera.com (Anthony Presley) wrote in message news:<a55ad738.0312222304.5eabf763@posting.google.com>... > 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

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