SLOW JOINS ON SE

F> From: foy@ix.netcom.com (Jim Foy) > Subject: Slow JOINs on SE > Date: 12 Jun 1995 17:14:10 GMT > Organization: Netcom > To: informix-list@rmy.emory.edu F> We are porting a product from Oracle7 to InformixSE, and are seeing > mojor performance hangups with SELECTs involving a view or involving a > join. It takes around 10 seconds for a response against a view that > involves 3 tables, each with less than 10 rows. The joins are against > indexed fields. We see the same problem on our InformixSE for Windows > platform, and on our customers RS/6000 platform. The first thing that came to my mind was "why are there indices on 10 row tables? The Informix documentation claims that you will be penalized in performance when indexing columns on tables with less than 100-200 rows in them. The second thing that comes to my mind is your select statement, as, even with indices, you shouldn't be needing 10 seconds to complete the select. Post your sample select. F> Is this performance a known problem? Tech Support says that SE is just > slow, and suggest that a $20K upgrade to OL will solve the problem... That's the last thing that comes to my mind. As a point of reference only, I built an app that had a three table join, and my own benchmarks showed that an interactive query of @10,000 records on a "live" 26 user SCO UNIX 486/33 box required 9-10 seconds to complete. BTW, the hardware was an IBM PS/2 95, 48MB RAM, SCSI HD (Standing on the soapbox): It seems as though the marketing answer is always to buy something else, to spend more money on the company. Informix is not solely to blame here, you get that jibe from (nearly) every salesman. --- ~ SPEED 1.40 [NR] ~