SQL performance

I have a query regarding something we have noticed using New Era and wondered whether it a bug in New Era or is related to the database. In New Era we have been preparing SQL Select statements with ? placeholders so that we could use the same prepared statement over and over again. What we noticed is that sometimes performance was abysmal, and by using set explain we could see that the reason for this was that the select was performing a sequential scan even though the column we provided data for was indexed. When we re-wrote the code so that we re-prepared the select statement everytime, providing the actual value to the SQL statement i.e. no longer using placeholders, for example WHERE order_number = myVariable then the performance was as you would expect. My question is that if you prepare an SQL statement using placeholders does the query optimiser choose a different strategy to that if you supplied the actual values to be used when preparing the statement? BTW we are using New Era 2.11 against IDS 7.30 on NT. I also have another question. Does the positioning of tables in the FROM clause or the positioning of joins or filter criteria in an SQL select statement affect performance? Regards Shaun Campbell

Shaun Campbell wrote: > > I have a query regarding something we have noticed using New Era and > wondered whether it a bug in New Era or is related to the database. > > In New Era we have been preparing SQL Select statements with ? > placeholders so that we could use the same prepared statement over and > over again. What we noticed is that sometimes performance was abysmal, > and by using set explain we could see that the reason for this was that > the select was performing a sequential scan even though the column we > provided data for was indexed. > > When we re-wrote the code so that we re-prepared the select statement > everytime, providing the actual value to the SQL statement i.e. no longer > using placeholders, for example WHERE order_number = myVariable then the > performance was as you would expect. > > My question is that if you prepare an SQL statement using placeholders > does the query optimiser choose a different strategy to that if you > supplied the actual values to be used when preparing the statement? > > BTW we are using New Era 2.11 against IDS 7.30 on NT. Yes it can, especially for a fragmented table. The problem is that the optimizer does not know what the value of the replacable parameter will be when the cursor is opened or the statement executed so it cannot use data distributions to determine the best query path depending on that value. Instead the optimizer must try to guess the best method based on averages and the lower level stats that LOW generates. It is often wrong in these cases. HOWEVER, since you have 7.30 you could try to use optimizer hints, since they are couched in ANSI std comments you should be able to sneak them by the New Era syntax parser, so long as it does not strip the comments out. > I also have another question. Does the positioning of tables in the FROM > clause or the positioning of joins or filter criteria in an SQL select > statement affect performance? No it does not. The optimizer calculates ALL possible versions of the join conditions and order of evaluating the several tables unless you SET OPTIMIZATION LOW and even then it finds the best table to start with then the best second table assuming the first is already selected, etc. SET OPTIMIZATION LOW can reduce long optimization times for joins of more than four tables and while it may result in a less than optimal solution the savings of the optimization time will outweigh the additional runtime of the sub-optimal query in all but the longest queries. FYI. Art S. Kagel