OPTMIZATION

Some of our users use a package called Brio to query the Informix database. Since we have migrated to IDS 7.31 FC4 some of their queries are not choosing the optimial path. When I run these same queries with OPTMIZATION set to first_rows they run much faster. The only problem is there is no way to add the 'set OPTMIZATION first_rows' statement to the query in Brio. Is there any way to add this set parameter in the ODBC configuration or to their userid? Thanks for any help Keith A. Lewis The Timken Company lewis@timken.com (330) 471-4119 (330) 471-4034 (FAX)

If you are using an ODBC driver with a server component (such as OpenLink, but there are others) you can set up a standard set of SQL statements (including any of the Informix set... commands) to be run whenever an ODBC connection is established. With OpenLink (and possibly others) the script to be executed can vary by user, client IP address/host name, application name, etc. Alternatively, you may have other configuration/optimization issues which may solve the performance problem without setting the optimization to "first_rows". Using "first_rows" may actually slow down overall query performance, although the results start to come back sooner. Appropriate "update statistics" commands grow ever more important with each "improvement" to the query optimizer, so make sure the appropriate update statistics are being done. There are several utlities available for automating this, including one of my own design which can be found at www.objectsoft.com . Finally, if memory serves, I think Brio Query does have a way of executing some SQL upon connection or even upon executing a particular query or accessing a particular data model. This is usually used for implementing some sort of sceurity audit trail feature. HTH In article <8li4oq$1kh$1@news.xmission.com>, "Lewis, Keith A." <Lewis@timken.com> wrote: > > Some of our users use a package called Brio to query the Informix database. > Since we have migrated to IDS 7.31 FC4 some of their queries are not > choosing the optimial path. When I run these same queries with OPTMIZATION > set to first_rows they run much faster. The only problem is there is no way > to add the 'set OPTMIZATION first_rows' statement to the query in Brio. Is > there any way to add this set parameter in the ODBC configuration or to > their userid? > > Thanks for any help > > Keith A. Lewis > The Timken Company > lewis@timken.com > (330) 471-4119 > (330) 471-4034 (FAX) > > -- Irwin Goldstein Objective Software Systems, Inc. http://www.objectsoft.com Sent via Deja.com http://www.deja.com/ Before you buy.