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

From: "reuben" <news@boo.NOSPAM.co.nz> > >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. Upgrade to 9.20 or 9.21, definitely. 9.14 still has (I think) the 7.1 "optimizer". >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. Can't you UPDATE STATISTICS on the non-ETX columns? >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. Quite often, the optimizer makes strange decisions, like group first and filter afterwards. >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. I don't think any of the text DataBlades support standard SQL functions. Rumour has it that ETX is not due for any more upgrades, so I guess Verity is a better bet. ________________________________________________________________________ Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com