.returnsrecords vrs .recordcount which method is faster? 1

[hwkranger]

Ok, Being less experienced in knowing which is the best/fastest way to do things - I'm asking this question.

Which is faster? and is there a faster way than what I'm doing?

I want to know if there exists 1 or many items with a certain ID in it's foreign key, to find out if this situation exists, which method is the fastest? I've achieved it by the following:

rst.Open "SELECT [tbDailyOtherTickets].[OffsetToID], [tbDailyOtherTickets].[TypeID], [tbDailyOtherTickets].[CategoryID] FROM tbDailyOtherTickets WHERE ((([tbDailyOtherTickets].[OffsetToID])= " & [Forms]![adjustmentsProof]!ID & &quot And (([tbDailyOtherTickets].[TypeID])=16) And (([tbDailyOtherTickets].[CategoryID])=8));", CurrentProject.Connection, adOpenKeyset, adLockOptimistic
If rst.RecordCount > 0 Then
'some code
end if
rst.close

also, I can have a saved query with parameters that I pass at run time. (this way I can reuse each query) eliminating the reference to forms (I can pass it at run time)

using a saved query I can do the following:

set qd = currentdb.querydefs("MyQueryName&quot
qd!listid = me.openargs
qd.execute
if qd.returnsrecords = true then
'some code
end if
set qd = nothing

If anyone knows of a site, or resource that can tell me which methods and ways of doing things are the fastest and most efficient i'd greatly appreciate it.

Thank you very much in advance.



Randall Vollen
National City Bank Corp.

Just because you have an answer - doesn't mean it's the best answer.

 

[jrbarnett]

Hi,

They are for different purposes.

The .REturnsRecords property of a query will return true if it returns a recordset, for example a select or union query, but an update, insert, delete or data definition won't return any - because it doesn't return a record set.

Of those query types where ReturnsREcords is True, then the .RecordCount variable will return the number of records in the recordset. If tried on anything else, recorcount will be 0.

John

 

[hwkranger]

well,

I knew that part. Actually what I have up there is incorrect, there should be no:

qd.execute

since it's a select statement.

Ideally I want to return a true or false whether or not there are records that meet a certain criteria. I want this as fast as possible, since I have 60,000 records - I don't need to know how many, just that at least 1 exists.

i could use a dlookup, a dcount, a recordset .count, a querydef with .returnsrecords. There's a million ways to get my answer, I want the fastest.




Randall Vollen
National City Bank Corp.

Just because you have an answer - doesn't mean it's the best answer.

 

[jrbarnett]

Hi,

I think the speed will depend on the context of your code. For example, if you need to open a recordset to process some data, then use If Recordset.RecordCount > 0 syntax.
If not, then DCount returning a positive result or DLookup returning a not null value.

Basically why not test which is best in your situation:

Dim startDate As Date
Dim EndDate As Date

startdate = now()
' process your data here
enddate = Now()

Msgbox "Time taken is: " & DateDiff ("s", startdate, enddate)

Remember that appropriate indexes on the fields and primary/foreign keys will help speed up any queries that look at actual data (ie use dlookup, retrieve actual records rather than count, or have criteria specified).

John

 

[hwkranger]

This is the most sensible thing I've heard since I started asking questions about which solution is 'faster'

Kudo's to you for this!!

A

Randall Vollen
National City Bank Corp.

Just because you have an answer - doesn't mean it's the best answer.

 

[crpjaviman]

kwkranger

I have used this vb code to find the first row of an item. This should be able to be modify for your use and increase speed. I could only find the code in one of my excel uses but can be converted to what ever you need.

With ActiveSheet.Range("C&quot
Set C = .Find("Sales Summary", LookIn:=xlValues)

Also, you might want to look up the "EXISTS" statement in SQL. It will tell you if the item you are looking for is in the recordset.
Sorry I can not give you an example.


Thanks,
crpjaviman