How to use Select Distinct
Jorge E. Jaramillo
me how this function is used and why, with the following code that pretends
to count the number of different records Fecha on the query cnsGeneral, I
get the error "Too few parameters 2 were expected"
Private Sub Comando2_Click()
Dim dbs As Database, rst As Recordset, lngNumero As Long
Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset("SELECT DISTINCT Fecha From cnsGeneral ")
' Llena el Recordset.
rst.MoveLast
lngNumero = rst.RecordCount
Texto0 = lngNumero
dbs.Close
End Sub
--
Jorge E. Jaramillo
king...@crosswind.net
graft...@my-deja.com
causing the problem.
When using parameters in queries you need to do it similar to this code
that I wrote for one of my systems which creates a temporary query.
(If you add a name between the quotes after the createquerydef
statement then the query wil be saved to your database.) The
parameters are specified before the SQl statement and then assigned
values after the SQL.
set db = CurrentDb
strSQL = "PARAMETERS RefNumber integer;"
strSQL = strSQL & " SELECT [tablename].[fld1], [tablename].[fld2]"
strSQL = strSQL & " FROM [tablename]"
strSQL = strSQL & " WHERE (([tablename].[fld1])=RefNumber);"
Set qdf = db.CreateQueryDef("", strSQL)
qdf.Parameters!RefNumber = lngOSRef
Set rsFirst = qdf.OpenRecordset
Hope this helps,
Grafter.
In article <#5MJChfHAHA.60@cppssbbsa04>,
Sent via Deja.com http://www.deja.com/
Before you buy.
Derek Best
Your question is a little confusing. Is "Fecha" a fieldname? If you want
to count records then your query must contain the word "count". For
example:
Select COUNT(*) as CountOfRecords from CnsGeneral
If "Fecha" is a fieldname and you want to count records where Fecha is a
certain value then you would use...
Select COUNT(*) as CountOfRecords from CnsGeneral WHERE Fecha = <value>
The word DISTINCT has no place in a count query.
Derek Best
"Jorge E. Jaramillo" <kingeor...@hotmail.com> wrote in message
news:#5MJChfHAHA.60@cppssbbsa04...
Jorge E. Jaramillo
The query contains several records, but I need to count only the different
ones on the field "Fecha", that is why I thought of using Distinct
Can I do it adding a parameter to the COUNT instruction?
--
Jorge E. Jaramillo
king...@crosswind.net
Derek Best <dere...@mindspring.com> wrote in message
news:e$C9IZlHAHA.289@cppssbbsa04...
Calvin Smith
>>> The word DISTINCT has no place in a count query. <<<
Pardon me for jumping in but, that's not true at all!
In order for unique, non-null values to be calculated, one can use SQL
similar to the following:
Select Distinct Count(BookPrices) As NonNullBookPriceCount From
tblBookPrices
In addition, on some platforms, you can even use SQL like the following:
Select Count(Distinct Price * 2) From tblBookPrices
Distinct can be used with each of the Sum, Avg, and Count aggregates on
certain platforms.
By the way, when counting records, it's always faster to Count([column
name]), rather than Count(*).
--
Calvin Smith
http://www.CalvinSmithSoftware.com
"Real-world Source Code Solutions"
Michael (michka) Kaplan
"Calvin Smith" <codeheadc...@yahoo.com> wrote in message
news:Ox61jBj...@cppssbbsa02.microsoft.com...
> By the way, when counting records, it's always faster to Count([column
> name]), rather than Count(*).
This is not alays true; I am tempted to say not usually true but it is
dependent on what type of queries you like.
--
MichKa
random junk of dubious value at the
multilingual http://www.trigeminal.com/ and
a new book on internationalization in VB at
http://www.i18nWithVB.com/
Calvin Smith
Nope, you're wrong!
Whenever Count(*) is used, Nulls are checked for as well [in all columns],
along the way, which slows things down.
--
Calvin Smith
http://www.CalvinSmithSoftware.com
"Real-world Source Code Solutions"
Michael (michka) Kaplan <forme...@spamfree.trigeminal.nospam.com> wrote in
message news:uU6$RMmIA...@cppssbbsa02.microsoft.com...
Michael (michka) Kaplan
Calvin,
And nulls are not valid records? Huh?
I would hate to have a db that I relied on for accurate data where Null
meant "hide this and do not count it against anything!" as this would result
in inaccurate data.
--
MichKa
random junk of dubious value at the
multilingual http://www.trigeminal.com/ and
a new book on internationalization in VB at
http://www.i18nWithVB.com/
"Calvin Smith" <codeheadc...@yahoo.com> wrote in message
news:#48KeymIAHA.286@cppssbbsa04...
Calvin Smith
You're misunderstanding and/or misreading my posts. My previous replies to
you and the original postee, had nothing to do with Null *RECORDS*. My
original reply implied that counting a single column, is much faster than
counting all columns to retrieve the total record count. That's just fact!
Again, why have a user wait on Count(*) to swim through all columns and
index Null columns as it's doing it's count, when you can simply use
Count(ColumnName)? That's as inefficient as using Delete * on a million row
table, if one has the option of using Truncate TableName.
By the way, I'm not just pulling this stuff out of my ass. These facts are
from reading through DBA related materials way back in 1995.
--
Calvin Smith
http://www.CalvinSmithSoftware.com
"Real-world Source Code Solutions"
Michael (michka) Kaplan <forme...@spamfree.trigeminal.nospam.com> wrote in
message news:OhmPktn...@cppssbbsa02.microsoft.com...
Michael (michka) Kaplan
The stuff is not being pulled from knowledge of how Jet works though. And I
am talking about JET.
--
MichKa
random junk of dubious value at the
multilingual http://www.trigeminal.com/ and
a new book on internationalization in VB at
http://www.i18nWithVB.com/
"Calvin Smith" <codeheadc...@yahoo.com> wrote in message
news:#4vcEAoIAHA.242@cppssbbsa04...
Calvin Smith
Okay, please enlighten me and the thread-followers on why Count(*) is faster
than Count(ColumnName), with Jet. Personally, I'm requesting a white paper
URL to convince me that your opinion is an educated one. Or, in other words,
prove it!
--
Calvin Smith
http://www.CalvinSmithSoftware.com
"Real-world Source Code Solutions"
Michael (michka) Kaplan <forme...@spamfree.trigeminal.nospam.com> wrote in
message news:#Lf6uwqI...@cppssbbsa02.microsoft.com...
Calvin Smith
about education, not who can flex his muscles the best.
--
Calvin Smith
http://www.CalvinSmithSoftware.com
"Real-world Source Code Solutions"
Marshall Barton <marsh...@fMindspring.com> wrote in message
news:8q9h60$2ls$1...@slb6.atl.mindspring.net...
> The crowd goes silent, holding its breath in anticipation.
>
>
>
>
> Calvin Smith wrote in message <#pC1w5rIAHA.298@cppssbbsa04>...
> >Really Michael?
> >
> >Okay, please enlighten me and the thread-followers on why Count(*) is
> faster
> >than Count(ColumnName), with Jet. Personally, I'm requesting a white
> paper
> >URL to convince me that your opinion is an educated one. Or, in other
> words,
> >prove it!
> >--
> >Calvin Smith
> >
Calvin Smith
>>> Unlike Calvin's view of SQL <<<
Hmmm, I think by people only thinking "the Jet" way, it will inevitably
limit him/her in other arenas, which can be critical to one's career.
Meaning, I've seen tech leads (including myself) filter out candidates for
jobs/projects, for only thinking "the Jet" way.
>>> It is almost always the fastest way to get the total number of records.
<<<
White paper URL please? No, never mind. :-)
This thread was never about the fastest way to count records. That spawned
from my ending comment on my original reply. The fastest way to count all
records in a domain w/ Jet, is probably always going to be through memory
using a Querydef, not a Recordset, regardless of the column(s) included in
the Count aggregate function.
--
Calvin Smith
http://www.CalvinSmithSoftware.com
"Real-world Source Code Solutions"
Marshall Barton <marsh...@fMindspring.com> wrote in message
news:8q9h0g$f7h$1...@slb3.atl.mindspring.net...
> Jorge,
>
> I suspect that the error message is coming from something in the
> cnsGeneral query, because the code you have appears OK to me. (Note:
> you should set dbs to Nothing instead of Closing it, rst should be
> Closed and set to Nothing)
>
> If cnsGeneral were a table, then I would calculate the number of
> different values for Fecha with SQL like this:
>
> SELECT Count(*) AS Different
> FROM [Select Fecha
> FROM cnsGeneral
> GROUP BY Fecha]. As xx
>
> Unlike Calvin's view of SQL, Access/Jet uses Count(*) as special
> syntax for an optimization that just returns the number of records
> regardless of the value in any fields. It is almost always the
> fastest way to get the total number of records.
>
> Marsh
>
>
> Jorge E. Jaramillo wrote in message <#5MJChfHAHA.60@cppssbbsa04>...
Marshall Barton
Marshall Barton
Michael (michka) Kaplan
When I talk with people like Neil Black who explain how the QPE works here
and that the case is indeed optimized (a conversation I had with him back
before Access 95 had shipped), or Kevin Collins on perf issues, then I trust
the discussion, as the test harness that the Jet team uses to test
performance is a helluva lot more impressive than anything I could whip
together (though my replication test harness is more impressive than
theirs!).
As I stated before, I am not your help index or your gopher. You have stated
your opinion, I have stated that I disagree. I guess that will have to be
it.
As I said, I am remembering now why I had you filtered before. I guess its
time to add the filter on this machine, too.
--
MichKa
random junk of dubious value at the
multilingual http://www.trigeminal.com/ and
a new book on internationalization in VB at
http://www.i18nWithVB.com/
"Calvin Smith" <codeheadc...@yahoo.com> wrote in message
news:#pC1w5rIAHA.298@cppssbbsa04...
> Really Michael?
>
> Okay, please enlighten me and the thread-followers on why Count(*) is
faster
> than Count(ColumnName), with Jet. Personally, I'm requesting a white paper
> URL to convince me that your opinion is an educated one. Or, in other
words,
> prove it!
>
> --
> Calvin Smith
> http://www.CalvinSmithSoftware.com
> "Real-world Source Code Solutions"
>
>
> Michael (michka) Kaplan <forme...@spamfree.trigeminal.nospam.com> wrote
in
> message news:#Lf6uwqI...@cppssbbsa02.microsoft.com...
> > The stuff is not being pulled from knowledge of how Jet works though.
And
> I
> > am talking about JET.
> >
> > --
> > MichKa
> >
> > random junk of dubious value at the
> > multilingual http://www.trigeminal.com/ and
> > a new book on internationalization in VB at
> > http://www.i18nWithVB.com/
> >
> > "Calvin Smith" <codeheadc...@yahoo.com> wrote in message
> > news:#4vcEAoIAHA.242@cppssbbsa04...
> > > You're misunderstanding and/or misreading my posts. My previous
replies
> to
> > > you and the original postee, had nothing to do with Null *RECORDS*. My
> > > original reply implied that counting a single column, is much faster
> than
> > > counting all columns to retrieve the total record count. That's just
> fact!
> > > Again, why have a user wait on Count(*) to swim through all columns
and
> > > index Null columns as it's doing it's count, when you can simply use
> > > Count(ColumnName)? That's as inefficient as using Delete * on a
million
> > row
> > > table, if one has the option of using Truncate TableName.
> > >
> > > By the way, I'm not just pulling this stuff out of my ass. These facts
> are
> > > from reading through DBA related materials way back in 1995.
> > >
> > > --
> > > Calvin Smith
> > > http://www.CalvinSmithSoftware.com
> > > "Real-world Source Code Solutions"
> > >
> > >
> > > Michael (michka) Kaplan <forme...@spamfree.trigeminal.nospam.com>
> wrote
> > in
> > > message news:OhmPktn...@cppssbbsa02.microsoft.com...
> > > > Calvin,
> > > >
> > > > And nulls are not valid records? Huh?
> > > >
> > > > I would hate to have a db that I relied on for accurate data where
> Null
> > > > meant "hide this and do not count it against anything!" as this
would
> > > result
> > > > in inaccurate data.
> > > >
> > > > --
> > > > MichKa
> > > >
> > > > random junk of dubious value at the
> > > > multilingual http://www.trigeminal.com/ and
> > > > a new book on internationalization in VB at
> > > > http://www.i18nWithVB.com/
> > > >
> > > > "Calvin Smith" <codeheadc...@yahoo.com> wrote in message
> > > > news:#48KeymIAHA.286@cppssbbsa04...
> > > > > Nope, you're wrong!
> > > > >
> > > > > Whenever Count(*) is used, Nulls are checked for as well [in all
> > > columns],
> > > > > along the way, which slows things down.
> > > > >
> > > > > --
> > > > > Calvin Smith
> > > > > http://www.CalvinSmithSoftware.com
> > > > > "Real-world Source Code Solutions"
> > > > >
> > > > >
> > > > > Michael (michka) Kaplan
<forme...@spamfree.trigeminal.nospam.com>
> > > wrote
> > > > in
Michel Walsh
SELECT DISTINCT COUNT(expression), ... FROM ...
is not the same as the (theatrical)
SELECT COUNT(DISTINCT expression ), ... FROM ...
since, in the first case, the DISTINCT is taken into account well after the
COUNT has been done, and forgot the value comes from a Count.
With Jet 4, you can try:
SELECT COUNT(SELECT DISTINCT expression ), ... to simulate the second
syntax.
Hoping it may help,
Vanderghast, Access MVP
"Calvin Smith" <codeheadc...@yahoo.com> wrote in message
news:Ox61jBj...@cppssbbsa02.microsoft.com...
> >>> The word DISTINCT has no place in a count query. <<<
>
> Pardon me for jumping in but, that's not true at all!
>
> In order for unique, non-null values to be calculated, one can use SQL
> similar to the following:
>
> Select Distinct Count(BookPrices) As NonNullBookPriceCount From
> tblBookPrices
>
> In addition, on some platforms, you can even use SQL like the following:
>
> Select Count(Distinct Price * 2) From tblBookPrices
>
> Distinct can be used with each of the Sum, Avg, and Count aggregates on
> certain platforms.
>
> By the way, when counting records, it's always faster to Count([column
> name]), rather than Count(*).
>
> --
> Calvin Smith
> http://www.CalvinSmithSoftware.com
> "Real-world Source Code Solutions"
>
>
> Derek Best <dere...@mindspring.com> wrote in message
> news:e$C9IZlHAHA.289@cppssbbsa04...
> > Jorge,
> >
> > Your question is a little confusing. Is "Fecha" a fieldname? If you
want
> > to count records then your query must contain the word "count". For
> > example:
> >
> > Select COUNT(*) as CountOfRecords from CnsGeneral
> >
> > If "Fecha" is a fieldname and you want to count records where Fecha is a
> > certain value then you would use...
> >
> > Select COUNT(*) as CountOfRecords from CnsGeneral WHERE Fecha = <value>
> >
> > The word DISTINCT has no place in a count query.
> >
> > Derek Best
> >
> >
> > "Jorge E. Jaramillo" <kingeor...@hotmail.com> wrote in message
> > news:#5MJChfHAHA.60@cppssbbsa04...
Michel Walsh
Hi,
Count(*) just have to reach the table header, not even have to touch any
index or any data. It is then so possible to IMPLEMENT a faster resolution
for it.
Anyhow, it is generally not, for various reason, but difference in time
between * and an indexed column is milli-nano relevant. Use * if null have
to be counted too, a column name if non-null values only on that column have
to be counted. Use the syntax accordingly what you should count, and,
please, FORGET about nano seconds, the result you need is more important
and a wrong result is irrelevant, no matter how FAST you get it!
Vanderghast, Access MVP
"Calvin Smith" <codeheadc...@yahoo.com> wrote in message
news:#48KeymIAHA.286@cppssbbsa04...
> Nope, you're wrong!
>
> Whenever Count(*) is used, Nulls are checked for as well [in all columns],
> along the way, which slows things down.
>
> --
> Calvin Smith
> http://www.CalvinSmithSoftware.com
> "Real-world Source Code Solutions"
>
>
> Michael (michka) Kaplan <forme...@spamfree.trigeminal.nospam.com> wrote
in
> message news:uU6$RMmIA...@cppssbbsa02.microsoft.com...
> > "Calvin Smith" <codeheadc...@yahoo.com> wrote in message
> > news:Ox61jBj...@cppssbbsa02.microsoft.com...
> > > By the way, when counting records, it's always faster to Count([column
> > > name]), rather than Count(*).
> >
Michel Walsh
Hi,
MichKa has no need of my help, but you are basically wrong, on that matter,
three times:
- Count(*) is faster since it uses the pk index and doesn't have to spot the
first non-null value of a potentially nullable column
- Timing is irrelevant because the required result is more important,
getting the wrong data very fast is irrelevant
- The original post has nothing to do with timing
Vanderghast, Access MVP
"Calvin Smith" <codeheadc...@yahoo.com> wrote in message
news:OaKIhhsIAHA.267@cppssbbsa04...
> Mike and I have had our WWF matches for five years now. But this is all
> about education, not who can flex his muscles the best.
>
> --
> Calvin Smith
> http://www.CalvinSmithSoftware.com
> "Real-world Source Code Solutions"
>
>
> Marshall Barton <marsh...@fMindspring.com> wrote in message
> news:8q9h60$2ls$1...@slb6.atl.mindspring.net...
Michel Walsh
Hi,
I think you get a limitation from the DAO (model). It seems to me
cnsGeneral has parameters. A start would be to specify the parameters of a
querydef copy and then, set rst=qdf.OpenRecordset ... but there is the
hickup, how to define a new query (SELECT something FROM .???. ) as
argument?
I suggest you make the complete SQL statement in a query, Q1 and then:
Dim qdf As QueryDef : Set qdf=Dbs.QueryDefs("Q1")
Dim param As Parameter
For each param in qdf.Parameters
param = Eval(param.Name)
Next param
Set rst=qdf.OpenRecordset() ' the query has to be complete, already
' the only "continuation" that DAO allows is to add filters
Now, you cay you want count the number of different values, I suggest your
query statement, in Q1, looks like:
SELECT COUNT(*) FROM (SELECT DISTINCT fieldName FROM TableName) '
Access 2000
SELECT COUNT(a.*) FROM [SELECT DISTINCT fieldName FROM TableName]. AS a
' Access 97
where tableName can there be the query name cnsGeneral.
The given syntax for Access 97 is not documented and not supported. Be
warned.
Hoping it may help,
Vanderghast, Access MVP
"Jorge E. Jaramillo" <kingeor...@hotmail.com> wrote in message
Marshall Barton
Calvin Smith wrote in message ...
>
>>>> Unlike Calvin's view of SQL <<<
>
>Hmmm, I think by people only thinking "the Jet" way, it will
inevitably
>limit him/her in other arenas, which can be critical to one's career.
>Meaning, I've seen tech leads (including myself) filter out
candidates for
>jobs/projects, for only thinking "the Jet" way.
If you should ever get hold of a copy of my resume, please do me the
favor of placing it in the nearest circular file. My career will not
suffer in the slghtest.
While standards are very importent, I have filtered out "tech leads"
who are slaves to them. Ignoring the realities of the local working
environment will inevitably lead to problems.
>
>>>> It is almost always the fastest way to get the total number of
records.
>
>White paper URL please? No, never mind. :-)
It's no trouble, just check Help. It's very clear on this issue.
>
>This thread was never about the fastest way to count records. That
spawned
>from my ending comment on my original reply. The fastest way to count
all
>records in a domain w/ Jet, is probably always going to be through
memory
>using a Querydef, not a Recordset, regardless of the column(s)
included in
>the Count aggregate function.
You're right, this thread wasn't about all that noise. It was about
Jorge's question and providing him with useful answers.
>--
>Calvin Smith
Michel Walsh
Hi,
Should have at least written it right:
SELECT COUNT(*) FROM (SELECT DISTINCT ...
and not SELECT COUNT(SELECT DISTINCT ... which is a catastrophic syntax.
Sorry if I mislead someone.
Vanderghast, Access MVP
"Michel Walsh" <Vande...@msn.com> wrote in message
news:#SGqjHwIAHA.248@cppssbbsa04...
> Hi,
>
>
> SELECT DISTINCT COUNT(expression), ... FROM ...
>
>
> is not the same as the (theatrical)
>
> SELECT COUNT(DISTINCT expression ), ... FROM ...
>
>
> since, in the first case, the DISTINCT is taken into account well after
the
> COUNT has been done, and forgot the value comes from a Count.
>
>
> With Jet 4, you can try:
>
> SELECT COUNT(SELECT DISTINCT expression ), ... to simulate the
second
> syntax.
>
>
> Hoping it may help,
> Vanderghast, Access MVP
>
>
> "Calvin Smith" <codeheadc...@yahoo.com> wrote in message
> news:Ox61jBj...@cppssbbsa02.microsoft.com...
> > >>> The word DISTINCT has no place in a count query. <<<
> >
> > Pardon me for jumping in but, that's not true at all!
> >
> > In order for unique, non-null values to be calculated, one can use SQL
> > similar to the following:
> >
> > Select Distinct Count(BookPrices) As NonNullBookPriceCount From
> > tblBookPrices
> >
> > In addition, on some platforms, you can even use SQL like the following:
> >
> > Select Count(Distinct Price * 2) From tblBookPrices
> >
> > Distinct can be used with each of the Sum, Avg, and Count aggregates on
> > certain platforms.
> >
> > By the way, when counting records, it's always faster to Count([column
> > name]), rather than Count(*).
> >
> > --
> > Calvin Smith
> > http://www.CalvinSmithSoftware.com
> > "Real-world Source Code Solutions"
> >
> >
> > Derek Best <dere...@mindspring.com> wrote in message
> > news:e$C9IZlHAHA.289@cppssbbsa04...
> > > Jorge,
> > >
> > > Your question is a little confusing. Is "Fecha" a fieldname? If you
> want
> > > to count records then your query must contain the word "count". For
> > > example:
> > >
> > > Select COUNT(*) as CountOfRecords from CnsGeneral
> > >
> > > If "Fecha" is a fieldname and you want to count records where Fecha is
a
> > > certain value then you would use...
> > >
> > > Select COUNT(*) as CountOfRecords from CnsGeneral WHERE Fecha =
<value>
> > >
> > > The word DISTINCT has no place in a count query.
> > >
> > > Derek Best
> > >
> > >
Calvin Smith
Duh! Count(ColumnName), *can also be* a pk column. You guys can continue to
write and speculate until you're blue in the face, but there is no way that
I would practice Count(*) on large amounts of data, nor would I preach for
others to get started with that practice. In addition, it's not the best
idea to do a record count with a recordset anyway.
Again, as I've posted elsewhere in this thread, you guys are going off on a
tangent that is irrelevant as hell, to what the original postee was looking
for in an answer. If you guys are stuck on "the jet" way of thinking, great!
Let's agree to disagree, and move on. As a SQL veteran, my entrance into
this thread was only to correct the piece about Distinct and Count having no
business together, which was not true.
--
Calvin Smith
http://www.CalvinSmithSoftware.com
"Real-world Source Code Solutions"
Michel Walsh <Vande...@msn.com> wrote in message
news:euK6fQwIAHA.196@cppssbbsa04...
> Hi,
>
>
> MichKa has no need of my help, but you are basically wrong, on that
matter,
> three times:
> - Count(*) is faster since it uses the pk index and doesn't have to spot
the
> first non-null value of a potentially nullable column
> - Timing is irrelevant because the required result is more important,
> getting the wrong data very fast is irrelevant
> - The original post has nothing to do with timing
>
>
>
> Vanderghast, Access MVP
>
> "Calvin Smith" <codeheadc...@yahoo.com> wrote in message
> news:OaKIhhsIAHA.267@cppssbbsa04...
> > Mike and I have had our WWF matches for five years now. But this is all
> > about education, not who can flex his muscles the best.
> >
> > --
> > Calvin Smith
> > http://www.CalvinSmithSoftware.com
> > "Real-world Source Code Solutions"
> >
> >
> > Marshall Barton <marsh...@fMindspring.com> wrote in message
> > news:8q9h60$2ls$1...@slb6.atl.mindspring.net...
Calvin Smith
Geez, another cop-out answer by Michael Kaplan. We should be used to those
by now.
Yes, please filter me out. As I wrote in the Module/VBA thread earlier
today, your constant curt answers over the last five years, are just short
of worthless.
--
Calvin Smith
http://www.CalvinSmithSoftware.com
"Real-world Source Code Solutions"
Michael (michka) Kaplan <forme...@spamfree.trigeminal.nospam.com> wrote in
message news:OpeY29vIAHA.196@cppssbbsa05...
> Calvin,
>
> When I talk with people like Neil Black who explain how the QPE works here
> and that the case is indeed optimized (a conversation I had with him back
> before Access 95 had shipped), or Kevin Collins on perf issues, then I
trust
> the discussion, as the test harness that the Jet team uses to test
> performance is a helluva lot more impressive than anything I could whip
> together (though my replication test harness is more impressive than
> theirs!).
>
> As I stated before, I am not your help index or your gopher. You have
stated
> your opinion, I have stated that I disagree. I guess that will have to be
> it.
>
> As I said, I am remembering now why I had you filtered before. I guess its
> time to add the filter on this machine, too.
>
> --
> MichKa
>
> random junk of dubious value at the
> multilingual http://www.trigeminal.com/ and
> a new book on internationalization in VB at
> http://www.i18nWithVB.com/
>
> "Calvin Smith" <codeheadc...@yahoo.com> wrote in message
> news:#pC1w5rIAHA.298@cppssbbsa04...
> > Really Michael?
> >
> > Okay, please enlighten me and the thread-followers on why Count(*) is
> faster
> > than Count(ColumnName), with Jet. Personally, I'm requesting a white
paper
> > URL to convince me that your opinion is an educated one. Or, in other
> words,
> > prove it!
> >
> > --
> > Calvin Smith
> > http://www.CalvinSmithSoftware.com
> > "Real-world Source Code Solutions"
> >
> >
> > Michael (michka) Kaplan <forme...@spamfree.trigeminal.nospam.com>
wrote
> in
> > message news:#Lf6uwqI...@cppssbbsa02.microsoft.com...
> > > The stuff is not being pulled from knowledge of how Jet works though.
> And
> > I
> > > am talking about JET.
> > >
> > > --
> > > MichKa
> > >
> > > random junk of dubious value at the
> > > multilingual http://www.trigeminal.com/ and
> > > a new book on internationalization in VB at
> > > http://www.i18nWithVB.com/
> > >
> > > "Calvin Smith" <codeheadc...@yahoo.com> wrote in message
> > > news:#4vcEAoIAHA.242@cppssbbsa04...
> > > > You're misunderstanding and/or misreading my posts. My previous
> replies
> > to
> > > > you and the original postee, had nothing to do with Null *RECORDS*.
My
> > > > original reply implied that counting a single column, is much faster
> > than
> > > > counting all columns to retrieve the total record count. That's just
> > fact!
> > > > Again, why have a user wait on Count(*) to swim through all columns
> and
> > > > index Null columns as it's doing it's count, when you can simply use
> > > > Count(ColumnName)? That's as inefficient as using Delete * on a
> million
> > > row
> > > > table, if one has the option of using Truncate TableName.
> > > >
> > > > By the way, I'm not just pulling this stuff out of my ass. These
facts
> > are
> > > > from reading through DBA related materials way back in 1995.
> > > >
> > > > --
> > > > Calvin Smith
> > > > http://www.CalvinSmithSoftware.com
> > > > "Real-world Source Code Solutions"
> > > >
> > > >
> > > > Michael (michka) Kaplan <forme...@spamfree.trigeminal.nospam.com>
> > wrote
> > > in
> > > > message news:OhmPktn...@cppssbbsa02.microsoft.com...
> > > > > Calvin,
> > > > >
> > > > > And nulls are not valid records? Huh?
> > > > >
> > > > > I would hate to have a db that I relied on for accurate data where
> > Null
> > > > > meant "hide this and do not count it against anything!" as this
> would
> > > > result
> > > > > in inaccurate data.
> > > > >
> > > > > --
> > > > > MichKa
> > > > >
> > > > > random junk of dubious value at the
> > > > > multilingual http://www.trigeminal.com/ and
> > > > > a new book on internationalization in VB at
> > > > > http://www.i18nWithVB.com/
> > > > >
> > > > > "Calvin Smith" <codeheadc...@yahoo.com> wrote in message
> > > > > news:#48KeymIAHA.286@cppssbbsa04...
> > > > > > Nope, you're wrong!
> > > > > >
> > > > > > Whenever Count(*) is used, Nulls are checked for as well [in all
> > > > columns],
> > > > > > along the way, which slows things down.
> > > > > >
> > > > > > --
> > > > > > Calvin Smith
> > > > > > http://www.CalvinSmithSoftware.com
> > > > > > "Real-world Source Code Solutions"
> > > > > >
> > > > > >
> > > > > > Michael (michka) Kaplan
> <forme...@spamfree.trigeminal.nospam.com>
> > > > wrote
> > > > > in
> > > > > > message news:uU6$RMmIA...@cppssbbsa02.microsoft.com...
> > > > > > > "Calvin Smith" <codeheadc...@yahoo.com> wrote in
message
> > > > > > > news:Ox61jBj...@cppssbbsa02.microsoft.com...
> > > > > > > > By the way, when counting records, it's always faster to
> > > > Count([column
> > > > > > > > name]), rather than Count(*).
> > > > > > >
Michel Walsh
Hi,
Anyhow about the "hypothesis", and since you still claim Count(fieldname) is
faster than Count(*), here an experimentation.
The code:
------------
Private Type LARGE_INTEGER
lowpart As Long
highpart As Long
End Type
Private Declare Function QueryPerformanceCounter Lib "kernel32"
(lpPerformanceCount As LARGE_INTEGER) As Long
Private Declare Function QueryPerformanceFrequency Lib "kernel32"
(lpFrequency As LARGE_INTEGER) As Long
Public Sub TestingCount()
Dim rst As ADODB.Recordset
Dim result As Long
Dim freq As LARGE_INTEGER
Dim starting As LARGE_INTEGER
Dim ending As LARGE_INTEGER
Dim dfreq As Variant
QueryPerformanceFrequency freq
dfreq = LargeToDec(freq)
QueryPerformanceCounter starting
Set rst = CurrentProject.Connection.Execute("SELECT COUNT(*) FROM
Iotas;")
result = rst.Fields(0)
rst.Close
QueryPerformanceCounter ending
Debug.Print "COUNT(*)", (LargeToDec(ending) - LargeToDec(starting)) /
dfreq
QueryPerformanceCounter starting
Set rst = CurrentProject.Connection.Execute("SELECT COUNT(Iota) FROM
Iotas;")
result = rst.Fields(0)
rst.Close
QueryPerformanceCounter ending
Debug.Print "COUNT(Iota)", (LargeToDec(ending) - LargeToDec(starting)) /
dfreq
QueryPerformanceCounter starting
result = DCount("*", "Iotas")
QueryPerformanceCounter ending
Debug.Print "DCount(""*"")", (LargeToDec(ending) - LargeToDec(starting))
/ dfreq
QueryPerformanceCounter starting
result = DCount("Iota", "Iotas")
QueryPerformanceCounter ending
Debug.Print "DCount(""Iota"")", (LargeToDec(ending) -
LargeToDec(starting)) / dfreq
End Sub
Public Function LargeToDec(Arg As LARGE_INTEGER) As Variant
' transform a large integer into a Decimal
LargeToDec = Arg.lowpart + 2 * CDec(Arg.highpart) * 2 ^ 31
End Function
---------------
The results, on a table with1 000 000 records.
No index, no PK:
TestingCount
COUNT(*) 1.5915711098558308791114850878
COUNT(Iota) 2.9424446563893856930459896311
DCount("*") 1.5273101672670221307394848397
DCount("Iota") 2.9516896835520482206402711405
( the results are in seconds)
and with a primary key on the single field Iota:
TestingCount
COUNT(*) 0.2193596618118610572402198491
COUNT(Iota) 0.2209369568096065813932828353
DCount("*") 0.2191375666076088978881679409
DCount("Iota") 0.2298417173574525931500810438
So, from this experience, your claim is wrong. And NO NULLs were present,
at your advantage.
The result may vary accordingly to the need to page memory on HD or not, so,
from the amount of memory, the OS, the speed of the memory and of the CPU (I
assume in decreasing order of importance). The results may also vary from
run to run, mainly from the first run to the others, caches and other page
flushing being more probable at the first run.
That's not as much as to convince you, but to convince any user who may
still wonder who to believe, that:
- we have to forget about speed when the result itself is the main important
point,
- in cases where both results are identical, Count(*) is marginally faster
than Count(FieldName) by the thought of a blink of an eye (0.001 sec) when a
sound design supports the computation.
by Galilean experience, not by Aristotlean deduction.
"Calvin Smith" <codeheadc...@yahoo.com> wrote in message
news:Oq7V1FzIAHA.172@cppssbbsa05...
> Duh! Count(ColumnName), *can also be* a pk column. You guys can continue
to
> write and speculate until you're blue in the face, but there is no way
that
> I would practice Count(*) on large amounts of data, nor would I preach for
> others to get started with that practice. In addition, it's not the best
> idea to do a record count with a recordset anyway.
>
> Again, as I've posted elsewhere in this thread, you guys are going off on
a
> tangent that is irrelevant as hell, to what the original postee was
looking
> for in an answer. If you guys are stuck on "the jet" way of thinking,
great!
> Let's agree to disagree, and move on. As a SQL veteran, my entrance into
> this thread was only to correct the piece about Distinct and Count having
no
> business together, which was not true.
>
> --
> Calvin Smith
> http://www.CalvinSmithSoftware.com
> "Real-world Source Code Solutions"
>
>
> Michel Walsh <Vande...@msn.com> wrote in message
> news:euK6fQwIAHA.196@cppssbbsa04...
> > Hi,
> >
> >
> > MichKa has no need of my help, but you are basically wrong, on that
> matter,
> > three times:
> > - Count(*) is faster since it uses the pk index and doesn't have to spot
> the
> > first non-null value of a potentially nullable column
> > - Timing is irrelevant because the required result is more important,
> > getting the wrong data very fast is irrelevant
> > - The original post has nothing to do with timing
> >
> >
> >
> > Vanderghast, Access MVP
> >
> > "Calvin Smith" <codeheadc...@yahoo.com> wrote in message
> > news:OaKIhhsIAHA.267@cppssbbsa04...
> > > Mike and I have had our WWF matches for five years now. But this is
all
> > > about education, not who can flex his muscles the best.
> > >
> > > --
> > > Calvin Smith
> > > http://www.CalvinSmithSoftware.com
> > > "Real-world Source Code Solutions"
> > >
> > >
> > > Marshall Barton <marsh...@fMindspring.com> wrote in message
> > > news:8q9h60$2ls$1...@slb6.atl.mindspring.net...