Re: Performance Problem with Temp Table

> We are having a performace problem with inserts into a temp table. > > Estimated Cost: 269 > Estimated # of Rows Returned: 13 > > > Temporary Files Required For: Group By > > 1) informix.stoordre: INDEX PATH > > (1) Index Keys: cust_code > Lower Index Filter: informix.stoordre.cust_code = 'NIPPN1' > > 2) informix.stoshipd: INDEX PATH > > Filters: informix.stoshipd.stage NOT IN ('NEW' , 'CAN' , 'PST' ) > > (1) Index Keys: doc_no line_no ship_no > Lower Index Filter: informix.stoshipd.doc_no = > informix.stoordre.doc_no > > ###################################################################### > > As you can see, there is no obvious problem with the cost of the > query. The problem is that it is taking 80 seconds to run that insert > statement. The temp table is empty at the time of the insert. > > Jerry M. Denman -- Director of Technical Services Jerry, Bob comment about No Log on the temp table is valid but it is also true that you haven't made a case that there is a problem. If the tables you are selecting from have large numbers of records that it has to read to filter out the rows to be inserted or if the select retrieves large numbers of rows then 80 seconds may not be a problem. If it is looking at 100,000 rows and inserting 50,000 maybe 80 seconds is not bad performance!! Set explain only gives estimates. It has no really usefull knowldegeof data distribution (Role on V6). The fact that an index is used doesn't mean that in the end it doesnt read 2/3 of the input data file if your customer NIPN1 happens to be the main customer in the data file and there are 100,000 docs attached to it. Also you are doing a group by which means that the records selected by the filter are written to a flat unix file and sorted before being written to the temp table. Is it necessary to create an order in the temp table? If it is could raising an index on the temporary table after it has been written be faster than using unix sort on the output of the select? Or just use an order/group by on the select from the temp table to do a unix sort. Cheers - Jim My opinions are my own. They may vary with time but they remain MINE! ---------------------------------------------------------------------- Name: Jim Gordon Internet: jgordon@ssf-sys.DHL.COM Company: DHL Systems Inc Phone: (415) 375-5222 (Work) Address: 700 Airport Blvd. #300 (415) 775-7762 (Home) Burlingame, CA 94010-1937 Fax: (415) 375-5019 ----------------------------------------------------------------------