Re: Bad performance with DBSPACETMP enabled

rainer.von-bongartz@dil.de wrote: > > I'm currently using OnLine 7.23 on a HP K460 server. > > I have one big table (appr. 1 GB) for which we do an "UPDATE STATISTICS HIGH" > on a regular basis. > When using 3 temporary dbspaces on raw devices, the running time for this task > is about 2 hours. > > If I dont't use temporary dbspaces, so that every temporary object is placed > in the UNIX file system /tmp, the execution time drops down to 50 minutes. > > Anybody having any ideas about the reason for this bad performance, which also > occurs when creating indexes. > Should I use temporary dbspaces on raw devices at all or let OnLine better > use /tmp ??? You have stumbled on a little known fact. Sorting is faster to filesystem space than to temp table space. And since you do not care about the safety of those sort-work files that's OK. Even better still, for large sorts like index builds and UPDATE STATISTICS, set the environment variable PSORT_DBTEMP to as many different filesystems as possible (at least 3, keeping in mind that the smallest will limit the size of the sort that is possible). Also, contrary to the documentation, setting PSORT_NPROCS to a value between 20 and 40 will also speed the sorting as long as you can afford to allocate so many resources to that one task. You might see the time drop to 15 or 20 minutes. Art S. Kagel