Re: Bad performance with DBSPACETMP enabled

Idiot wrote: > > > 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. > > > I am sure that you have tested this. Please excuse my ignorance. I find it > hard to believe. Are we saying that Informix DBSPACETEMP is not implemented > properly. This is scary because all the temporary tables generated by ORDER > BY and/or GROUP BY uses DBSPACETEMP. No not an implementation problem. It is just that sort-work files written to tempdbspaces have to go through the BUFFER cache which is relatively high overhead. If the PSORT_* parameters are set the ORDER BY, GROUP BY, INDEX BUILDS, and UPDATE STATS processes can bypass the normal Informix page handling and write directly to the UNIX filesystem. While this is slower than raw disk for general data because of the extra layer that UNIX buffering adds, for sort work files removing the buffering layer that Informix imposes on dbspaces (event tempdbspaces) is even more important! In general I use the tempdbspaces simply because when the results of a sort are to end up in a temporary table anyway it is probably just as fast to use the tempdbspaces for sorting. However, as I pointed out in my response, for index builds and UPDATE STATS the PSORT_NPROCS, PSORT_DBTEMP, and PSORT_MAXALLOC=10240 combine to make this tasks blisteringly fast! > I would think this is installation specific. I normally put my DBSPACETEMP > in separate spindle and try to distribute them among different controllers > and never experienced any problem. Someone who has never driven a Maseratti, Lambourghini, or Rolls Royce thinks that their Camaro or Mustang is fast too! Art S. Kagel