Re: MS Access performed faster than Informix in 800000 row tests

Were there other users on the Compaq? Connected to Informix and doing work? What was the OS doing on the Compaq at the time? How well is the IDS engine tuned? How many BUFFERS does the engine have configured? How many extents does the table have? Are all those indexes attached or detached? For all but the simplest queries detached indexes improve performance. HOW was UPDATE STATISTICS run, with what options and at what level(s)? Did you runthe queries under SET EXPLAIN ON to determine if the query plan could be improved by adding indexes, changing OPTCOMPIND, fragmenting the table? Other considerations beyond single user single query speed: Have you tried the same or similar test with your normal user load? Do you always run single user? If so maybe Informix SE is a better choice it shines at single user simple queries while IDS shines on multi-user complex queries. Why not to dump Informix? Because you are not finished testing it yet! Oh, BTW, is this a Troll? Art S. Kagel Boo wrote: > > I was recently asked to copy some data from an informix server to ms access > so a customer could run some queries. > > Just for interest i ran my own queries first. > > There are 839000 rows in a table in both databases. > Informix is on a big compaq server with 2 cpus and lots of ram. > MS Access is on my pIII-600 with a single cpu and only 120meg ram. > Access had no indexes. Informix has plenty. Update statistics has also be > set. > > I ran a query using LIKE on memo fields and access came back within a minute > with 13 thousand matches. > Informix performed not much faster on VARCHAR or LVARCHAR using Excalibur > search index or even a standard LIKE. > > I also ran a simple agregate query, grouping on 2 columns, agregates on 2 > columns, to return approx 300 results from the 800k odd. Access time approx > 40 seconds, informix over 2 minutes. > > And informix had always been my favourite. > > Funny eh! > > Anyone want to tell me why I shouldn't give Informix the boot? > > Cheers > Reuben > > For a better WEB DRIVER than Informix Web Blade, www.boo.co.nz/disco > For lots of funny jokes goto www.boo.co.nz/jokes