How to limit the number of records sent to Front End? 1

[Jackvmae]

Using Access 2000, I have a standard mdb. The back end (tables only) are shared from one central computer. All the other computers on the network have the front end components only (forms, queries, etc).

My question is this: Let's say you have 10,000 records in the customers table. On the front end, someone opens a form and only wants to view the 75 records of customers from California. Is there a way to make it that only the 75 records they want to view are sent across the network from the back end to the front end? For the sake of network speed, I don't want all 10,000 records coming to the FE.

I know that I can base the form on a query that filters the records to just the California customers. But I understand that the filtering is done on the front end, meaning that all 10,000 records still travel across the network from the BE to the FE.

Can you somehow make it that only the specific records you want travel across the network?

Thanks,
Jack

 

[psemianonymous]

There's a few ways to answer this question:

First: you can limit the records that are 'transferred' by using a WHERE clause in your query. i.e. "WHERE [STATE] = 'CA'" to limit your records down to a manageable level. You should manipulate this sort of information on the Form_Open() event, i.e. Me.RecordSource = "[some valid SELECT statement]"


Second: You can use "SELECT TOP 75 * FROM tblTableName" - the "TOP #" syntax will only return the first # records in the set.


Third: most recordsets don't attempt to pull ALL information across the network at once. Just make sure your form is set to DynaSet. You can read up on the help files to see exactly how Dynaset (DynaSet? whatever) type recordsets work.


Fourth: If you are filtering the records down, I recommend you filter down as much as possible. Meaning, try to filter down to one record, so that the user only edits the one record, or at least one specific small subset of the body of records. This way, your users don't expect to find any other records by browsing through the recordset--they are expected to close the form, re-set the search/filtering terms and re-open the form to the new record. This somewhat goes with my first suggestion. This is what I do to manage large recordsets: use some sort of 'helper form' that loads the actual data entry form. The recordset is chiseled down based on the user's entry in the 'helper form', specifically the data entry form checks Me.OpenArgs on open, and sets the recordsource based on whatever is passed into Me.OpenArgs.


So, uh, yeah, here are some answers. Hope one of them suits you.


Pete

 

[BNPMike]

This is what an index does. If your query is processable using the index, the front end will first pull the index pages across the network. It will then see which pages the California records are on and request the server to send those.

Obviously how well this works depends on how spread about the California records are. However the worst case is 75 pages plus the index. So you'll probably be alright.

 

[grnzbra]

Have I been asleep or something? I thought that when one queried a back end for all the records for California, all the records in the table were sent to the front end where the query then selected the the California records. Has something changed?

 

[psemianonymous]

It depends. When you do a 'snapshot'-type recordset, all records are sent immediately. Dynaset-type recordsets pull one ... 'frame' at a time, however big that is. I just looked, and there's a great help topic (under Access 97, anyway) that basically answers the original poster's question entirely:


"Tips for optimizing external SQL database performance"

Here's some excerpts:
If you need to retrieve a large number of records, a dynaset is faster and more efficient than a snapshot. For example, moving to the end of a snapshot requires the entire result set to be downloaded to the local computer, but with a dynaset only the last screenful of data is downloaded to the local computer. In addition, the fastest way to add new records to a table, form, or query is to click Data Entry on the Records menu. (Data Entry isn't available if the RecordsetType property is set to Snapshot.)

Retrieve only the data you need. Design your queries to limit the number of records that you retrieve, and select only the fields you need, so that Microsoft Access can transfer as little data as possible over the network.

 

[BNPMike]

I thought that when one queried a back end for all the records for California, all the records in the table were sent to the front end where the query then selected the the California records. Has something changed?

That would be a very crude way of doing it. Access Jet is by contrast quite sophisticated. Or to put it another way, why would Microsoft waste time with an index if it was going to do fetch all the records? Some queries will require an area sweep. The optimiser will not be able to avoid it, but any relational database will start by identifying fields with indexes to see if it can cut down i/o. Jet uses cost-based optimisation, examining various strategies for getting the data. Pulling all the table into Access would be the last resort. Whether the database is in one place or split front and back, the data still resides on filesystem files. It's accessing these that is the issue. Whether the work is being done on the back or front end, neither would ask for pages unless Access had reason to believe required records were on those pages.

 

[grnzbra]

So in other words,

SELECT *
FROM Employees
WHERE State = "CA"

when there are 2 CA records in a 50,000 record table, only 2 records are sent over the network?

 

[psemianonymous]

grnzbra",
Let's not say exactly two, let's say the minimum number of 'pages' that contain those two records.

I didn't implement the JET engine, and I didn't even study the performance docs too much, but the basic idea is this:

-Yes. The answer to your question, is "yes."


There's too many levels of 'meta' and performance layers to give any hard evidence, besides to self-assert. Yeah, it's a lot faster to return a recordset of two records, especially if the WHERE clause uses a direct comparison with an indexed field.

 

[BNPMike]

The unit of access for Access is a page. I think a page is now 4k. I'm not sure about that though.

However what comes across the network might be bigger than that. I don't know what the unit of access is for a typical network. If it were 32k then you would always get 8 pages coming across at a minimum, but I've no idea.

 

[grnzbra]

And if you link to a SQL Server view with the same SQL statement, how many records come over the network, assuming the same records are int the SQL Server table?

Or, how about the same SQL statement without the WHERE clause in an Access query and the table contains ONLY the two CA records? How much comes across the network?

 

[psemianonymous]

grnzbra",

Where are you going with this? I don't see what you're asking beyond the surface questions. I think we've moved way beyond the original scope of the question, and now we're just ... see, that's the problem. I have no idea why you just asked that.

Flummoxed,

Pete

 

[grnzbra]

My experience with extremely large databases (each of the 5 main tables had to be in its own back end database in Access97) was that the suggestions so far had minimal effect and the people who built the database said that it was because all the records had to be sent across the network. This seemed to make sense because reports that took 3 to 4 hours to run from the accountant's computer only took about 40 minutes to run when done on the server (which held both the front end and the back ends.)

What you guys are saying is the exact opposite to what the people who built these thing were telling me and it makes me wonder why anyone in their right mind would want to go to SQL Server, considering that you can't use a calculated field in another calculated field the way you can in Access. We have queries here that run 5 page sql statements for queries with 80 to 100 calculated fields, many of which contain other fields calculated in the same query.

 

[psemianonymous]

The first issue:
-Yes, local databases run faster 'locally' rather than over a network. That makes sense--you don't have to bother sending/receiving chunks over the network, so it can be several times faster than over the network.

-Extremely large database cause problems, especially when you're using different data sources? I honestly don't know what happens when you join multiple data backends into the same query, but I assume it does pull most of the data, like you said. But whatever, because I don't know. I hold by my original statement, but you have to realize, your situation is exceptional. Most JET/Access speed optimizations work for data sets that fit into a single database file. If you start using multiple data sources, all these optimizations are useless.

-Calculated fields: if you are really interested in a speed boost, store the query results in a temp table (assuming it fits into a database!). Then for your next round of queries, generate a second (set of) temp tables. And so on, until you have your result. I believe SQL Server even has a tabletype specifically meant for use as a temp table. Anyway.


So while it seems like you're hearing conflicting information, you have to realize: we're not exactly talking about the same situation. I was talking (above) about a bound form linked to exactly one table. You (above) are talking about a multi-step update spanning several Access backends, using multiple layers of querydef datasources. The same rules don't ... exactly ... appply.

 

[grnzbra]

Thanks for the clarification.

 

[BNPMike]

We have queries here that run 5 page sql statements for queries with 80 to 100 calculated fields, many of which contain other fields calculated in the same query

I don't know what the situation is now but not so long ago optimisers couldn't optimise even a single sub-select which was one reason why everyone tried to persuade you to substitute joins. They also tended to ignore complex WHERE clauses ('non-sargable predicates'). I doubt things have changed a lot.

If you've got 5 page SQL tomes it is quite likely Access gives up and brings loads of records across the network that you wouldn't if you coded it yourself in C.

So a) you're probably right in this case, and others of us are wrong, and b) you've brought out another rule for limiting network traffic - keep your SQL statements very simple.