Fast Queries

I have the task of converting an old Informix ver 3.3 database running on an Intel 286 microcomputer under Xenix to Informix 4GL ver 4.10 running on a Unisys 5000 under UNIX with Informix Standard Engine. The ver 3.3 database has only .dat and .idx files (along with a .dbd file). There are no Perform screens (.frm) or Ace reports (.arc). Somehow, somebody managed to code a bunch of C programs that access the Informix 3.3 files. I'm not familiar with the old 3.3 package, but the Intel computer does not have C-ISAM or ESQL/C on it. All it has is Informix 3.3. Wasn't there a way you could call C functions from Perform? And could you then drop all your unused .frm files and access the .dat/.idx files using your C programs?? Just curious. Nevertheless, the problem I may be facing is one of access speed. Here's the schema: There are four tables: CARD IR SDS CL record size of each table (in bytes): 983 851 145 145 current number of records: 16,979 62,975 17,785 69,930 Total approximate megabytes: 16 MB 53 MB 2 MB 10 MB The CARD table is the master to the other three. The users insist on having rapid query response time, about 45 secs or less with all the detail records ready to view. The Intel has 8 MB of RAM and never has more than 7 users. The Unisys has 16 MB of RAM and will have approx 8 to 12 users at any given time. I've run some test queries on some existing databases on the Unisys. One 33 MB table displayed a record in 3 secs when querying on a field with an index and in 1 min, 10 sec on a field without an index. This was when there were 5 users on the system. I've been reading Chapter 4 of "Informix Guide to SQL: Tutorial" and can't help but be concerned by the statement that queries are faster on small tables. Well, of course they are. And using indexes speed things up as we all know. At first, the database contained just the four tables above, but after a while, the programmer divided them into 10 sets of 4 identical tables stored in 10 subdirectories. This was because they were growing in size and would exceed the maximum file size limit of Xenix. When a query was done, the C program searched all the CARD tables in the 10 directories for matching master records and then searched the 10 directories for all the matching detail records. My users say they have never had a problem with slow queries. They want to have the same response speed on the Unisys machine. Gee, wouldn't it be faster to search just 4 tables, even though they will be comparatively extremely large, than to search through 40 tables in 10 directories??? Also I will be using one VIEW since there is one user of the database that will always query for the same subset of master records. Will a VIEW be slower than a SELECT with the appropriate WHERE condition? We have the OnLine engine, and I'll install it and use it if it will provide much more rapid response. There won't be more than 7 users of this database, though, so shouldn't SE be just as fast in this scenario? Thanks in advance, John ---------- John Baker jbaker@bluegrass-emh1.army.mil Division of Information Mgt. Blue Grass Army Depot, Richmond, KY 40475-5109 Ph: (606) 625-6704 DSN 745-