Low performance on temp tables with 7.31.FD3 on Digital Unix when created in tempdbs

Hello, we have some strange performance isssues with temporary tables. tempdbs is our extra dbspace for temporary tables. 2 tempdbs 1 09/11/2000 N T CHUNKS FOR tempdbs Chunk Chunk Pages Pages Full Pathname of Chunk Status Id Offset In Chunk Used 2 0 1000000 3603 /dev/rvol/testdbdg/rawvol02-rz24f PO- from onconfig -c # DBSPACETEMP: # Dynamic Server equivalent of DBTEMP for SE. This is the list of dbspaces DBSPACETEMP tempdbs # Default temp dbspaces Now during program execution there are some temp tables created and one of them shows a bad performance behaviour. DECLARE q_kontroll CURSOR FOR SELECT * FROM t_tmp_kontroll WHERE nr_fmass >= 0 AND nr_antrag < 0 AND nr_tierbestand > 0 FOR UPDATE FOREACH q_kontroll INTO lr_kontroll.* ...... do something. END FOREACH there is an index on nr_fmass, nr_antrag and nr_tierbestand and a select count(*) from t_tmp_kontroll using the conditions from thedeclare statement shows that there are no matching rows (from about 8000 rows in the table). However a time display before the declare and after the end foreach is showing that this code is consuming about 8 minutes to complete. Now we tried the following: explicitly creating the temporary table in a "normal" dbspace (on our case daten2, which is the dbspace which holds all the other tables in the database) with the "no log" option. And this greatly improves performance. Time for processing the select is now below 1 second. Is there any known issue about problems with tempdbs usage?

J?rg Spilker wrote: > Hello, > > we have some strange performance isssues with temporary tables. > tempdbs is our extra dbspace for temporary tables. > > 2 tempdbs 1 09/11/2000 N T > > CHUNKS FOR tempdbs > > Chunk Chunk Pages Pages Full Pathname of Chunk > Status > Id Offset In Chunk Used > > 2 0 1000000 3603 /dev/rvol/testdbdg/rawvol02-rz24f > PO- > > from onconfig -c > > # DBSPACETEMP: > # Dynamic Server equivalent of DBTEMP for SE. This is the list of > dbspaces > DBSPACETEMP tempdbs # Default temp dbspaces> > Now during program execution there are some temp tables created and > one of them shows a bad performance behaviour. > > DECLARE q_kontroll CURSOR FOR > SELECT * FROM t_tmp_kontroll > WHERE nr_fmass >= 0 AND > nr_antrag < 0 AND > nr_tierbestand > 0 > FOR UPDATE> > FOREACH q_kontroll INTO lr_kontroll.* > > ...... do something. > > END FOREACH > > there is an index on nr_fmass, nr_antrag and nr_tierbestand and a > select count(*) from t_tmp_kontroll using the conditions from the> declare statement shows that there are no matching rows (from about > 8000 rows in the table). However a time display before the declare and > after the end foreach is showing that this code is consuming about 8 > minutes to complete. > > Now we tried the following: explicitly creating the temporary table in > a "normal" dbspace (on our case daten2, which is the dbspace which > holds all the other tables in the database) with the "no log" option. > And this greatly improves performance. Time for processing the select > is now below 1 second. > > Is there any known issue about problems with tempdbs usage? Yes ;) Bug 164089. It is related to the unnecessary checking of locks. Some other workarounds : Just create the temp table without the "with no log" and it will be logged (i.e. placed in a logged dbspace). Reduce the amount of locks the session has by locking tables exclusively - this may be impractical.

Hello, > Yes ;) Bug 164089. hm, i didn't find this bug in the IDS 7.31 release notes? Can you please post the bug description for me? > > It is related to the unnecessary checking of locks. > > Some other workarounds : > Reduce the amount of locks the session has by locking tables exclusively > - this may be impractical. strange. As this was practical for the application, we did lock all temp tables exclusive without solving the problem. Ok, hopefully this bug is fixed in the 9.X IDS release to which we will change soon. Greetings, J'rg