[AccessD] Fastest Way To Get Data Field From Recordset

How slow is slow?

There are at least three ways to access a field in a recordset via VB.

Rs!FieldName
Rs.Fields("FieldName")  and
Rs("FieldName")

Each of these in turn has two variations. You can use
	Rs.this
	Rs.that
	Rs.thenextthing

Or you can write 
	With Rs
		.this
		.that
		.thenextthing
	End With

The use of With / End With used to be recommended by MS as when you
referenced the Rs object Access would apparently refresh its collections,
including the collection of recordsets, and this slowed execution. Using
with/end with cuts the number of references to Rs down to one. It also
reduces the amount of typing that has to be done.

However. I just did some testing in Access 2002, and is seems the alleged
speed benefit of with/end with does not happen. Perhaps the whole collection
refresh issue has been fixed now. Which leaves us with the question of which
syntax *is* faster?  I ran some tests and the results suggest that it does
not make any damned difference. See below...

The code that produces these results simply opens a recordset and then
accesses each record in turn, pulling the value of one field from each
record.

NOT using With Rs
-----------------
1/ Using Rs.Fields('FieldName')
Time to access 683200 records: 4.414063 Seconds
-----------------
2/ Using Rs!FieldName
Time to access 683200 records: 4.4375 Seconds
-----------------
3/ Using With and .Fields('FieldName')
Time to access 683200 records: 4.347656 Seconds
-----------------
4/ Using With and rs('FieldName')
Time to access 683200 records: 4.425781 Seconds
-----------------
5/ Using With and rs!FieldName
Time to access 683200 records: 4.34375 Seconds

So when looping through 680k records it seems like only a few milliseconds
are available to be gained. If you only want to look at one record in a
piece of code then we are approaching quibbling over nanoseconds.

Lambert

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Charlotte Foust
Sent: Wednesday, June 29, 2005 10:47 AM
To: Access Developers discussion and problem solving
Subject: RE: [AccessD] Fastest Way To Get Data Field From Recordset


The first option  is the slowest way.

Charlotte Foust


-----Original Message-----
From: paul.hartland at fsmail.net [mailto:paul.hartland at fsmail.net] 
Sent: Wednesday, June 29, 2005 7:37 AM
To: accessd
Subject: [AccessD] Fastest Way To Get Data Field From Recordset


To all,

I have taken over a project and where I would normally use something like

strMinPayroll = rsMinPayroll.Fields("LowPayrollNo")

it is written as

strMinPayroll = rsMinPayroll!LowPayroll

Is there a difference in speed with these, i.e. is one more efficient than
the other etc....

Thanks in advance for any help on this...

Paul Hartland

-- 

Whatever you Wanadoo:
http://www.wanadoo.co.uk/time/

This email has been checked for most known viruses - find out more at:
http://www.wanadoo.co.uk/help/id/7098.htm
-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com