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

Huh? No ways hoss, we run a query like yours on 1.5 million in seconds, no ways access faster. Now, things to consider, how did you hit these tables? odbc or shm? The compaq box, operating system, quantity of users, lock modes? If a lock was held on a record, your query may have been set to wait with a timeout perhaps? I've seen informix clean up on sql server with no problems brett In article <8o263t$fta$1@news.wave.co.nz>, boo@boo.co.nz says... > 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 > > > > >

How fragmented is the Informix table, disk space wise? The same question for Access table.

Not to sure off hand, but we have 2x 1 gig filechunks. and its not raw sapce, its on NT. <Z@forget.about.it> wrote in message news:39aa0028.30476729@news... > How fragmented is the Informix table, disk space wise? > > The same question for Access table.

Yeah, lock mode is wait, about 1 minute i think. However, there were no concurrent users. (BTW When there are, the 3rd or 4th query seems to get an error, but this is a slightly different issue. ) A single user query has to be faster that what we're doing. BTW, what is the query you're doing on 1.5 million. Is this an agregate?, or a simple text search? If the later, what kind of indexing are you using? bob <bofh@mailbox.co.za> wrote in message news:MPG.140efc762b43d1e098968f@news.dbn.lia.net... > Huh? > > No ways hoss, we run a query like yours on 1.5 million in seconds, > no ways access faster. Now, things to consider, how did you hit these > tables? odbc or shm? > > The compaq box, operating system, quantity of users, lock modes? > > If a lock was held on a record, your query may have been set to > wait with a timeout perhaps? > > I've seen informix clean up on sql server with no problems > > brett > > In article <8o263t$fta$1@news.wave.co.nz>, boo@boo.co.nz says... > > 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 > > > > > > > > > >

Ho there, just noticed your story about it being on NT.... well, are the spaces on a compressed filesystem? NT loves that. I watched a query grind to a halt once because of that Aways, the query: timex dbaccess intratex << END select ins_branch_abbrev, sum (ins_os_amount) from cocinstn group by ins_branch_abbrev END 33 seconds, 60 users concurrent at that time. Ok, this is a 450k row table, the other is getting heavily hit by a job I'm running, so I dont want to access it. we can do text searches quite quickly if you like. Meet the advantages of UNIX and raw disks. brett [This followup was posted to comp.databases.informix and a copy was sent to the cited author.] In article <8o4ai1$18e$1@news.wave.co.nz>, news@boo.NOSPAM.co.nz says... > Yeah, lock mode is wait, about 1 minute i think. > However, there were no concurrent users. > (BTW When there are, the 3rd or 4th query seems to get an error, but this is > a slightly different issue. ) > A single user query has to be faster that what we're doing. > > BTW, what is the query you're doing on 1.5 million. Is this an agregate?, or > a simple text search? > If the later, what kind of indexing are you using? > > > bob <bofh@mailbox.co.za> wrote in message > news:MPG.140efc762b43d1e098968f@news.dbn.lia.net... > > Huh? > > > > No ways hoss, we run a query like yours on 1.5 million in seconds, > > no ways access faster. Now, things to consider, how did you hit these > > tables? odbc or shm? > > > > The compaq box, operating system, quantity of users, lock modes? > > > > If a lock was held on a record, your query may have been set to > > wait with a timeout perhaps? > > > > I've seen informix clean up on sql server with no problems > > > > brett > > > > In article <8o263t$fta$1@news.wave.co.nz>, boo@boo.co.nz says... > > > 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