Un-Used Fields

[ions]

Dear Ms Access Expert.

1. If there is no data in a text field ( assume text length of 50 characters) does this take up physical space on the harddrive?

2. Scenario Details:

Suppose I have a query: Select fieldA from tableX where A > 100.

Suppose FieldA is indexed and a long datatype.

Suppose tableX is on a server and the data must be transmitted over the network to the workstation?

Suppose TableX has 3 other text fields (B,C,D) with char length of 50.

Questions:

Will JET send data from fields B, C, D over the wire eventhough they are not in the query definition.

If the answer to the above is YES, what if B, C, D hold no data for the particular record(s)?


Thanks

 

[boblarson]

Access doesn't pad fields that are empty. So, if you define a field as 255 characters and only use 30, it doesn't pad it to 255.

 

[ions]

Thanks.

Bob if you have time can you take a look at question 2?

Thanks.

 

[boblarson]

If you are selecting only field A, only field A is returned. Access will formulate the query and send an optimized query depending on what you are doing. It will only return and process on the workstation what it can't do initially. So, if you have a user defined function, for example, it would have to transfer as much as needed in order to process that function. If you do a table scan, then it would need to return the entire table to do it. But if you are only processing on a small subset of records, it will attempt to return only those records and then process the function. The Jet Engine is pretty good in that way. The problem comes when you try to select more than you need.

Using Select * FROM TableName is not a good thing to do unless you absolutely need the entire table.

 

[ions]

Excellent.

Thank you Bob

 

[Banana]

Few additional nitpicky points...

By default, all text data types are similar to VARCHAR, and as Bob said, that isn't padded with spaces if it's empty. However, if we were to use a SQL DDL:

CREATE TABLE tbl(
   col CHAR(10) NOT NULL DEFAULT ''
);

Then the col would always be padded with 10 spaces if there were empty values. Fortunately, CHAR data type can't be selected via UI, only via DDL query and thus it's extremely unlikely we will run into fixed space columns. In other RDBMS, it is good idea to use CHAR when we know that the width is always same (e.g. an account number that is always X digits for instance) because that helps optimizes indexing and space as it does not have to store a extra byte to state the size as VARCHAR would have to. However, on average, VARCHAR is going to be cheaper than CHAR for strings that could vary considerably, even with that extra byte(s) to convey the information about the size. However, I've yet to see any documentation that suggests that Jet similarly benefits from CHAR's optimization.

Bob also has it right regarding Jet's optimizing the query. It will ask only for what it actually needs. By default, most queries are dynaset recordset type, which means that it will only start off by asking for the primary key that matches the criteria and lazily fetch the actual rows when it's actually needed to paint the screen. Of course, it's easy to force Jet to grab more columns if we make use of functions that requires local evaluation so it must grab those columns with the primary key and performs the evaluation to determine what actually goes into the final resultset. Note further that when working with Jet backend, all processing are client-side anyway. That still does not translate into that it must fetch everything as it will start off by fetching the pages containing the index and try to guess at which pages it needs to fetch thus optimizations still does occur even if it's entirely done client-side. Linking against ODBC tables, OTOH means that Jet passes much processing as possible to the backend server but it's still subject to the same restriction: if we use a user-defined function that is intelligible and unknown to the backend server, then Jet must fetch all columns it needs to perform the evaluation locally.

Thus, the moral? Be careful how you write the query. It could produce the desired result but behind the stage, the machinery may be busting a cog or two in serving the request.

 

[ions]

Thanks Banana.

That was very informative. Where do you find information about how JET actually processes queries? I.e If there is criteria on an index field, Jet first sends Index Pages to be processed on the local machine.

Appreciate it.

 

[Banana]

Fortunately, this kind of things isn't really documented in details. This is largely conjecture based on others' observation and sharing thereof and "reading between the lines" when we read the various KB articles on Jet. So it's really a guess at best. Sorry.

However, you could look at using SHOWPLAN to study how Jet transform your query and how it can be improved.

 

[TexanInParis]

If you are selecting only field A, only field A is returned. Access will formulate the query and send an optimized query depending on what you are doing. It will only return and process on the workstation what it can't do initially. So, if you have a user defined function, for example, it would have to transfer as much as needed in order to process that function. If you do a table scan, then it would need to return the entire table to do it. But if you are only processing on a small subset of records, it will attempt to return only those records and then process the function. The Jet Engine is pretty good in that way. The problem comes when you try to select more than you need.

Using Select * FROM TableName is not a good thing to do unless you absolutely need the entire table.

If we're talking about an Access database back end (which I assume is the case since you mention the JET engine), I have to disagree. First, JET runs on the workstation and acts like a smart flat file manager. It *must* fetch entire pages from the database. However, if you ask for only a few columns from a table, Access then puts just those columns in the recordset or form buffer. There is some small memory advantage to asking for only the columns you need, but unless the table rows are very wide, it'll be negligible. There are two advantages to asking for only the columns you need:

1) You don't get extra stuff if columns are added to the table after you've created the query.

2) The query engine doesn't have to fetch the table definition to find out what columns constitute * at runtime.

John Viescas, author
Microsoft Office Access 2007 Inside Out
Building Microsoft Access Applications
Microsoft Office Access 2003 Inside Out
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)​