Re: Database Optimiser Cost Calculation : Suggestion for formula

Ronnie wrote in message <8v2cek$8gs$1@clematis.singnet.com.sg>... >My program allows user to formulate their query via drag and drop. > >Unfortunately, many of the tables are really big : hundreds of thousands >and millions of rows. I need to access different databases (Oracle,Informix, >SQL Server). > >I need to be able to build a cost estimate of the query and then prompt the >user whether he wants the query run. > >....... > >Any suggested formulas? If you are using a language which allows you to prepare/declare the statement, and you have access to the sqlca record as well, then you could get a rough estimate from the Informix engine itself - see sqlca.sqlerrd[4] (if your language counts arrays starting from 1, or [3] if your language starts counting from zero). Check out the Informix manuals. This is a guesstimate from the engine, so it's really not that reliable, but differences in the orders of magnitude are generally significant. The only gotcha is that some queries involve the building of internal temporary tables, so you may get a big delay followed by a rush of data at the end, and it's difficult to predict when that is going to happen. Can't help you for the other engines. Another thought: the informix manuals have a chapter called "Optimising your queries" which talk about the way the Informix engine makes its predictions. Whether you can use the same formulas to do your own predictions depends on your commitment, and there's no saying whether it would make the slightest sense with other engines anyway. It would however give you a feel for the difficulty of the task. Good luck on your mission.