Question About How Access Loads Data Into Forms (1 Viewer)

[shadow9449]

I've seen the suggestion a number of times that forms be designed to only load the one record from the table that the user wants to deal with at the moment. This is accomplished by binding the form to a query that returns the one record the user has selected (from a combo or list box, for example). The rationale behind this design as opposed to loading the entire table when the form loads is that it results in less data being loaded into memory and being drawn across the network, thus improving performance.

My question is that I've seen a number of times that Access needs to load an entire table into the client's PC i(and I would assume into RAM as well) in order to retrieve a single record. If this is true, then using one record for the datasource of a form won't have any improvement over using the entire table.

Can someone shed light on how Access actually loads the data into the record to explain why this design is more efficient?

Thank you

SHADOW

 

[namliam]

My question is that I've seen a number of times that Access needs to load an entire table into the client's PC i(and I would assume into RAM as well) in order to retrieve a single record. If this is true, then using one record for the datasource of a form won't have any improvement over using the entire table.

I am by no means an expert on this, but ... I think this is true for a strict Server/FE enviroment where you have a "proper" database on the background, SQL Server, MySQL, Oracle, Etc. that has their own processing/processor

When you have a Access (like) backend there is no backend processing there is just the client and no gain is made ...

Again... I think... accoording to my knowledge.... which is kindoff limited on this subject.

 

[DCrake]

There is a simple analogy between Access queries and SQL Stored procedures

If you were to ask Access "how many blue cars do you have in your showroom?" The would drive every vehicle to your house then count them for you.

However, if you ask SQL they would count them there and then tell you the answer.

So it depends how you construct your question.

David

 

[shadow9449]

So both of you are confirming that there is no benefit to the one-row design if you are using Access with JET as the back end?

SHADOW

 

[DCrake]

Whether you use single forms or continuious forms the iss ue is still the same.

Single record form are more elegant and user friendly. It all depends on what your want to display on the form.

David

 

[shadow9449]

Whether you use single forms or continuious forms the iss ue is still the same.

Single record form are more elegant and user friendly. It all depends on what your want to display on the form.

David

I think there's some confusion as to what I meant by 'single record'. I didn't mean a form that only shows a single record. I meant a form whose recordsource consists of just one record based on a query rather than a form whose recordsource is an entire table.

The point of this thread is to question whether the former method entails more work for either the client PC or the network than the latter method.

Thank you

SHADOW

 

[boblarson]

I've seen certain people say it both ways.

1. One says that JET will and can pull information from a backend file just fine without pulling data across.

2. Another says it can't.

Perhaps I need to go ask the question of the Access design team and let them answer.

 

[shadow9449]

I've seen certain people say it both ways.

1. One says that JET will and can pull information from a backend file just fine without pulling data across.

2. Another says it can't.

I think that's why I'm confused as well! It depends on which article I read and that really will affect how I design my forms.

Perhaps I need to go ask the question of the Access design team and let them answer.

That would be awesome! I think that everyone who reads the forum would benefit from authoritative information as to whether there are benefits to designing forms with the 'single record query' method. As I said, I think that many people would design forms differently if they realized that there's a benefit (IF there's a benefit!)

Thanks Bob!

SHADOW

 

[Banana]

Well, DCrake's analogy is right in the sense that all processing are done locally but it does not mean it has to process the whole table & rows and there are indeed benefits to filtering the data being returned, regardless of what backend we're using.

Suggest that we have a look at this thread which gets into the gritty-nitty detail about how Jet works with pages and makes "guesses" at how it needs to process the data

To put it in simplest & concise terms possible:

All processing is done on the client's machine, but Jet has resources available to extract only needed data from pages using the index pages, statistics pages and metadata pages.

Like I discussed in ODBC guide, I'm reasonably certain it's possible to coerce Jet to perform table scans (bad) by not indexing the column used in criteria, for instance and may go by unnoticed because the dataset is too small/concurrency is low to make any difference anyway but would be a severe & crippling flaw in a busy database server.

 

[shadow9449]

Well, DCrake's analogy is right in the sense that all processing are done locally but it does not mean it has to process the whole table & rows and there are indeed benefits to filtering the data being returned, regardless of what backend we're using...

To put it in simplest & concise terms possible:

All processing is done on the client's machine, but Jet has resources available to extract only needed data from pages using the index pages, statistics pages and metadata pages.

So, to paraphrase what you're saying, even though JET does need the entire table (and the network takes a hit accordingly), there are still benefits to limiting forms to one record in how memory management is done and therefore it's advisable?

Like I discussed in ODBC guide, I'm reasonably certain it's possible to coerce Jet to perform table scans (bad) by not indexing the column used in criteria, for instance and may go by unnoticed because the dataset is too small/concurrency is low to make any difference anyway but would be a severe & crippling flaw in a busy database server.

Presumably, the way the row is being extracted is via its primary key in the WHERE condition of the query which will be indexed in any table.

SHADOW

 

[Banana]

So, to paraphrase what you're saying, even though JET does need the entire table (and the network takes a hit accordingly), there are still benefits to limiting forms to one record in how memory management is done and therefore it's advisable?

No, I'm not saying that. It doesn't have to move/scan the whole table. It starts off with getting the metadata and statistics pages which provides some general information about the schema. When it's then handed a query, it then examines the metadata pages to figure which index pages it needs to read, fetch those index pages and then examine the index pages to finally get the data pages that contains the actual records.

So, processing is all done on the client machine, but that does not translate into requiring that all data be copied over the network into the client.

Presumably, the way the row is being extracted is via its primary key in the WHERE condition of the query which will be indexed in any table.

Yes, but not just the WHERE; columns that's used in a JOIN, ORDER BY, GROUP BY also may benefit from indexing. It also depends on whether the query itself is well-written. For example, if we write a query that does function evaluation on the same side of the equation (e.g. WHERE Year([MyDate]) <> 2009 ), Jet may have no choice but to scan the whole table to evaluate the expression. OTOH, using WHERE MyDate BETWEEN #2009-01-01# AND #2009-12-31#, enables Jet to go directly to the index pages and select only data pages it needs to satisfy the request.

 

[shadow9449]

...It doesn't have to move/scan the whole table. It starts off with getting the metadata and statistics pages which provides some general information about the schema. When it's then handed a query, it then examines the metadata pages to figure which index pages it needs to read, fetch those index pages and then examine the index pages to finally get the data pages that contains the actual records.

So, processing is all done on the client machine, but that does not translate into requiring that all data be copied over the network into the client. .

Interesting. Then having a form bound to a single row of data rather than a whole table can improve network efficiency as well.

...
Yes, but not just the WHERE; columns that's used in a JOIN, ORDER BY, GROUP BY also may benefit from indexing. It also depends on whether the query itself is well-written. For example, if we write a query that does function evaluation on the same side of the equation (e.g. WHERE Year([MyDate]) <> 2009 ), Jet may have no choice but to scan the whole table to evaluate the expression. OTOH, using WHERE MyDate BETWEEN #2009-01-01# AND #2009-12-31#, enables Jet to go directly to the index pages and select only data pages it needs to satisfy the request.

This can be helpful when writing queries but wouldn't apply to my specific question about designing forms.

Thanks again

SHADOW

 

[Banana]

Well, I've had followed Pat Hartman's excellent advice: Always use queries for your recordsource/rowsource. Ideally, every form's recordsource should have some kind of criteria, even if it's a hard TOP X number of records to ensure that forms will be responsive because this enables Jet to read less amount of metadata/index to serve the request and when/if the application gets upsized, it's already a good citizen. Doing it right from the start makes it very robust application.

 

[namliam]

To put it in simplest & concise terms possible:

All processing is done on the client's machine, but Jet has resources available to extract only needed data from pages using the index pages, statistics pages and metadata pages.

It is reasonable to assume that access does use the indexes, but indexes so many times fail like with your "Year([MyDate]) <> 2009" sample...
In oracle i am sure you can build an index seperatly on Year([MyDate]), but access I dont think so...

Also I have noticed that when running a query with many records access returns the "visible" part of the query much faster than it fetches the remainder, so the optimization is set to return top 100 or something faster than the rest....

 

[Banana]

Actually, it's not top 100 but rather "lazy fetching" at work. In the Jet-ODBC whitepaper, it's explained in details quite nicely. Basically if you have a query:

SELECT * FROM aTable;

What Jet actually does behind the scene is first send a statement:

SELECT primaryKey FROM aTable;

and once it has all the primaryKey, it then send sequential SQL statements:

SELECT * FROM aTable WHERE primaryKey = 1 OR primaryKey = 2 OR primaryKey = 3 OR primaryKey = 4 OR primaryKey = 5 OR primaryKey = 6 OR primaryKey = 7 OR primaryKey = 8 OR primaryKey = 9 OR primaryKey = 10;

It keeps doing so until the screen is completely painted. Say that for 10 records, it's good enough. Wait a longer and it sends another statement in background:

SELECT * FROM aTable WHERE primaryKey = 11 OR primaryKey = 12 OR primaryKey = 13 OR primaryKey = 14 OR primaryKey = 15 OR primaryKey = 16 OR primaryKey = 17 OR primaryKey = 18 OR primaryKey = 19 OR primaryKey = 20;

It'll keep doing this until the recordset has filled (in theory), and periodically refresh the records while idle activity. Of course if you click the Go To Last button, it immediately interrupt its own lazy fetching and sends the statement with the last few primary keys.

A observation to note: When you open a linked table, you don't know the recordcount until you move to the last record. There's a reason for that: Jet is fetching and filling the rows just in time so it doesn't know how many records there are.

You can easily verify all of that by using a linked table and using the tracing utility of whatever backend you used (SQL Server's Tracer, MySQL's General Log... I'm sure Oracle has something like that).

But we're talking about Jet as a backend, not with a ODBC backend. I assert that the behavior is mostly same (seems to me easier to code the client to behave same for all backend anyway!) so I wouldn't be surprised to see that Jet does the same process with its own data files.


As for the indexing weirdness like Year([MyDate]) <> 2009, I would charge that it is developer's responsibility to understand how it works and adjust the query accordingly. Microsoft tried their best to make Jet "forgiving" in that it will attempt to process whatever it may have been handed, even if it's not the best thing for everyone all around. That's one reason why some people make the mistake of blaming Access when they should be blaming the developers for writing bad queries.

 

[shadow9449]

Well, I've had followed Pat Hartman's excellent advice: Always use queries for your recordsource/rowsource. Ideally, every form's recordsource should have some kind of criteria, even if it's a hard TOP X number of records to ensure that forms will be responsive because this enables Jet to read less amount of metadata/index to serve the request and when/if the application gets upsized, it's already a good citizen. Doing it right from the start makes it very robust application.

Ok, I have to admit that my initial question in this thread was based (at least in part) on Pat's advice to always use queries. I definitely understand that the benefit comes into place where the database is upsized, but my question was whether there are benefits even if someone keeps JET as the backend.

I have some databases that have been running successfully for years with a good number of users (between 40 and 50) just using a split Access database keeping the linked tables in JET. I don't have any pressing need to upsize them but I do want to make them run efficiently as possible.

Just to repeat, the advice provided so far is that there will be an improvement to the efficiency if I limit the forms to one record. As it is now, the client form loads about 16,000 clients when it opens and then the user navigates to the requested client rather than opening to a form with zero records and only loading the record that is required.

SHADOW

 

[Banana]

Ok, I have to admit that my initial question in this thread was based (at least in part) on Pat's advice to always use queries. I definitely understand that the benefit comes into place where the database is upsized, but my question was whether there are benefits even if someone keeps JET as the backend.

Right. The trouble is that there's no explicit documentation detailing the internal working of Jet. The information I've provided so far is based on hearsay and reading between the lines on various KB articles about optimizing Jet. The ODBC whitepaper is probably most comprehensive information on how Jet works under the hood, though how much of this apply to Jet as the backend is debatable (IMHO, many of those applies- it seems nonsensical to me that they would code the optimizer differently for Jet backend and ODBC backend; the difference would be mainly restricted to *how* Jet fetches data, but not how Jet optimizes the queries).

Just to repeat, the advice provided so far is that there will be an improvement to the efficiency if I limit the forms to one record. As it is now, the client form loads about 16,000 clients when it opens and then the user navigates to the requested client rather than opening to a form with zero records and only loading the record that is required.

Right. Again, using the ODBC whitepaper as a guess at how Jet works with Jet backend, it tells us that it'll fetch all 16,000 primary keys before you can proceed, though you need not wait until it has fetched all 16,000 records. Using a query should then enable us to avoid the trouble of fetching 16,000 primary keys that would get tossed out anyway and restrict the set to what is useful.

For example, my old database initially only fetches active clients within last 90 days with a option to get "archived" clients. That way, the form loads much faster because it needs to get less primary keys, and if the user need to see more, they can hit the archive view which is just another query on the same table with looser criteria and they take the performance hit.

This also alludes to another important point: If you then put down a combobox for the user to select a client out of 16,000 possible clients, while the form initially loads zero records, this is actually no different than having the form load with 16,000 clients because, well, the combobox has to ask a query of its own!

Of course, you could have the combobox start out unbound, and only retrieve records after the user has typed a minimum number of letters (say 3 letters) and thus get only filtered result and thus reduce the amount of index & data Jet needs to bring over.

Finally, it should not be forgotten that Jet works on pages, even though it supports row-locking. Therefore, if index happen to fit on a single page, then there will be no difference whatsoever. Two pages may be miniscule. 10 pages may be more of benefit if you can have the criteria help Jet choose only one index page to satisfy the request, rather than all 10 pages.

 

[gemma-the-husky]

my understanding is that how much data is fetched by jet depends on the way the query is written. so, i thought that a user defined function has to be evaluated on the client machine - so all the data comes across. but an explict comparison eg date<somedate can be evaluated by jet, and will reduce the data coming across.

careful design can therefore achieve performance increases with jet backends.

The corollary is that upsizing to SQL doesnt necessarily achieve performance increases - because dbs redesign may be necessary to take advantage of advanced features

 

[shadow9449]

Of course, you could have the combobox start out unbound, and only retrieve records after the user has typed a minimum number of letters (say 3 letters) and thus get only filtered result and thus reduce the amount of index & data Jet needs to bring over.

My practice is that ALL combo boxes are unbound and retrieves the row source On_Enter. That makes my forms load much faster. If you have a form with 10 comboboxes that all have to load data, the amount of loading that is necessary will give the form a huge performance hit especially if the user only uses one or two of the comboboxes.

Here's an example:

The "City" field on the client form of my applications is a combobox. The data in the combobox would be something like:

SELECT DISTINCT city FROM tblClients ORDER BY city

The same goes for the province field.

If the user is pulling up the client form and it has to query 10,000 clients to populate the city field and then again for the province field, there's going to be a performance issue. This is especially true if the client form is being accessed to sell a product to a client!

The next reason I do this is to make combo boxes dynamic. Users get frustrated that they can't enter a new product into a product catalogue and immediately see it on their screen in the combo boxes.

SHADOW

 

[Banana]

my understanding is that how much data is fetched by jet depends on the way the query is written. so, i thought that a user defined function has to be evaluated on the client machine - so all the data comes across. but an explict comparison eg date<somedate can be evaluated by jet, and will reduce the data coming across.

It requires a qualification here. This:

SomeColumn = MyCoolFunction()

will not require a table scan because Jet can then evalulate the MyCoolFunction first and send in the value instead.

This *may* require a table scan:

MyCoolFunction(SomeColumn) = "Right on!"

because we must then evaluate each record's field 'SomeColumn' to see if we get the expected result.

However, Jet can short-circuit:

SomeColumn > 10 AND MyCoolFunction(SomeColumn)="Right on!"

If the SomeColumn is indexed and there's no value bigger than 10, then Jet knows already that there's no matching records and doesn't bother doing the evaluation at all. But if there's a couple of records where SomeColumn > 10, Jet only evaluates those records, again not needing a table scan here.

careful design can therefore achieve performance increases with jet backends.

Agreed.

The corollary is that upsizing to SQL doesnt necessarily achieve performance increases - because dbs redesign may be necessary to take advantage of advanced features

Well, Jet is a relational database, and upsizing to SQL Server, Oracle, MySQL, DB/2, whatever is just moving data to another relational database. Therefore, holding to the general principle about relational databases mean that any optimizations will benefit the application regardless of the actual engine in use. To be sure there may be some nuances one should be aware, but those are corner cases rather than general principles.

As Pat Hartman has remarked, her upsizing applications was very easy because she designed it for client/server architecture from the very start. Probably a minor tweak there & here but otherwise it's still same application as it was with Jet.

However, it is emphatically true that simply upsizing the database will not automatically improve the performance, and in fact may make it worse as we now introduce more layers and a daemon to talk with. Jet is actually faster than daemon-based RDBMSes when we are considering a single-user localhost scenario because of this fact. Of course, RDBMSes maintain better performance as number of concurrent users increases and thus shine in that area.