Re: Query performance: 16 secs vs 5+ hrs
Ordering by field1, field2 gives a very stromg suggestion to the optimizer
to use the filed1, field2 index. The fact that your where clause uses
field1 and field2 should make this index a candidate also; but apparently
the optimizer is not making the correct decision without your help
with the order by. My guess is that you have few rows (as compared to the
total number of rows in the table) where field2 < "number".
If you really want to know what is going on use SET EXPLAIN (i.e. in dbaccess
issue set explain; then your SQL). The optimizer will dump it's access
path plan to a file named sqexplain.out so that you can see the access
paths used to execute the query.
-Bill
}
} <HTML>
} I can run the following query against a table with one minor (!?!) change
} and vary the return times from 5+ hours to <16 secs:
}
} <P>select field1, field2, field3, field4, field5, field6, field7 from "dba".table
} where field2 < "number" and field1 >= "date" order by field1
}
} <P>The query above finishes in 5+ hours, but by adding field2 to the order
} by clause, I can reduce the run time to <16 secs:
}
} <P>select field1, field2, field3, field4, field5, field6, field7 from "dba".table
} where field2 < "number" and field1 >= "date" order by field1, field2
}
} <P>The table has 3 indexes on:
} <BR>field2, field1
} <BR>field1
} <BR>field7, field1, field2
}
} <P>I do not understand what is happening here. Can anyone shed some
} light on the difference between the scripts. Thanks</HTML>
}
}
--
Bill Ennis Voice: 312-474-7516
SSA Fax: 312-474-7460
500 W. Madison email: ennis@ssax.com ennis@accesschicago.net