Are temporary tables cached during writing?

Hello everybody. Our application intensivly uses temporary tables and it sufficiently decreases a total performance. Of course I have dedicated three temporary dbspaces on three different disks but it doesn't seem to help. I want them to be cached in buffers but I can see that they are written to disk. To see that I used onperf and monitored disk usage parameters. At the same time I have enough free buffers to cache all the temporary tables. Checkpoints occurs only every 15 minutes. What should I do to cache them in my buffers? Thank you for any advice. Anatoly Nelyubin System administrator, SCSA

Temporary tables do get cached in the normal buffers. What you are probably seeing is the nominal allocation of the tablespace for the tables. If your temporary dbspaces are marked with the T flag, then you can reduce the amount of logging. SQL also supports a syntax: create temp table ...... with no log and that could be a worthwhile thing to try. Good use of temporary tables can improve performance. Why use them if they are not being used properly? It's purely a programming issue. Optimising SQL statements is an art as well as a science, and it's well worth learning the optimisation tricks from the Informix manuals. I've seen 16 hour reports turn into 20 minute reports merely with appropriate rewrites to take advantage of the engine, and that included ADDING use of temp tables, as well as appropriate use of cursors in the code instead of endless "inline" select statements. Finally, have a look at table fragmentation so that you may use PDQ. It can give you some great performance benefits if you have a really difficult job to do. Anatoly Yu. Nelyubin wrote in message <3A1E0EC1.2CFD2F54@permonline.ru>... >Hello everybody. > >Our application intensivly uses temporary tables and it sufficiently >decreases a total performance. Of course I have dedicated three >temporary dbspaces on three different disks but it doesn't seem to help. >I want them to be cached in buffers but I can see that they are written >to disk. To see that I used onperf and monitored disk usage parameters. >At the same time I have enough free buffers to cache all the temporary >tables. Checkpoints occurs only every 15 minutes. What should I do to >cache them in my buffers?