Performance and fragmented indexes
Posted in 2004
We are in the process of moving our application and supporting COTS components from Solaris 2.7/IDS 7.31.UC4 (good riddance) to IDS 9.40.UC4 with Redhat Enterprise Server 3.0 (hello old wine in new bottles). We finally have the opportunity to benchmark our Java servlet-based application (a rather plain vanilla OLTP information system) running on Tomcat 4/5 using different data distribution schemes with Informix. The databases themselves are tiny: most don't approach 1 GB, with the largest about 1.25 GB. Up until now we've used the brain-dead separate individual dbspace for index and data, each dbspace restricted to a single device, mostly due to resource contraints. Now, two other experienced Informix DBAs have told me that if the data is fragmented across separate devices, attached indexes deliver better performance. I've read the posts to CDI from the past few years, but they don't seem to quite answer the question. What has been your experience: Do detached indexes yield better performance? Or does it depend on other factors -- table size, number of users, RAID configuration, etc.? If so, how do these factors affect performance?