Re: To fragment or not to fragment - opinions requested

Spokey the Wheeler (spokey@haunted.demon.co.uk) wrote: : In article <43abr6$1emk@news.gate.net> miles@gate.net "Robert D. Miles" writes: : > On a SMP (4 processors) system is it better to fragment all of the tables : > using round robin, or should the tables be put is different or the same : > Dbspace? My main concern is for performance on a database that is normalized : > and uses several joins on queries. Yes we will be changing the design to : > de-normalize some of the larger tables as new software is developed. : I think (and this is just a thought, mind! :-), that you should have a look at : your main queries and see if you can't determine an expression for fragging : that would allow you to scan in parallel. It's a bit involved, so I'm going to : opt out and suggest that you look in the Performance Tuning manual for 7.1, : as I'm sure Informix would sue me for a copyright breach if I just quoted huge : chunks of it on the 'net! :-) : In general, I wouldn't advise round robin *or* putting them in the same : dbspace for a large table (unless you have a specific set of circumstances). : If the table is split according to an expression, the optimiser has a better : idea of where to find specific sets of records. : Carter - the Unstoppable Sex Machine / Wheeler - the Unstoppable Mail Machine! ^^^^^^^^^^^^^^^^^^^^^^^^^^^^ First of all, the manifestation of your personal problems in this forum is unnecessary. Second, I think that Mr. Miles needs to determine if his app is mainly DSS or OLTP oriented. Fragmenting by expression has diminishing returns on large tables used for DSS purposes while fragmenting by expression can be extremely useful for OLTP apps. (All of that came from the performance tuning class). My first attempt with fragmentation yielded unsatisfactory results. Logically, I thought that detaching my indexes and putting them on separate drives away from the data would improve performance. I was sure to update statistics and all that after creating the indexes also. What I found was that my 2 second response time went to minutes! When I recreated the tables and left the indexes attached like in previous releases, performance went back to 2 seconds. I called it in to Informix but have yet to hear back..... -M. Reed, Radiix, Inc., Ann Arbor, MI, 313-677-UNIX email: mreed@radiix.com