Re: Performance

Thanks Helen and Jack for the quick response. I restructured the query and have considerably improved its performance. Query time has gone from over 8 minutes to less than 8 seconds. I evaluated the original query with the INFORMIX "SET EXPLAIN" command and found that it was weighted at 218 "units" of work. My revised version of the query weighs in at "37" units of work. To improve performance I broke the original query into 4 separate queries using the technique of substituting sorting in temporary tables to avoid nonsequential disk access. This technique is described in Chapter 4 of the INFORMIX "Guide to SQL Tutorial" - Using a Temporary Table to Speed Queries. (Lots of other good stuff in this chapter too.) >>>> From sixhub!eisinc!helen@hustle.rahul.net Thu Mar 23 06:12:13 1995 X-Mailer: SCO System V Mail (version 3.2) To: peterl@minerva.robadome.com Subject: Re: Performance Content-Length: 3113 X-Lines: 90 I work with the Informix 4.??? standard engine as well and come across the same problem all the time. I found one solution, that is a real pain in the but - but it works. Seems to be (and I'm a newbie so I may not explain this too well) that if you make a temporary table to store the larger tables in, or all the tables in, that the query seems to be faster on the temporary table than in the pre-existing tables. So, my suggestion is to load as much as possible into temporary tables before starting your query. It should cut back on the time, and the hit it takes on the CPU. Helen >>>>> One - SET EXPLAIN ON and see how the optimizer is handling it. Two - My guess is that it is trying to resolve the 'MATCHES' first, which is hard on the engine. Three - try breaking it into multiple statements. have fun. j. _____________________________________________________________________________ Jack Parker yauib* - Hewlett Packard, BSMC Boise, Idaho, USA jparker@hpbs3645.boi.hp.com _____________________________________________________________________________ * yet another unix/informix bigot _____________________________________________________________________________ Any opinions expressed herein are my own and not those of my employers. _____________________________________________________________________________ >>>>> --- Peter Levine | peterl@minerva x 24945 PBX Software Engineering | levinep@pnna (408) 492-4945 Siemens ROLM Communications Incorporated | peterl@minerva.robadome.com