[AccessD] Record count

Jim Moss jim.moss at jlmoss.net
Wed May 25 13:58:37 CDT 2005


What about just comparing execution plans for the two types of COUNT query?

Jim

> I have heard the same about Access.  I've never actually tried it though.
> It seems a test could be done to time both on a large table to see which
> is
> faster.
>
> John W. Colby
> www.ColbyConsulting.com
>
> Contribute your unused CPU cycles to a good cause:
> http://folding.stanford.edu/
>
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Bobby Heid
> Sent: Wednesday, May 25, 2005 2:31 PM
> To: 'Access Developers discussion and problem solving'
> Subject: RE: [AccessD] Record count
>
>
> Just a side note...
>
> My boss told me, that at least for SQL Server that using
> SELECT COUNT(*)
> Was better optimized for execution speed than
> SELECT COUNT(somefield)
>
> He was not sure if this carried over to Access though.
>
> Bobby
>
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Hale, Jim
> Sent: Wednesday, May 25, 2005 2:16 PM
> To: 'Access Developers discussion and problem solving'
> Subject: RE: [AccessD] Record count
>
>
> Thanks everyone. Here is what I use when I need to determine the NUMBER of
> records, it just seems like a lot of code to see if there are ANY records.
>
> Dim dbs As Database, rs(1) As Recordset, strQry(1) As String, intCt As
> Integer
>
> Set dbs = CurrentDb
> strQry(1) = "SELECT Count(tblNewTransCodes.fldDesc) AS CountOffldDesc FROM
> tblNewTransCodes;"  Set rs(1) = dbs.OpenRecordset(strQry(1))
>         intCt = rs(1).Fields("countoffldDesc")
>  If Not (rs(1) Is Nothing) Then rs(1).Close: Set rs(1) = Nothing
> If Not (dbs Is Nothing) Then dbs.Close: Set dbs = Nothing
>
> Jim Hale
>
> --
> 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
>





More information about the AccessD mailing list