Re: Temporary dbspace

bozon wrote: > Temp tables with no log are fragmented across the temp dbspaces as are > other things (like sorting merge files, it seems like index builds and > group by's use a merge sort algorithm). So I think it is a benefit to > have more than one temp dbspace. I never thought of using an odd number > of spaces though. I would be skeptical that 3 would be better than 4. I > would of course agree if you told me 3 was better than 2. Skeptical of > course means I reserve the right to say I agreed all along if given > facts about why 3 is a magic number of dbspaces (3 in schoolhouse rock > was a magic number). 3 temp dbspaces because of how the merge-sort works. It reads two sort-work files writes the results to a third. With PSORT_DBTEMP you'd want to use 3 or more dbspaces for the reason that the algorithm is smart enough to put the result file in a different FS than the two source files. When using temp dbspaces for sort-work files each file is fragmented round-robin across all of the dbspaces, but IB the algorithm rotates the starting dbspace for each file so that it can read from and write to different ones during the merge operation if there are 3 or more temp dbspaces. That's why 3. More than 3 is OK, there's just not much additional gain from the 4th+ dbspace. It's the 3rd one that improves sort performance for larger sorts above using 1 or 2 temp dbspaces. > Since, disks are much slower than processors it seems like you should > be able to service more than 1 tempdbs with 2 processors. So I am not > sure what you were thinking about only one temp dbpace given that you > have only 2 processors. We have our temp dbspaces on a ram disk, it > seems improve latency and seek times ;-) > Art S. Kagel