Re: Access 7.0 -> Informix 7.1-2

"Irwin Goldstein" <irwin@objectsoft.com> wrote: :Nils Myklebust <Nils.Myklebust@idg.no> wrote in article :<32fafd05.9241017@gate.idg.no>... :> <snip> :> Testing a selection on an index returning about 31.000 rows from a :> table of about 1.000.000 rows, dbaccess used about 33 seconds to :> select them all and write them to a file. With MS Access I saw the :> last row of the query after about 16 seconds. (MS Access have then :> read all the 31.000 rows from the database and stored them locally on :> my PC.) :> :> The interesting part here is that I get the same or better performance :> using MS Access via ODBC as I see using dbaccess on the server on :> similar operations. :> :> I repeat again that these are *very* informal tests : :Actually, if your table has a unique index, then MS-ACCESS has fetched all :of the columns of the (first) unique index locally, and then fetched the :rest of the columns for just the rows that you actually see on the last :"page" of results. If the index is relatively small (say a single serial :column?) and the rows relatively large, this could easily explain the :difference. : :Try the same test on a table with no unique index defined. This will cause :MS-ACCESS to treat the table as a 'snapshot' and return all columns. I :think you'll find MS-ACCESS much slower (mainly due to the network :bandwidth) in this case. You are right of course. I didn't think of that at the time. (I did say the tests where *very* informal.) I tested by creating a new table with no indexes at all and inserting the 1632 rows from the previously used table. MS Access then used 10 seconds to show the last row, while when there was a primary key (2 x char(6) columns in a composite key) the same operation takes about 2 seconds. I don't have space or time to test with the larger table for now. One can also see the result in that going back and forth in the table is substantially faster when there is no index than it is on the indexed table. In the last case, as you say, MS Access is actually fetching the rows from the server database as they are needed for display, based on the primary key it has read. It remains thoug, that ODBC access to the database is reasonably fast. The fact that MS Access is smart about how it reads the data is nice. MS Access is a very good tool to look at your data while developing and maintaining that data without having to program. Informix have a lot to learn from it when they make GUI tools. In their SQL editor that comes with the Windows NT OnLine databases they have a *major* flaw in that all data is fetched from the database before anything is shown to you. This makes to tool very dangerous to use (tink of a small error in your select that results in perhaps milions of rows being selected), and much slower than MS Access. Happily MS Access is cheap and I truly can't live without it. Just make sure you don't try to use it to create large applications. Then you may easily hit a wall that isn't easy to climb over if at all possible. Nils.Myklebust@idg.no NM Data AS, P.O.Box 9090 Gronland, N-0133 Oslo, Norway My opinions are those of my company The Informix FAQ is at http://www.iiug.org