Re: Using TEMP Tables

> Do temp tables take more overhead than a "permanent" table? Temp tables are better because you can use WITH NO LOG and there is no locking contention. In version 6+, you can create temp dbspaces on separate disks and Online will automatically round-robin the temp tables across them. > Does it take longer to retrieve things from it? It doesn't seem to. You can also create indexes on temp tables. > Any extra overhead when dropping the table? Creating and dropping table does have an overhead so you should only do it once per session i.e. don't drop and recreate within an application, use delete instead. You can also create them by using SELECT .. FROM table WHERE field = -1 INTO TEMP tmptab WITH NO LOG. We prefer this because we don't have to change the code if any of our column definitions are altered. The SELECT can also be a join and can include literals. If you use a join however, remember to put in the full join criteria and a filter for each table otherwise it will sequentially scan all the tables. > We load an average of a couple hundred rows or less. The size of the row > is 2 char columns, total row size is 50. If the max number of rows is known, use module arrays. We found this to be up to ten times faster. > Steve Goldberg > Landata RE-Source > goldberg@landata.com David W Chan Objective Research Ltd dwc@cix.compulink.co.uk