Back End Access DB & Jet Engine

[dima69]

Hi guys,

Thanks to a lot of luck and help from you great people, I am on the verge of completing my first Database/Application.

but at the moment the Jet Engine back end is not able to handle the load

here are some specs re my database:
Back End: Access -> Jet Engine
Front Ends: Access -> Jet Engine
usually not more than 2 users accessing the database and the back end is stored on one of the users computers which is quite a new computer.

Here are some structural mistakes I believe that could be causing the problem:

1. Whenever I add a new record, I use ADO to connect to an entire table as a recordset, I would think that would be putting unnecessary load on the Jet back end, what is the most streamlined way to add a new record to a recordset.

2. I have external query objects (in the front ends), will external query objects put more load on my front end then SQL strings in VBA would?

Thank You Very Much

I am not sure what the problem is, however I think you do take one thing wrong.
Access database Back End never uses Jet Engine or anything else - it's just a file server. All data is processed at the Front End. So when you have a big table and you run a query to select a few records, the whole table must be transfered to the Front End anyway.

[NeoPa]

1. Opening a table will generally not load all the data. This is a non-issue. Let Access handle it.
2. If you mean QueryDefs in your front end, then it's actually better to use them than a VBA SQL string. The former are 'optimised' on first use. A SQL string needs to have optimisation guesses done every time it's run. The Jet Engine will work quite happily on the Back End database.

PS. I don't like to contradict other Experts (so apologies for that Dima) but I think I'm right on this occasion. I would be happy to be proved wrong if there's something I need to learn. I may ask some other Experts to have a look in to check.

[missinglinq]

"Let Access handle it."

I have to say that I think this is one of the best pieces of advise that has ever been offered on this or any other Access forum! After all these years, it still amazes me how often people will insist on re-inventing the wheel! Granted, Access isn't perfect; there are some things that can be done better in Access by "rolling your own," but overall, if Access offers it, it'll be more economical than writing your own hack! How many times have we all read a post asking "How can I retrieve a certain record using the ID\Client Name\Company Name?" when a combo box option exists for this very purpose! Even more amazing, to me, is that someone will respond telling the OP to use a combobox, and then proceed to give half a page of code to accomplish the task, when the Combobox Wizard will accomplish the same thing in about 60 seconds!

To everyone on this side of The Pond, have a great Memorial Day! And a special "Thank you!" to all the veterans out there, no matter which side of The Pond you're on, even those "Down Under!"

[Killer42]

I'm inclined to agree partly with both dima69 and NeoPa.

If both front- and back-end are Access, then it will be the "front-end PC" doing all of the processing, while the back-end is simply a shared file, regardless of where it lives. That's why we go to SQL Server for larger projects, as that unloads a lot of work to the server (back-end) and greatly reduces network traffic.

However, my (limited) experience would tend to support NeoPa in his assertion that opening a dataset does not necessarily read all of the records, and will thus not place a large load on the front-end, back-end or network.

That is, unless you are dealing with UNION queries, which appear to completely change the rules.

In this sort of case, I would suggest "if in doubt, try it out". Find or create a very large table (millions of records) and open it the intended way from the front-end. You will soon be able to tell whether you are reading the entire dataset or not.

[dima69]

I'm inclined to agree partly with both dima69 and NeoPa.

If both front- and back-end are Access, then it will be the "front-end PC" doing all of the processing, while the back-end is simply a shared file, regardless of where it lives. That's why we go to SQL Server for larger projects, as that unloads a lot of work to the server (back-end) and greatly reduces network traffic.

However, my (limited) experience would tend to support NeoPa in his assertion that opening a dataset does not necessarily read all of the records, and will thus not place a large load on the front-end, back-end or network.

That is, unless you are dealing with UNION queries, which appear to completely change the rules.

In this sort of case, I would suggest "if in doubt, try it out". Find or create a very large table (millions of records) and open it the intended way from the front-end. You will soon be able to tell whether you are reading the entire dataset or not.

I've done some testing with large table (1M records, on the LAN) comparing 4 cases:
1. Opening Dynaset-type DAO recordset on the whole table and populating the records (movelast)
2. The same with Snapshot-type recordset
3. Running SELECT query returning one record, based on simple criteria on indexed field.
4. The same with criteria on non-indexed field

The results are:
1. Opening Snapshot-type recordset (as if copying all data to local computer) is the same time as running a query, whith a criteria on non-indexed field.
I guess this test proves my previous point that the whole table must be brought from BE to run a query :)
2. Running the query with a criteria on indexed field is very fast.
3. Opening Dynaset-type recordset is much faster than opening Snapshot recordset (I guess it's because the data is not transfered as in case of snapshot)

[NeoPa]

I've done some testing with large table (1M records, on the LAN) comparing 4 cases:
1. Opening Dynaset-type DAO recordset on the whole table and populating the records (movelast)
2. The same with Snapshot-type recordset
3. Running SELECT query returning one record, based on simple criteria on indexed field.
4. The same with criteria on non-indexed field

The results are:
1. Opening Snapshot-type recordset (as if copying all data to local computer) is the same time as running a query, whith a criteria on non-indexed field.
I guess this test proves my previous point that the whole table must be brought from BE to run a query :)
2. Running the query with a criteria on indexed field is very fast.
3. Opening Dynaset-type recordset is much faster than opening Snapshot recordset (I guess it's because the data is not transfered as in case of snapshot)

1. Surely this proves only that you've used MoveLast? This would behave the same way whether a FE/BE were used or a simple (and single) database.
2. This proves? that the Jet Engine is able to work quite happily with the BE data. If this were not so, then surely this could only produce the results after the whole table is loaded?
3. Further indication, if it were needed, that the BE database is processed by Jet rather than simply as a repository of unindexed data.

From your points, I'm starting to wonder if we're not talking at some level of cross-purposes?

[NeoPa]

I'm inclined to agree partly with both dima69 and NeoPa.

If both front- and back-end are Access, then it will be the "front-end PC" doing all of the processing, while the back-end is simply a shared file, regardless of where it lives. That's why we go to SQL Server for larger projects, as that unloads a lot of work to the server (back-end) and greatly reduces network traffic.

However, my (limited) experience would tend to support NeoPa in his assertion that opening a dataset does not necessarily read all of the records, and will thus not place a large load on the front-end, back-end or network.

That is, unless you are dealing with UNION queries, which appear to completely change the rules.

In this sort of case, I would suggest "if in doubt, try it out". Find or create a very large table (millions of records) and open it the intended way from the front-end. You will soon be able to tell whether you are reading the entire dataset or not.

Your points, while valid, don't seem to reflect the FE/BE situation any more than they do a simple database. A single database reflects the same issues with UNION queries as you've discovered in your BE database, doesn't it?

[NeoPa]

The original question (Novice Mistakes that could be crashing Access) is still ongoing, but further discussions on this topic (Back End Access DB & Jet Engine) should remain in here please.

[ADezii]

I am not sure what the problem is, however I think you do take one thing wrong.
Access database Back End never uses Jet Engine or anything else - it's just a file server. All data is processed at the Front End. So when you have a big table and you run a query to select a few records, the whole table must be transfered to the Front End anyway.

--------------------------------------------------------------------------
Mastering Microsoft Office Access 2003 by
Alison Balter
Copyrighted by SAMS Publishing
Page 10
--------------------------------------------------------------------------

If you develope an application that stores the data on a File Server on an Access Database, the workstation performs all data processing. This means that everytime the user runs a Query or Report, the File Server returns all the data to the workstation. The workstation than runs the Query and displays the results in a datasheet or on a report. This process generates a significant amount of network traffic, particularly if multiple users are running reports and queries at the same time on large Access tables. In fact, such operations can bring the entire network to a crawl.

A Client/Server database, such as SQL Server or Oracle, processes queries on the Server machins and returns results to the workstation. The Server software can't display data to the user, so this is where Access comes to the rescue...

[NeoPa]

--------------------------------------------------------------------------
Mastering Microsoft Office Access 2003 by
Alison Balter
Copyrighted by SAMS Publishing
Page 10
--------------------------------------------------------------------------

If you develope an application that stores the data on a File Server on an Access Database, the workstation performs all data processing. This means that everytime the user runs a Query or Report, the File Server returns all the data to the workstation. The workstation than runs the Query and displays the results in a datasheet or on a report. This process generates a significant amount of network traffic, particularly if multiple users are running reports and queries at the same time on large Access tables. In fact, such operations can bring the entire network to a crawl.

A Client/Server database, such as SQL Server or Oracle, processes queries on the Server machins and returns results to the workstation. The Server software can't display data to the user, so this is where Access comes to the rescue...

I believe this quote is referring to the advisability (or otherwise) of storing the database remotely (across a network) rather than the use of a FE/BE model. FE/BE databases can both be stored on an individual PC, they can both be stored on a server (across a network) and they can be stored as FE on local PC with BE on a server. Any databases, whether FE or BE, which are stored remotely, across a network, will suffer from having to transfer the data (whatever data is requested) across the much slower medium of the network. This is NOT a FE/BE issue, but a general network one.
Where the book refers to :

the File Server returns all the data to the workstation.

the all data refers to everything requested by the PC. In this case, the PC is running the Jet Engine, so any requests to a BE database, are intelligently controlled by this, so that it only requests the same data as it would on a local PC. It will NOT need to request the data from the whole table (necessarily), any more than it would need to if it were processing data stored on the local hard drive.

[ADezii]

I believe this quote is referring to the advisability (or otherwise) of storing the database remotely (across a network) rather than the use of a FE/BE model. FE/BE databases can both be stored on an individual PC, they can both be stored on a server (across a network) and they can be stored as FE on local PC with BE on a server. Any databases, whether FE or BE, which are stored remotely, across a network, will suffer from having to transfer the data (whatever data is requested) across the much slower medium of the network. This is NOT a FE/BE issue, but a general network one.
Where the book refers to :
the all data refers to everything requested by the PC. In this case, the PC is running the Jet Engine, so any requests to a BE database, are intelligently controlled by this, so that it only requests the same data as it would on a local PC. It will NOT need to request the data from the whole table (necessarily), any more than it would need to if it were processing data stored on the local hard drive.

1. From the Original Post - wouldn't his imply a FE on Local PC/BE on Server scenario?
   

   the back end is stored on one of the users computers which is quite a new computer.

2. Given this scenario, it is not a matter of Requests but where the processing is performed. Since the processing would be performed on the Client (FE) in this case, it would have no choice but to download the entire Table contents, process it, and return a result set. If a single Record was returned from 100,000, all 100,000 Records would still have to be sent to the FE for processing.
3. You gotta love this discussion stuff.

[dima69]

1. Surely this proves only that you've used MoveLast? This would behave the same way whether a FE/BE were used or a simple (and single) database.
2. This proves? that the Jet Engine is able to work quite happily with the BE data. If this were not so, then surely this could only produce the results after the whole table is loaded?
3. Further indication, if it were needed, that the BE database is processed by Jet rather than simply as a repository of unindexed data.

From your points, I'm starting to wonder if we're not talking at some level of cross-purposes?

The point here was to show that even if you restrict the returned data using query it takes the same time as returning the whole table, since both cases require bringing the whole table to the FE. Of cause, not all operations require all data to be processed instantly (like in case of dynaset). So I tend to agree with ADezii on this one.

[NeoPa]

1. From the Original Post - wouldn't his imply a FE on Local PC/BE on Server scenario?
   

   the back end is stored on one of the users computers which is quite a new computer.

2. Given this scenario, it is not a matter of Requests but where the processing is performed. Since the processing would be performed on the Client (FE) in this case, it would have no choice but to download the entire Table contents, process it, and return a result set. If a single Record was returned from 100,000, all 100,000 Records would still have to be sent to the FE for processing.
3. You gotta love this discussion stuff.

1. No. It simply states that the BE is on a single PC, which must be across the network for at least some of the PCs using it. Nothing I can see puts the FE on a local PC.
2. That is simply not true. The Jet engine is running on the PC so will make requests of the BE just as it would of the FE, wherever they are stored. If every query loaded all the records then we may as well not use an RDBMS at all. Please refer to dima's post #5 for examples of BE queries finishing well before they could if all data were transferred in that un-RDBMS way.
3. :) I certainly do love a good argu... discussion :D

[NeoPa]

The point here was to show that even if you restrict the returned data using query it takes the same time as returning the whole table, since both cases require bringing the whole table to the FE. Of cause, not all operations require all data to be processed instantly (like in case of dynaset). So I tend to agree with ADezii on this one.

But it doesn't.
You're other points showed that quite clearly and unambiguously.
If you say explicitly in your code "Load all the data from the table." (MoveLast), then how can you use that to support the idea that the BE forces all the data to be loaded. Clearly it's not the BE at all but your code (MoveLast) which causes this.

[dima69]

But it doesn't.
You're other points showed that quite clearly and unambiguously.
If you say explicitly in your code "Load all the data from the table." (MoveLast), then how can you use that to support the idea that the BE forces all the data to be loaded. Clearly it's not the BE at all but your code (MoveLast) which causes this.

It was not the MoveLast I was checking - I just used it as a reference to evaluate the query performance, which showed to be the same.

[NeoPa]

It was not the MoveLast I was checking - I just used it as a reference to evaluate the query performance, which showed to be the same.

Nevertheless, with the MoveNext in there at all, you absolutely cannot use that as evidence to show that a BE database will load all records. As ANY database would under those circumstances.

[dima69]

Nevertheless, with the MoveNext in there at all, you absolutely cannot use that as evidence to show that a BE database will load all records. As ANY database would under those circumstances.

I guess I'me not explaining myself well. I agree that MoveLast loads all records in any case - that's why I use it to measure the time it takes. I other test case (which has nothing to do with MoveLast) I run a query which returns one record. This query just takes the same time as MoveLast case. That's why I conclude here that the query loads all records as well.

[MMcCarthy]

I don't think the issue here is one of backend/frontend databases exactly. The issue is rather one of Recordsets and how the Jet Engine handles them.

There are five recordset types in DAO.

Table
Dynaset
Snapshot
ForwardOnly
Dynamic - odbc only, doesn't use the Jet Engine

Table type (dbOpenTable)
This has the advantage that you can use the table indexes. You cannot use indexes against other recordset objects.

Dynaset type (dbOpenDynaset)
When dynaset is created Access copies the key fields only to the recordset. These are the fields that uniquely identify the recordset. Then whenever you want to view/access a particular record Access fetches the latest version of that record. The advantage is that large recordsets will load faster but processing can be slow as Access has to refer to source each time it wants a record.

Snapshot (dbOpenSnapshot)
Access retrieves a copy of all records for all fields to the recordset. The advantage of this is that the full recordset is held in memory so that any procedures will process faster as Access doesn't have to reference the source.

ForwardOnly (dbForwardOnly)
Similar to snapshot except its read only and can only move in one direction which is forward.

Defaults

If no recordset type is defined then the following defaults are used by Access.

If the recordset is a table in the current database then Access defaults to Table type recordset.

If recordset is a query, sql statement or a non-access table then access defaults to Dynaset type recordset.

All other recordsets default to snapshot.

I'm not sure how access treats linked Access tables in this but specifying dynaset or snapshot is probably a good idea depending on requirements.

NOTES:

For Dynaset and Snapshot Access doesn't fully populate the recordset until navigation to the end of the table or query. Hence, dima69 using MoveLast.

Using saved queries will give slightly better preformance as the query is already compiled.

[NeoPa]

I guess I'me not explaining myself well. I agree that MoveLast loads all records in any case - that's why I use it to measure the time it takes. I other test case (which has nothing to do with MoveLast) I run a query which returns one record. This query just takes the same time as MoveLast case. That's why I conclude here that the query loads all records as well.

I was misreading you I must admit. To be fair, that was more my misunderstanding than your explanation. Having checked back though, your test is still invalid.
From your earlier post :

1. Opening Snapshot-type recordset (as if copying all data to local computer) is the same time as running a query, with a criteria on non-indexed field.
I guess this test proves my previous point that the whole table must be brought from BE to run a query :)

Would this not also be a special case?
A query which processes via a non-indexed field is also going to load the whole table. The whole point (well main point at least) of the Jet Engine is to process through the tables using indeces where possible. The only valid tests would be your later tests, which use an index. These clearly show (from your stated results), that a faster response is given than could be expected if the Jet Engine were not used.

[MMcCarthy]

This is taken directly from Wrox's "Beginning Access 2002 VBA".

Chapter 10 - Optimising your Application
subheading: Search on Indexed Fields in Attached Tables p.831/832

SELECT *
FROM tblPerformance
WHERE ID=4000

If the ID field in tblPerformance is indexed then the Jet engine knows that it will only need to retrieve pages containing records with an ID of 4000 from the tblPerformance table.

However, if the ID field is not indexed, then Jet will have to read the whole of the tblPerformance table from the disk and transfer it into memory of the PC running the query. If the tblPerformance table is on a network server, this means that the whole table will need to be transferred accross the network. Once it is in local memory, Jet can determine which records match the criterion by going though each record in turn and checking whether the ID field is equal to 4000. This is known as a full table scan and is slow.

[Killer42]

Subscribing.

(This might be one for the wishlist - my subscription is still to the old thread.)

[MMcCarthy]

Subscribing.

(This might be one for the wishlist - my subscription is still to the old thread.)

Subscribing without replying will be introduced soon.