informix sequential scans are blazingly fast

here is something we have found over the last months in trying to speed up some ESQL/c programs we have with out HP9000/Hp-Unix that uses informix 7.3 Dynamic Server. I hope this is helpful for anyone adjusting to ESQL and DB-based apps as I am. informix sequential scans of tables are REALLY fast. On a table with a row size of 500, we can scan through 10-20K rows per second. When processing large batches, we try to read an entire table into memory (usually hashing) and look up the info in memory. This is implementing in code what a hash join is doing. My boss thinks it is due to informix's recent focus upon data warehousing : the buffering schemes seem to be very well tuned for fast scanning. We have also had success using hash-joins over nested joins that involve an index lookup, which would be expected from the experience with sequential scans. If you can afford to put alot of memory on your boxes, try to use sequential scans to load huge amounts of data in memory! Han Sent via Deja.com http://www.deja.com/ Before you buy.