Re: Query Optimizer Weirdness in Stored Procedure

Use optimizer directives ? Or, prepare the statement with inplace value (not with the question mark placeholder). May be it will help. Best Regards, Octav Richard Auslander wrote: > > This is a multi-part message in MIME format. > --------------4B602889D8BAD06C703FCEBA > Content-Type: text/plain; charset=us-ascii > Content-Transfer-Encoding: 7bit > > (Environment: Sun Enterprise/450, SunOS 5.6, Informix 7.30UC5) > > Greetings All! Has anyone out there encountered this same problem, and > figured out a solution? I have a single table with over 10 million > records in it. I am trying to retrieve a small number of records from > it based on a "LIKE" expression against a indexed non-unique varchar() > column - assume it's a name of some sort. The user can type in the > first "n" letters of a search string, and then my program appends a "%" > to the end of the search string. The resulting WHERE clause is > something like "... WHERE varchar_column LIKE 'INFORMIX%'". If I > perform the query using "dbaccess", I get extremely fast response - > sub-second in every case. However, when I perform the *identical* query > in a stored procedure, it runs for 15 minutes (and works, too). > Investigating the "sqexplain.out" information gives me a clue: since the > optimizer cannot determine whether the search literal (the target of the > "LIKE" function) BEGINS with a wildcard or not, it assumes that it > could, and optimizes accordingly, instead performing a serial scan on > those 10 million records. In effect, I wish Informix had a "BEGINS > WITH" function instead of the "LIKE" function, so the stored procedure > query optimizer could take advantage of my index on the name column. > Any suggestions? > > Rich > -- > Richard C. Auslander > Database Manager > > AirFlash, Inc. > 1733 Woodside Rd., Suite #110 > Redwood City, CA 94061 > (650) 556-7928 > > www.airflash.com > > --------------4B602889D8BAD06C703FCEBA > Content-Type: text/x-vcard; charset=us-ascii; > name="rich.vcf" > Content-Transfer-Encoding: 7bit > Content-Description: Card for Richard Auslander > Content-Disposition: attachment; > filename="rich.vcf" > > begin:vcard > n:Auslander;Richard > tel;fax:650-556-7930 > tel;work:650-556-7928 > x-mozilla-html:FALSE > url:http://www.airflash.com > org:AirFlash, Inc. > adr:;;1733 Woodside Road, Suite 110;Redwood City;CA;94061;USA > version:2.1 > email;internet:rich@airflash.com > title:Manager of Database Services > fn:Richard Auslander > end:vcard > > --------------4B602889D8BAD06C703FCEBA-- -- Octav Chiriac Phone: (373) 2 22 99 67 NetInfo S.R.L. Fax: (373) 2 21 36 59 Chisinau (373) 2 22 84 88 Moldova, Republic of mailto:com@netinfo-moldova.com