How Does Jet Engine Retrieve Fields From Table

[dominicdunmow]

Hi there,

I've put together a database and to keep things 'looking' neat and tidy I added all static data to one table, so there are about 10 fields in the table all completely independent of one another.

Will this method hamper the performance of the database when the form comboboxes try to retrieve only one field?

E.g. I have a combobox on a form for Customer name. The comboxo gets the list og custoemr names via a query from the table. Does the Jet Engine retrieve all the fields in the table and then single out and return only the Customer field?

And hence would it be more efficient if I hade a separate table for the custoemr name?

Thanks

 

[r937]

http://www.tek-tips.com/viewthread.cfm?qid=1017093

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts March 6 2005)

 

[BNPMike]

The database is divided into pages. I've forgotten how big they are at the moment for Jet but for the sake of argument say they are 8KByte. A page will contain as many complete records from one table as can be fitted on it. You always access at the page level. Thus the amount of i/o will normally increase as you add more fields to a table.

As a general design rule, if you get into a situation where you are going to have large numbers of entries in a form control then attack it a different way eg enter the first letter or two letters and let that trigger a parameterized query to fill the combo-box.

 

[willir]

The record set of the data you have indicated in this post and the other post seems to be relatively small -- if so, then performance will not be impacted.

If you have a larger dataset, then retrieving a single field should result in a smaller dataset.

However, it may be to your advantage to retrieve the data as read-only so Access avoids loading the overhead for updating records. They are static - right?

Will you run into problems by retrieving recordsets by different columns -- one combo box retrieves the recordset for one column; another combo box retrieves another recordset for another column. ...Interesting. ...Interesting results perhaps?

Richard