Recordsource for subforms

[jacksonmacd]

Conventional wisdom to achieve maximum performance is to limit the
recordset for a form to just a few records, rather than using the
whole table. OK

But for an associated subform, is there any advantage to using a
limited-recordset query instead of the whole table? Or does Access
manage the querying itself, and automatically retrieve the fewest
number of records, thus minimizing network traffic?

 

[Tom Wickerath]

Hi RG,

Access can *always* use your assistance in limiting a recordset. Limit any
recordset to the least amount of records whenever possible.

I've only run this type of test one time before, but the data I collected
seems to suggest differently. The test involved a split application, with the
FE on my hard drive, located in Renton, WA. at the time, and the BE on a file
server located in Bellevue, WA. Test run on 2/2/2006. Outlook & IM closed, so
that these applications would not add to packet transmission activity. Tests
run late at night (~9:00pm) when network traffic at work should have been at
a fairly low level.

I recorded the packets transferred over the network wire with and without a
WHERE clause in the recordsource of a subform linked with a parent form via
the normal LinkMasterField / LinkChildField properties. The primary and
foreign keys were both long integer (Autonumber PK related to Foreign Key;
relationship included enforced referential integrity). The Comment_desc
(description) field is a memo data type. The test was run using (10) randomly
selected records. The average comment length was 870 characters, minimum =
806, maximum = 988.

The recordsource for the subform without the WHERE clause included the
following:

SELECT Comment.pkCommentID, Comment.Request_rk, Comment.Comment_dt,
Comment.Person_rk, Comment.Comment_desc
FROM Comment
ORDER BY Comment.Comment_dt;


The recordsource for the subform with the WHERE clause included the following:

SELECT Comment.pkCommentID, Comment.Request_rk, Comment.Comment_dt,
Comment.Person_rk, Comment.Comment_desc
FROM Comment
WHERE (((Comment.Request_rk)=[Forms]![frmActionsIssues]![Request_rk])
ORDER BY Comment.Comment_dt;

The results included the following:

Without WHERE clause

Initial Final Difference
53708 53962 254
54111 54373 262
54523 54780 257
54856 55113 257
55189 55446 257
55557 55814 257
55964 56221 257
56297 56554 257
56630 56890 260
57523 57780 257

Average: 258
----------------------------------
With WHERE clause

Initial Final Difference
58261 58539 278
58615 58893 278
58969 59247 278
59321 59602 281
59676 59954 278
60030 60308 278
60384 60662 278
60736 61016 280
61090 61369 279
61670 61948 278

Average: 279

Penalty: 8.1 % for including the WHERE clause.

I was surprised by these results, as I would have thought that including the
WHERE clause would have helped, but it did not. Note: The Comment.Request_rk
("_rk" was a previous developer's naming convention for "row key") is the
foreign key field of a relationship with enforced RI, thus it was indexed by
definition.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[jacksonmacd]

Thanks for the info, Tom and Allan. Good stuff.

Interesting results Tom. Thanks for taking the time to share. It would have
been interesting to see the results wothout the OrderBy Clause. I do believe
it would make a marked difference if the db has SQL as the back end. Now the
OP here has quite a bit of imperical data to use for their decision.

Jack: Tom definately knows his stuff.

Hi RG,

Access can *always* use your assistance in limiting a recordset. Limit any
recordset to the least amount of records whenever possible.

I've only run this type of test one time before, but the data I collected
seems to suggest differently. The test involved a split application, with the
FE on my hard drive, located in Renton, WA. at the time, and the BE on a file
server located in Bellevue, WA. Test run on 2/2/2006. Outlook & IM closed, so
that these applications would not add to packet transmission activity. Tests
run late at night (~9:00pm) when network traffic at work should have been at
a fairly low level.

I recorded the packets transferred over the network wire with and without a
WHERE clause in the recordsource of a subform linked with a parent form via
the normal LinkMasterField / LinkChildField properties. The primary and
foreign keys were both long integer (Autonumber PK related to Foreign Key;
relationship included enforced referential integrity). The Comment_desc
(description) field is a memo data type. The test was run using (10) randomly
selected records. The average comment length was 870 characters, minimum =
806, maximum = 988.

The recordsource for the subform without the WHERE clause included the
following:

SELECT Comment.pkCommentID, Comment.Request_rk, Comment.Comment_dt,
Comment.Person_rk, Comment.Comment_desc
FROM Comment
ORDER BY Comment.Comment_dt;

The recordsource for the subform with the WHERE clause included the following:

SELECT Comment.pkCommentID, Comment.Request_rk, Comment.Comment_dt,
Comment.Person_rk, Comment.Comment_desc
FROM Comment
WHERE (((Comment.Request_rk)=[Forms]![frmActionsIssues]![Request_rk])
ORDER BY Comment.Comment_dt;

The results included the following:

Without WHERE clause

Initial Final Difference
53708 53962 254
54111 54373 262
54523 54780 257
54856 55113 257
55189 55446 257
55557 55814 257
55964 56221 257
56297 56554 257
56630 56890 260
57523 57780 257

Average: 258
----------------------------------
With WHERE clause

Initial Final Difference
58261 58539 278
58615 58893 278
58969 59247 278
59321 59602 281
59676 59954 278
60030 60308 278
60384 60662 278
60736 61016 280
61090 61369 279
61670 61948 278

Average: 279

Penalty: 8.1 % for including the WHERE clause.

I was surprised by these results, as I would have thought that including the
WHERE clause would have helped, but it did not. Note: The Comment.Request_rk
("_rk" was a previous developer's naming convention for "row key") is the
foreign key field of a relationship with enforced RI, thus it was indexed by
definition.

Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

Access can *always* use your assistance in limiting a recordset. Limit any
recordset to the least amount of records whenever possible.