MS Access vs other tecnologies (2 Viewers)

[CJ_London]

In addition, even if the entire table has to be transferred if the form is bound to the unfiltered table, then loading the form should be much slower, but once the form is loaded and the data is transferred and cached/loaded into RAM or wherever the data resides, retrieving one row from a table that's already been transferred should be comparable or even faster than retrieving the row from the server each time the user navigates.

if your form recordsource is based on an entire table, an entire table will be loaded. However once access has received a few records, it will display and continue to load the remainder of the records in the background - so it appears faster. But try and navigate/filter etc before all records are loaded will result in delays

You can see this with a large table - just open the table and wait until the record stats are loaded at the bottom. It doesn't know how many records are in the recordset until they are all loaded.

whereas JET/ACE are simply acting as a file share, so requesting one row requires the entire table to be transferred and it's only the client computer doing the filtering to display the row we need. If that's the case, why is basing a form on a single row an advantage?

not true - if you have requested a single record in a query with appropriate criteria, then a single record will be returned. Note that the WHERE parameter of docmd.openform actually applies a filter to the form, not a criteria. I think you may be confusing applying certain dao queries (such as group by's) to linked tables but it will still only bring through the records limited by the WHERE criteria and is why you should use the WHERE criteria on grouped fields and only use the HAVING on summed/count etc fields. With SQL server of course, you should use pass through queries and make use of stored procedures so all the heavy lifting is done on the remote server

With regards implementation it does depend on how your front end works and the work patterns of the users. But assuming your form has a combo on it (and again, do you really want to populate with 10,000 customer names for the user to select one?) to select a record you can set the form recordsource to say

SELECT * FROM myTable WHERE False

no records are returned and the controls will not display #Error or similar

user selects a record. Instead of having the code

me.filter="PK=" & cboPK
me.filteron=true

your code would be

me.recordsource="SELECT * FROM myTable WHERE PK=" & cboPK

there is potentially a very small benefit in using a query so the query plan does not need to be determined, but in my experience it has not been an issue.

if the form is opened based on a previously selected PK, have the recordsource set as above to false

pass the PK as an openarg , not in the where parameter and in the form open event

if me.openargs<>"" then 
    me.recordsource="SELECT * FROM myTable WHERE PK=" & me.openargs
'or you can use me.recordsource=replace(me.recordsource,"False","PK=" & openargs)
else
    me.dataentry=true
end if

With regards adding records, you can still use the same data entry/allow additions form properties as indicated in the above code. The form is not bound to a single record, but a recordset

oncurrent works exactly the same.

Subforms require a little bit more work. You still populate the linkchild/master properties so the relationship is maintained for new records but in the mainform current event you put

subform1.form.recordsource="="SELECT * FROM mySFTable WHERE FK=" & me.PK

or you can use the replace method if user is only acting on a single record (sometimes a user might want to open several records rather than just the one)

 

[Galaxiom]

With SQL server of course, you should use pass through queries and make use of stored procedures so all the heavy lifting is done on the remote server

Many developers assume Passthrough queries would be faster than Access queries but in most cases they aren't. The Access engine will pass a whole query to be processed by the SQL engine if it is translatable by the ODBC layer.

Stored procedures do make a difference. Where very large tables are being accessed, I found the best performance, particularly for subforms, comes from returning recordsets via ADODB Commands passing parameters to a Stored Procedure.

 

[CJ_London]

Many developers assume Passthrough queries would be faster than Access queries

I must admit, I'm one of those developers. Back to school for me

 

[Galaxiom]

I must admit, I'm one of those developers. Back to school for me

We are all still in school.

 

[shadow9449]

if your form recordsource is based on an entire table, an entire table will be loaded. However once access has received a few records, it will display and continue to load the remainder of the records in the background - so it appears faster. But try and navigate/filter etc before all records are loaded will result in delays

And once the records are loaded? Wouldn't navigating through a form that already has the records loaded be comparable or even faster than retrieving the specific record over the network every time the user navigates?

not true - if you have requested a single record in a query with appropriate criteria, then a single record will be returned.

I never realized this!

to select a record you can set the form recordsource to say

SELECT * FROM myTable WHERE False

no records are returned and the controls will not display #Error or similar

user selects a record....

your code would be

me.recordsource="SELECT * FROM myTable WHERE PK=" & cboPK


With regards adding records, you can still use the same data entry/allow additions form properties as indicated in the above code. The form is not bound to a single record, but a recordset

I'm not following.

If the form is initially opened to a blank record (...WHERE FALSE) then if the user starts to type, they are adding data to a recordset with no records. How does this add a record to the underlying table?

I suppose if the user has a record selected then the Add Record button code would simply change the record source to ....WHERE FALSE, and then we're back to where we were when the form was opened.

oncurrent works exactly the same.

Ok, so changing the recordsource fires Oncurrent. It's not just when you navigate.

 

[static]

I never realized this!

You are talking about different things.
If you specifically request 1 record, 1 record will be displayed. But you are right, much more data would need to be transferred over the network to display it.

There are free alternatives to SQL Server you could investigate. e.g. PostgreSQL.

 

[shadow9449]

You are talking about different things.
If you specifically request 1 record, 1 record will be displayed. But you are right, much more data would need to be transferred over the network to display it.
.

I'm talking about, for example, a customer table with tens of thousands of records and the user wants to navigate to a specific record. If I'm reading CJ_London correctly, if the form's recordsource is set to SELECT * FROM tblClients WHERE PK = 1234 then only record 1234 is transferred over the network. If I'm reading you correctly then my initial assumption is correct, which is that all 50,000 records need to transfer over the network so the form can display record 1234, which brings me back to my initial questions

Am I misunderstanding?

 

[CJ_London]

Wouldn't navigating through a form that already has the records loaded be comparable or even faster than retrieving the specific record over the network every time the user navigates?

most likely, but who is going to scroll through 10,000 records? And we are talking about overall performance here. Most users are looking for a specific record and they will know certain things about that record such as a persons name or part of their address. So better to get than information first and apply it as a criteria to return just those records than load everything and apply a filter.

If the form is initially opened to a blank record (...WHERE FALSE) then if the user starts to type, they are adding data to a recordset with no records. How does this add a record to the underlying table?

let me ask you this. If a user is entering the very first record into a blank table - How does this add a record to the underlying table?

It's a technique I use all the time. It works, believe me

 

[shadow9449]

most likely, but who is going to scroll through 10,000 records? And we are talking about overall performance here. Most users are looking for a specific record and they will know certain things about that record such as a persons name or part of their address. So better to get than information first and apply it as a criteria to return just those records than load everything and apply a filter.

Personally I used the good old

  Me.RecordsetClone.FindFirst "[ID] = " & Me![cboSearch]
      Me.Bookmark = Me.RecordsetClone.Bookmark

Of course they won't be scrolling.

let me ask you this. If a user is entering the very first record into a blank table - How does this add a record to the underlying table?

It's a technique I use all the time. It works, believe me

In that case, the underlying table IS the recordset.

Either way, I'll give it a shot and see what happens. Thanks!

 

[static]

If I'm reading you correctly then my initial assumption is correct, which is that all 50,000 records need to transfer over the network so the form can display record 1234, which brings me back to my initial questions

Am I misunderstanding?

I don't think it's that black and white.

File types tend to have a header (if you open an Access file in notepad you'll see 'Standard ACE DB' among a load of other unreadable garbage) that tells the application what type and version the file is.
If the application doesn't recognise the header or doesn't support the version it wont load it.

An Access file contains both data and information about the data structure (definitions) (tabledefs and querydefs) .
Knowing that it supports this file type, it will also know exactly where to look for those definitions. It doesn't need to read any data to know what tables exist or how data should be formatted, it just needs those definitions.

How much data needs to traverse the network would depend on indexing, concurrent users and the type of recordset you are using. It would also depend on how efficient the database/query engine is - something I can't answer because I don't work for MS and have never done/seen any server logs to analyse.

edit. I'd certainly hope it's efficient enough to not read the entire table every time though.

 

[shadow9449]

I don't think it's that black and white.

File types tend to have a header (if you open an Access file in notepad you'll see 'Standard ACE DB' among a load of other unreadable garbage) that tells the application what type and version the file is.
If the application doesn't recognise the header or doesn't support the version it wont load it.

An Access file contains both data and information about the data structure (definitions) (tabledefs and querydefs) .
Knowing that it supports this file type, it will also know exactly where to look for those definitions. It doesn't need to read any data to know what tables exist or how data should be formatted, it just needs those definitions.

How much data needs to traverse the network would depend on indexing, concurrent users and the type of recordset you are using. It would also depend on how efficient the database/query engine is - something I can't answer because I don't work for MS and have never done/seen any server logs to analyse.

edit. I'd certainly hope it's efficient enough to not read the entire table every time though.

I think you're right that reality is probably neither extreme. What matters is that when you request one record that less data is processed and is therefore faster than a table based on a query. I'm not sure if anyone really knows exactly how that works considering the conflicting information I see about the topic.

 

[static]

I'd like to know too.

I'm sure Access is as efficient as it can be. Whether it's efficient enough will come down to your network.

More important than speed and efficiency, is reliability. I wouldn't use Access if users are likely to connect wirelessly.

http://www.kallal.ca/wan/wans.html

 

[The_Doc_Man]

In general, given what I know about indexing and file systems, the only way to avoid the extreme of reading a whole table while searching for some particular record is if the table is indexed. In that case, Access on the user machine requests the index of the table and finds what record it needs, then directly asks for that information based on whatever the index tells it. Reading the whole index is a lot easier than reading the whole table and Access is smart enough to take advantage of that.

If the table is not indexed on the field holding the thing that you seek, then you trigger a relation scan (if I recall the term correctly) for which the expectation is that you would read about half of the records based on (essentially) random distribution of records with respect to non-key fields.

I'm speculating a little bit but I believe that in the case of an Access FE/Access BE, requesting one record via a recordset based on a query with a WHERE clause on a non-key field doesn't help vis-a-vis opening a recordset and doing a .FindFirst on that same non-key field. In either case, the Access JET/ACE engine has to scan the recordset since all processing is done on the machine running Access. The same is not true for active BE servers.

Some of that is speculative. Take it cum grano.

 

[Galaxiom]

And once the records are loaded? Wouldn't navigating through a form that already has the records loaded be comparable or even faster than retrieving the specific record over the network every time the user navigates?

No. The engine's performance retrieving a specific record is far superior to finding it on a form.

 

[shadow9449]

In general, given what I know about indexing and file systems, the only way to avoid the extreme of reading a whole table while searching for some particular record is if the table is indexed. In that case, Access on the user machine requests the index of the table and finds what record it needs, then directly asks for that information based on whatever the index tells it. Reading the whole index is a lot easier than reading the whole table and Access is smart enough to take advantage of that.

If the table is not indexed on the field holding the thing that you seek, then you trigger a relation scan (if I recall the term correctly) for which the expectation is that you would read about half of the records based on (essentially) random distribution of records with respect to non-key fields.

I'm speculating a little bit but I believe that in the case of an Access FE/Access BE, requesting one record via a recordset based on a query with a WHERE clause on a non-key field doesn't help vis-a-vis opening a recordset and doing a .FindFirst on that same non-key field. In either case, the Access JET/ACE engine has to scan the recordset since all processing is done on the machine running Access. The same is not true for active BE servers.

Some of that is speculative. Take it cum grano.

Let's focus on doing searches based on an index field, as one would usually call up a client based on the primary key which is indexed. You said:

In that case, Access on the user machine requests the index of the table and finds what record it needs, then directly asks for that information based on whatever the index tells it.

That would imply that the ACE/JET BE is intelligent enough to pass back just the requested row. That is a real eye-opener to me and definitely changes how forms should be designed if you expect decent performance.

 

[shadow9449]

No. The engine's performance retrieving a specific record is far superior to finding it on a form.

Good to know. I'm just trying to figure out why that is. It seems that documentation on this topic is a bit scant.

At this point I'm pretty much ready to focus less on the why and start working on a prototype with the one-record search. I am wondering if someone has a working model with subforms.

 

[CJ_London]

I am wondering if someone has a working model with subforms.

To see the difference you will need a) a large dataset (say 100k records) and b) it will need to be located on a network somewhere, not on your local drive. c) the slower the network the better!

And/Or build in some time recording to determine how long a process takes

 

[shadow9449]

To see the difference you will need a) a large dataset (say 100k records) and b) it will need to be located on a network somewhere, not on your local drive. c) the slower the network the better!

And/Or build in some time recording to determine how long a process takes

I have some clients with 30K - 70K clients in their databases and not surprisingly, they would be really happy to have the application in turbo-mode. What I want to do it build a prototype and when I get that right, redevelop some forms in my real application to test it on some users and see if they have better speeds than users of the same application using the old method.

 

[CJ_London]

example code was provided in post #12

Don't forget to also consider list and combobox rowsources as well

 

[shadow9449]

example code was provided in post #12

Yup, will be using that in my prototype.

Don't forget to also consider list and combobox rowsources as well

I plan to use Allen Brown's code so the search doesn't kick in until a few characters are entered, if that's what you mean.