set explain analysis

I've recently learned how to use set explain to analyze sql statements. Now I need to know what to do with the information. I was able to greatly improve one process by adding indexes to the tables that were being read sequentially. I figured out what columns to index by looking at the where clauses. Now I'm trying to improve another process but no matter what I do, the cost isn't changing. How do you interpret the set explain output? How do you know if you should rearrange the sql, add indexes to what columns, etc.? Also, does it matter what order the tables are listed in the from clause, what order the conditions are listed in the where clause and what side of the conditional operator the columns are listed? I'm running IDS 7.30 on WINNT and 7.31 on WIN2000. I will be upgrading to IDS 9 on WIN2000 in the near future. Thanks in advance! Tina

post the query plan and someone will take a look Tina Moriarty wrote: > > I've recently learned how to use set explain to analyze sql > statements. Now I need to know what to do with the information. I > was able to greatly improve one process by adding indexes to the > tables that were being read sequentially. I figured out what columns > to index by looking at the where clauses. Now I'm trying to improve > another process but no matter what I do, the cost isn't changing. > > How do you interpret the set explain output? How do you know if you > should rearrange the sql, add indexes to what columns, etc.? Also, > does it matter what order the tables are listed in the from clause, > what order the conditions are listed in the where clause and what side > of the conditional operator the columns are listed? > > I'm running IDS 7.30 on WINNT and 7.31 on WIN2000. I will be > upgrading to IDS 9 on WIN2000 in the near future. > > Thanks in advance! > Tina -- Paul Watson # Oninit Ltd # Growing old is mandatory Tel: +44 1436 672201 # Growing up is optional Fax: +44 1436 678693 # Mob: +44 7818 003457 # www.oninit.com #

Tina Moriarty wrote: > I've recently learned how to use set explain to analyze sql > statements. Now I need to know what to do with the information. > [...] > > How do you interpret the set explain output? How do you know if you > should rearrange the sql, add indexes to what columns, etc.? Also, > does it matter what order the tables are listed in the from clause, > what order the conditions are listed in the where clause and what side > of the conditional operator the columns are listed? > > I'm running IDS 7.30 on WINNT and 7.31 on WIN2000. I will be > upgrading to IDS 9 on WIN2000 in the near future. I'd suggest starting with the IDS Performance Guide. The guides for both 7.3 and 9.4 (I didn't check for other versions) have a section dedicated to queries and the query optimizer. If you don't already have it, the documentation can be found here: http://www.ibm.com/informix/pubs/library/lists.html I think you'll be able to answer most, if not all, of your questions once you get through the chapter on queries. If you want to go further, there are articles at the IIUG site (www.iiug.org), and searching through Google can turn up some interesting comments. But start with the Performance Guide. It has a lot of good information in one place. -- June Hunt