Re: Performance Problem with Temp Table

jerry, try creating your temp table with the 'with no log' option: create temp table tmppo( doc_no integer, po_no char(24), net_amount decimal(13) ) with no log; see the sql ref about when temp tables with this option are dropped. } } We are having a performace problem with inserts into a temp table. } } The configuration is a Motorola 88K Dual Processor running Online 4.1. } } We are creating a temp table with the following command: } } ###################################################################### } } create temp table } tmppo( } doc_no integer, } po_no char(24), } net_amount decimal(13) ) } } ###################################################################### } } We are then selecting information into this table with this 4GL code: } } ###################################################################### } } #_prep_insert_cursor } if po_prep is null } then } } #_define_cursor } let scratch = } "insert into tmppo ", } "select stoordre.doc_no, stoordre.po_no, ", } "sum(stoshipd.net_amount) ", } "from stoordre, stoshipd ", } "where (stoordre.doc_no = stoshipd.doc_no) ", } "and (stoordre.cust_code = ? ", } "and stoshipd.stage not in ('NEW','CAN','PST')) ", } "group by stoordre.po_no, stoordre.doc_no " } } prepare po_fill from scratch } } #_init_prep_flag } let po_prep = "Y" } } end if } } #_insert_into_tmppo } if p_oordre.cust_code is not null } then } } #_insert } execute po_fill using p_oordre.cust_code } } end if } } ###################################################################### } } Here is the results of "set explain on" } } ###################################################################### } } QUERY: } ------ } insert into tmppo select stoordre.doc_no, stoordre.po_no, } sum(stoshipd.net_amount) from stoordre, stoshipd where } (stoordre.doc_no = stoshipd.doc_no) and (stoordre.cust_code = ? and } stoshipd.stage not in ('NEW','CAN','PST')) group by stoordre.po_no, } stoordre.doc_no } } } 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. } } If anyone has any clue about this I would appreciate any information } that you could provide. } } -- } Jerry M. Denman -- Director of Technical Services } Sherwood Systems (a division of Sherwood Mfg Co. Inc.) jerry@sherwood.com } 4760 N. Central Phone:(602) 230-8188 } Phoenix AZ, 85012 FAX:(602) 230-9491 } ------------------------------------------------------------------------------ } A true realist demands the impossible } A true idealist demands the impractical } -- regards, +----------------------------------------------------------------------------+ | . . | | | ... ... | Bob Baskett | | ..... ..... | Software Engineer, DBA | | .. ... .. | Business Systems Integration Group | | . . . | Semiconductor Products Sector | | | Mesa, AZ | | Motorola, Inc. | | |----------------------------------------------------------------------------| | 'connectionLESS IS MORE' -- Data Broker | |----------------------------------------------------------------------------| | Duct tape is like the force. It has a light side, and a dark side, and | | it holds the universe together ... | | -- Carl Zwanzig | +----------------------------------------------------------------------------+