RE: OWS performance problem

I would need more infor to correctly analyse this problem but here are some things to look at. When you query by B matches "*Y*" you will force a sequental scan your index for 'B' element (after filtering by 'A'). 1. How unique is the 'A' element in this index. If you have a large percentage of your total rows where A = 'X' then you would be better off without the index. 2. What is the percentage of rows that should be returned against the total number of rows in the table. If this is high again you would be better with the Seqential scan. 3. Outside of this you way want to talk to informix and find out what the engine is doing in a case like this. It may be generating a temp table for all A='X' and then scanning this table for all B='*Y*'. In general it is not advised to do '*Y*' for queries that need to be fast. If you know any portion of the begining of the string ('XX*Y*') will help greatly.... I hope this helps...... Russ..... }---------- }From: Guy Germonpre[SMTP:germonpre_guy/dis@bekaert.com] }Sent: Friday, June 20, 1997 4:58 AM }To: informix-list@rmy.emory.edu }Subject: OWS performance problem } }Hi } }I have an Informix Online Workgroup server 7.20.UC2 on SCO Unix 5.0.2 }After first install, it was dead slow. I did an update statistics high }and it worked much quicker. } }One problem stays : }if I have a composed index (A,B) and i do a select "where A="X" and B }matches "*Y*" it takes }minutes before i have an answer. If I drop the index, it is much }quicker. } }sqexlain.out says that with the index, OWS uses the index, without, he }does a seq.scan. I changed OPTCOMPIND to 0, I did an update statistics }high on A and on B without result. } }HELP ! } }Guy }