That may be a clever solution, but it seems dicey (and of course, not very portable). It's even worth testing to see if it's indeed faster than the count(*) Bob offered.
The thing is with count(*), most DBMS's do a very fast internal lookup to report the number of records in the table, without really scanning the table records (or even any particular column index) themselves. (I suppose some may indeed scan the primary key index for how many records it points to, but since not all tables enforce a primary key, I'd think they have another approach as well.) Indeed, this points to another benefit of count(*) over count(somecolumn). The latter actually (in most DBMSs) checks how many records have a non-null value for that named column (which may indeed not be the same result as count(*)). It seems also likely that such a request is going to have to involve some sort of a scan (whether of an index for an indexed column, or the actual tables for a non-indexed column), which would be faster than the lookup of the internal record count. The DBMS might even hold that record count as a simple number that it keeps up to date internally whenever records are added or deleted. Anyway, count(*) seems the way to go, for sure. Every DBMS is different, though, so test for yourselves. :-) /charlie -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Steve Budan Sent: Monday, February 25, 2002 8:45 AM To: '[EMAIL PROTECTED]' Subject: RE: [CFTALKTor] RecordCount Well, in MSSQL 6.5 and 7 there are system tables you can query to get the number of records in an index. Works if you have a primary key. Not sure how wise it is to query system tables, but I prefered it to having to query large tables. -steve -----Original Message----- From: Bob Silverberg [mailto:[EMAIL PROTECTED]] Sent: Monday, February 25, 2002 1:36 AM To: [EMAIL PROTECTED] Subject: RE: [CFTALKTor] RecordCount Bill, Although you cannot get a count of the number of rows in a table without performing some kind of query, the following is a bit better: <CFQUERY name="name" datasource="datasource"> SELECT Count(*) AS RecordCnt FROM tblFoo </CFQUERY> You can then refer to name.RecordCnt for the number of records. This is better in that the database only has to return one record to CF, whereas using the other method causes the database to return many records. I have seen many debates about whether you should use Count(*) or Count(ID). This supposedly differs between databases, but I'd guess that any db worth its salt will be able to properly optimize a Count(*) query. Bob P.S. Sorry I haven't posted that promised "Caching with Structures" example code yet. I just returned from a week's vacation, so I hope to get it done soon. > -----Original Message----- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On > Behalf Of Bill Sourour > Sent: Monday, 25 February 2002 6:56 p.m. > To: [EMAIL PROTECTED] > Subject: [CFTALKTor] RecordCount > > > Before my question, I am obliged to say: > > "GO CANADA! GO! ... WE ARE THE BEST HOCKEY PLAYERS ON EARTH BABY!!!" > > ok, now that that's out of the way. > > Is there a way to return a record count for a given table whithout > performing a query? To clarify: > > here's what I do now: > > <CFQUERY name="name" datasource="datasource"> > SELECT ID > FROM tblFoo > > </CFQUERY> > > Then I can use #name.recordcount#. But I never use the ID for > anything, the > sole purpose of that query is to get a record count. This strikes me as > inefficient. Any suggestions? > > Thanks in advance. > > - > You are subscribed to the CFUGToronto CFTALK ListSRV. > This message has been posted by: "Bill Sourour" <[EMAIL PROTECTED]> > To Unsubscribe, Please Visit and Login to http://www.CFUGToronto.org/ > Manager: Kevin Towes ([EMAIL PROTECTED]) > http://www.CFUGToronto.org/ > This System has been donated by Infopreneur, Inc. > (http://www.infopreneur.net) > - You are subscribed to the CFUGToronto CFTALK ListSRV. This message has been posted by: "Bob Silverberg" <[EMAIL PROTECTED]> To Unsubscribe, Please Visit and Login to http://www.CFUGToronto.org/ Manager: Kevin Towes ([EMAIL PROTECTED]) http://www.CFUGToronto.org/ This System has been donated by Infopreneur, Inc. (http://www.infopreneur.net) - You are subscribed to the CFUGToronto CFTALK ListSRV. This message has been posted by: Steve Budan <[EMAIL PROTECTED]> To Unsubscribe, Please Visit and Login to http://www.CFUGToronto.org/ Manager: Kevin Towes ([EMAIL PROTECTED]) http://www.CFUGToronto.org/ This System has been donated by Infopreneur, Inc. (http://www.infopreneur.net) - You are subscribed to the CFUGToronto CFTALK ListSRV. This message has been posted by: "charles arehart" <[EMAIL PROTECTED]> To Unsubscribe, Please Visit and Login to http://www.CFUGToronto.org/ Manager: Kevin Towes ([EMAIL PROTECTED]) http://www.CFUGToronto.org/ This System has been donated by Infopreneur, Inc. (http://www.infopreneur.net)
