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

OK, I'll try and give a little more info. I'd be interested to try out some of your ideas to speed informix up. We're using Universal Server 9.14.x on Compaq NT, can't remember the exact specs on hand, as I'm home now for the weekend and i never installed it anyhow. Its dual CPU and will definitely have more than 240m ram, possibly more like 512m. No we don't use LIKE for our application queries, but I was trying to have a compatible benchmark. LIKE was used as a test on both servers. We use a special search plug in ETX, that copes with fuzzy logic etc, and returns in about the same time as a LIKE. Issues to consider are, the use of ETX ("excalibur text search blade") which prevents us from running update statistics. (It kills the etx index) So we have 2 copies of the table, one for the etx blade and a base table for all other queries. Excalibur seems to return in about the same time as LIKE, approx 40 seconds. The searchable column used to be VARCHAR(255) and now its a LVARCHAR but performance seems the same. On average, they have approx 200 characters. In Access, we searched on a MEMO and a text column. Re the other simple query, I grouped by 1 integer column, 1 char(5) column, and I counted the rows, plus summed a float. It still surprises me how slow this is. Informix has indexes on both the integer and char(5) column. We are using Informix Web Blade and I assume this is using shared memory. (its running on the same server) Although concurrent users are possible, these tests were performed with only 1 user logged in. BTW, we are currently reconsidering our choice of index. So if anyone has any comments to make regarding ETX, or Varity VTS, or other alternatives then please do so. We want a very fast index to search on approx 200 character column within approx 20 million rows. Substrings, phrase and letter transpositions a must. It would be desirable if we could insert bulk data while the index is active, but not mandatory. Should be able to make bulk insertions of data available within 1 hour. Searches should return within 1 minute. Indexes should preferable make use of other SQL filters, eg ETX does not. Cheers Reuben Boo <boo@boo.co.nz> wrote in message news:8o263t$fta$1@news.wave.co.nz... > 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 > > > >

reuben wrote: > > OK, I'll try and give a little more info. I'd be interested to try out some > of your ideas to speed informix up. > > We're using Universal Server 9.14.x on Compaq NT, can't remember the exact AHAH! This could be the whole problem. IDS/US 9.1x had MAJOR performance problems on anything even remotely OLTPish. Definitely upgrade to IDS v9.21 and look to eventually upgrade to 9.30 when it comes out and has had time to stabilize. 9.1 was based on the VERY OLD 7.0 codebase while the 9.21 is based on the 7.31 code base and is MUCH faster with FULL OLTP support. > specs on hand, as I'm home now for the weekend and i never installed it > anyhow. Its dual CPU and will definitely have more than 240m ram, possibly > more like 512m. > > No we don't use LIKE for our application queries, but I was trying to have a > compatible benchmark. > LIKE was used as a test on both servers. We use a special search plug in > ETX, that copes with fuzzy logic etc, and returns in about the same time as > a LIKE. > > Issues to consider are, the use of ETX ("excalibur text search blade") which > prevents us from running update statistics. (It kills the etx index) So we > have 2 copies of the table, one for the etx blade and a base table for all > other queries. Excalibur seems to return in about the same time as LIKE, > approx 40 seconds. > > The searchable column used to be VARCHAR(255) and now its a LVARCHAR but > performance seems the same. > On average, they have approx 200 characters. In Access, we searched on a > MEMO and a text column. > > Re the other simple query, I grouped by 1 integer column, 1 char(5) column, > and I counted the rows, plus summed a float. It still surprises me how slow > this is. Informix has indexes on both the integer and char(5) column. > > We are using Informix Web Blade and I assume this is using shared memory. > (its running on the same server) > Although concurrent users are possible, these tests were performed with only > 1 user logged in. > > BTW, we are currently reconsidering our choice of index. So if anyone has > any comments to make regarding ETX, or Varity VTS, or other alternatives Verity is a superior product to Excalibur according to my sources. Art S. Kagel > then please do so. We want a very fast index to search on approx 200 > character column within approx 20 million rows. Substrings, phrase and > letter transpositions a must. It would be desirable if we could insert bulk > data while the index is active, but not mandatory. Should be able to make > bulk insertions of data available within 1 hour. Searches should return > within 1 minute. Indexes should preferable make use of other SQL filters, eg > ETX does not. > > Cheers > Reuben > > Boo <boo@boo.co.nz> wrote in message news:8o263t$fta$1@news.wave.co.nz... > > 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 > > > > > > > >