Speed / Efficiency - Question 1

[cascot]

I have a speed/efficiency type question that any feedback on would be much appreciated.

The question refers to the psuedo-client/server architecture where both frontend and backend are Access .mdb's, one on the client machine holding all non-table objects and one on a remote server holding only data tables.

1. If I open up a form (one intended to display information relating to a single record) with it's Record Source set to a query of the sort "SELECT * FROM TableName" and use code similar to

WhereClause = "TableNameId=" & ThisRecordId
DoCmd.OpenForm "Form", acNormal, , WhereClause, acFormEdit, acDialog

Will the entire table still be "downloaded" from the server before the WhereClause is applied to isolate the single record?

 

[psemianonymous]

This is a setting you can configure on the bound form's properties box. By default: no, you only download ... for lack of research, let me refer to this as 'an unspecified amount of data, smaller than the entire set' (Dynaset). It's not a crippling amount.

However, if you do need to search inside the form or run a filter of some sort, this is where you start to run into problems. If you have a million records and you hit the 'Filter' key...expect some large performance owies.

The general wisdom is that for large data sets (tens of thousands of records or more) filter the data source by default and only 'bind' the form to a subset, e.g. "only records from the last month" or "only records matching the user's search query" or something like that. Table indexes, of course, are key for performance in any size set.


For small data sets you'll be fine no matter what.

 

[cascot]

Thanks for your reply pseale, though I am still a little unclear.

Maybe a rewording is called for...

If I include a WhereCondition as part of a DoCmd.OpenForm and the form being opened is bound to a query which returns the contents of the entire underlying table (say 10,000 records), will the existance of the WhereCondition result in (say) just two records being "downloaded" by the form, or will the entire 10,000 record recordset be "downloaded" to the form before the WhereCondition is then applied resulting in the final two record recordset?

I am aware that in the typical pseudo-client/server Access architecture, there is no way to pull only the records you wish over the network from the server as it is unable to process any SQL. It is merely a file sharer of the server-side .mdb and it's data tables. But there is always a most efficient approach and I'm keen to find out how best to approach certain situations in order to optimize speed.

 

[psemianonymous]

Sorry, let me rephrase my statement:

I wouldn't use WhereCondition to boost performance. I believe (not 100% sure) this is just another way to apply a "Filter" which does not affect the recordset.

If you want to actually filter the recordset and actually boost performance, do:

1. Pass in the WHERE clause (or something you can build the WHERE clause off of, or something you can build an SQL statement out of) into the form's OpenArgs argument.

2. On Form_Open() event, set Me.RecordSource = Me.OpenArgs if you have set this. This will change the recordsource BEFORE you load the data, thus actually boosting performance.


So in example form:

'note the openargs assignment, also note that 
'you would probably do some string manipulation
'and put in a variable instead of just '5'
DoCmd.OpenForm "frmEtc", OpenArgs:="SELECT * FROM qryInvoices WHERE InvoiceID = 5"



'----------------------
'in the form module:

'pseudocode / aircode
'I might have screwed up the form_open 
'event, just build your own event instead of pasting this
Public Sub Form_Open()
    If Nz(Me.OpenArgs, Empty) <> Empty Then
        Me.RecordSource = Me.OpenArgs
    End If
End Sub

 

[cascot]

Thanks again pseale,

I guess much of this comes down to understanding precisely what is going on "under the hood" of Access, but I too belive that the WhereCondition may just be another form of form level filtration.

I guess the best way is to run some timing tests with a very large table, say 500,000 records.

Thanks for your replies. I very much appreciate it.

 

[jsteph]

cascot,
You should set the form's recordsource to either a select statement that you change every time you select a 'where' condition, or use a stored querydef in which you alter the sql.

First of all, generally if the WHERE clause is on an indexed field/fields, then regardless of what's in the where clause, the entire index has to come over. Once JET gets a pointer to the row/rows from the index, it can get just these pages from the .mdb file. So it doesn't have to drag the whole .mdb, or the whole table, just to get a few rows--*if* you use an index. If just part of the WHERE is indexed, it will get all the records that it can based on the index, then pare it down on the 'client' based on the rest of the WHERE clause.

But the suggestions above are good--use query or sql for each 'search' or 'open' you do with the form.

For example the form's source might be "select * from Customers WHERE custnum = 0" --either a query or straigt sql in the Recordsource.

(The * is not a performace hit so much with access because it schleps all fields anyway--but in general you should limit the select list to just what the form will use)

The 'custum=0' above causes the form to open empty, with no real network traffic. I'll typically have a box where they'll type in a name or something, then on clicking 'search' button (or afterUpdate or whatever), just set the recordsource to be the sql but include the new WHERE clause.
--Jim

 

[cascot]

jsteph,

Thanks for your reply.

What is your opinion or knowledge of the specific example I gave, namely if I include SQL like "TableNameId = SpecificId" in the DoCmd.OpenForm WhereCondition when opening a form whose RecordSource is set to a query like "SELECT * FROM TableName". Is that different (and less efficient) to opening the same form and dynamically setting the RecordSource at that point to be "SELECT * FROM TableName WHERE TableNameId = SpecificId"?

 

[jsteph]

cascot,
I'm not sure if, when the OpenForm is called, the recordset is already fetched and the 'WHERE' argument is tacked on after the fact, which would be slower. I assume it's done prior to fetching, but I don't know.

However, I never user filters or 'where' arguments in forms, I always reset the querydef's .SQL or set the RecordSource property. To me, it's cleaner, I never have to worry that a record being sought might not show because of a filter that I forget about was on. The user can right-click and do his own filter if that's needed, but you can either disable this (AllowFilters=No) or set the .Filter to "" after setting the recordsource or sql to make sure that the sought after recordsource is truly what the form is displaying.

Again, having a form's default recordsource to a wide-open full-table scan can be troublesome. Typically if it's a dynaset, the form may open to the first record fairly quickly, but scrolling through or filtering or doing a 'Find' will take longer, since it then has to look at all the records if the Find is on a non-indexed field.
--Jim

 

[psemianonymous]

Jim has said everything I was trying to say. I'll just chime in in agreement: don't use the WhereCondition if you're worried about efficiency in any way.

 

[cascot]

I just wanted to thank both pseale and jsteph for the time you took to reply to my questions and for all the other insight you offered.