SELECT Count(*) or .RecordCount

[gasca]

I have to do a lot of Querys on the same page with thousands of records and would like to know which one of these is a faster operation:

1. sql = "SELECT id FROM Table..."
Rs.Open sql, conn
TCount = Rs.RecordCount

2. sql = "SELECT count(id) as c FROM Table..."
Rs.Open sql, conn
TCount = Rs.Fields("c&quot

or even this
3. sql = "SELECT id FROM Table..."
Rs.Open sql, conn
array = Rs.GetRows()
TCount = Ubound(array,2)

Otherwise, is there any already written function to calculate the time an operation lasts?

 

[onpnt]

any time you can keep things pure SQL it will be more efficienct.

so
sql = "SELECT count(id) as c FROM Table..."
Set Rs = conn.Execute(sql)
TCount = Rs("c&quot

will be your best bet

___________________________________________________________________

[sub]

http://www.onpntwebdesigns.com

The answer to your ??'s may be closer then you think.
Check out Tek-Tips knowledge bank by clicking the FAQ link at the top of the page[/sub]

 

[shaddow]

You can figure it out from this also.

"select * from table" - the database try to return as much as many rows to the client, and sets the RS object to the corect values
- problems - database server/engine workload to high for a simple task, also this operations needs memory for each connection you create.Imagine 10 those connections...

"select count(*) as nr from table" - database counts the records and returns only 1 row and sets the RS object to the corect values
- problems - none that i'm aware of

I've been reading lately that in ASP.NET this has been changed. Some Database access objecs will only return on row of data from database even if you selected more, but the mai problem still remains: "To much workload on the database server/engine"

________
George, M

 

[medic]

If you are going to use the recordset afterwards, other than just counting the records returned, use of RecordCount property is more efficient as it does not require you to execute another SQL command.