Re: Query Performance Problem SE 5.06.UC1
On May 10, 4:13pm, Apostolos Varnas wrote:
} Subject: Query Performance Problem SE 5.06.UC1
} One of our clients with following environment:
} Hardware: Olivetti SNX 400 with RAID
} OS: UnixWare 2.01
} INFORMIX SE 5.06.UC1
} ISQL 4.14.UC1
}
} is facing following problem:
} The optimizer doesn't select the appropriate index and the response time
} varies from seconds to 30 minutes as also the no of rows found in the same
} SQL statement.
} The update statistics statement has been run.
} The index is checked with bcheck with no problem.
} set optimization low, alter index to cluster etc didn't have any effect
} at all.
} Why does the Optimizer not choose the right index ?
} Has someone with this environment faced a similar problem ?
} Here is the table & index structure:
} create table position
} ( firmaid char(4),
} aufnr integer,
} aufpos integer,
} ausgabe char(4),
} artnr char(15),
} artlandkz char(3),
} .....
} )
} row length is 1089 and there are 108392 rows in this table
} create unique index position_idx1 on position (firmaid,aufnr,aufpos);
} create index position_idx2 on position
} (firmaid,ausgabe,artnr,artlandkz);
}
} QUERY
}
} select * from position
} where firmaid = "0200"
} and aufnr = 11586
} and aufpos >= 1
} order by aufpos asc
}
} Time: 28 min
} 6 rows retrieved
} Estimated Cost: 40
} Estimated # of Rows returned: 1
} Temporary Files Required for: Order by
} 1) informix.position: INDEX PATH
} Filters: (informix.position.firmaid = '0200' and
} informix.position.aufnr = 11586 ...)
} (1) Index Keys: firmaid ausgabe artnr artlandkz
} Lower Index Filter: infromix.position.aufpos >= 1
}
} QUERY
}
} select * from position
} where aufpos >= 1
} and aufnr = 11586
} and firmaid = "0200"
} and aufnr = 11586
} order by aufpos asc
}
} Time: 3 sec
} 0 rows retrieved
} Estimated Cost: 40
} Estimated # of Rows returned: 1
} Temporary Files Required for: Order by
} 1) informix.position: INDEX PATH
} Filters: (informix.position.aufpos >= 1 and informix.position.aufnr =
} 11586 ...)
}
} (1) Index Keys: firmaid ausgabe artnr artlandkz
} Lower Index Filter: informix.position.aufnr = 11586
}
}
} Thanks
}
} Tolis Varnas
Tolis,
Unfortunately the V5 optimiser is not that intelligent. Actually I'm not sure
the V7 one is that intelligent either. I've never been certain why it chooses
the second index. In the past it has sometimes been because the second index
appears first in the sysindexes table. Sometimes it chooses the second index
because I suspect it reads the statistics in such a way that it believes that
it has a smaller set of rows per key value after firmaid than the other one
does. So it thinks that it will have to go through less rows. This despite
that fact that the other index is unique and can only have one entry per row.
Unfortunately it completely misses the fact that you have set the values of the
first two items of the first index and the third item is the sort order.
Thankfully this problem is easily solved by giving the optimiser some help.
Recode your query this way and not only will it use the correct index but it
will stop using a temporary file for sorting.
select * from position
where firmaid = "0200"
and aufnr = 11586
and aufpos >= 1
order by firmaid, aufnr, aufpos
This should work fine. But looking back at your Set explain results you may
have a bigger problem. Are you sure that those are the results generated by
the set explain? Both are showing Index filter usage that doesn't make any
sense.
I hope these are mistypes into the email as if these are actual results then
either set explain or the optimiser has some bugs. Both queries should show
firmaid as the lower index filter and the first query also has a inconsistent
upper index filter.
Cheers - Jim
--
-----------------------------------------------------------------------------
Jim Gordon DHL Airways Inc. jgordon@us.dhl.com
-----------------------------------------------------------------------------
My opinions are my own. They may vary with time but they remain mine!