I notice that various threads in this forum caution against using RecordSet.RecordCount because it’s not supported by all Xojo-supported databases. But those threads are all a few years old. Is this still a cautionary concern? If it is, I can easily alter places I’ve been using RecordSet.RecordCount and use
SELECT count(*) AS mycount ...
instead. It’s just nice to be able to not use the above function in cases where I want to access all the rows that would be present if I didn’t throw that in the query. That is, using that function results in a single row. RecordSet.RecordCount allows me to access all the rows of that query when that function is not used, while still letting me get the record count.
The recordset interface implements both BOF & EOF, so knowing the number of records returned is seldom relevant. In Xojo most plugins only iterate in the forward direction - MoveNext (which is not a problem if you order your returned data). I use a
Do Until rs.EOF
...
rs.MoveNext
Loop
Loop which allows me to iterate through the recordset without knowing how may records are included.
If I need to just know the number of records (e.g. calls to a customer) I would use “SELECT COUNT(1) AS recordcount FROM sometable;”. You’ll notice I use 1 as a literal rather than * which can cause the db engine to retrieve all the data for each record in the table including blobs (tip from @Dave S). Some people prefer to use 8 instead of * which of course is the same key but unmodified.
In “some” databases, the answer is YES, they do… but in others NO they don’t
Some database engines recognize COUNT and optimize the action, so the overhead is the same, Some databases don’t even need to actually “count” the records, because the cache INSERT and DELETES and store a running count so the result is instant.
But where they do need to count, there are a few databases where “*” creates a datavector that defines the ENTIRE record structure causing an unnecessary amount of traffic where the use of a constant (“1” or “8” or whatever), causes the datavector to be a) small, and b) not related to the physical structure.
So from MY point of view, using COUNT(8) is a good habit to get into… because on some databases it won’t matter one way or another, but on those where it DOES make a difference, you are already in the correct mind-set.
So, with databases where count(*) performs differently than count(1) or count(8), is count(1) loading a single record before counting, and count(8) loading 8 before counting?
Emile… the issue is “" vs ANY constant, it could be 1,2,3,4,5,6,7, or 8, it could be “A”,“B”,“C” etc
it is USUALLY ‘8’ because on AMERICAN/ENGLISH keyboard it it below the "”
And yes this behaviour is NOT consistent across databases, which I tried to have explained
Believe it or not, it can make a very noticiable difference in ORACLE depending on what other optimizations were applied
[quote=366377:@Dave S]Emile… the issue is “" vs ANY constant, it could be 1,2,3,4,5,6,7, or 8, it could be “A”,“B”,“C” etc
it is USUALLY ‘8’ because on AMERICAN/ENGLISH keyboard it it below the "”[/quote]
When I wondered if that literal represented how many records would be loaded before determining the count, I really meant how many columns would be loaded before determining the count. Moot point, I see. All literals have the same effect. Interesting discussion.
Ralph… for those database engines where this would have an effect, the constant vs “*” just says "Hey don’t load the data vector, but here is something to “count”)… Remember the COUNT() function has an argument for a reason, as you can say something line
COUNT(distinct name)
which of course will return how many unique values for the field “name” there are, but obviously it has to load every name from the table to determine that… so SOME databases execute COUNT(*), as “Count the records by reading every field in every record”
SQLite doesnt really seem to matter much between Count(*) and Count(8)… and it DOES seem to remember that value
do a COUNT(*) or COUNT(8)… elapsed time X (depending on how big the database is)
turn right around and do it again… almost instant
Insert/Delete records… it goes back to step one again
at least this seems to be my observations during the creation of my Tadpole SQLite manager
apologizes for everybody. Life is not easy and when I try to understand something, I have bad feelings when the explanation is dark (vs crystal clear).
Dave - above - make the explanation crystal clear (and probably not only for me).
PS: remember that even when you read something in your own language, sometimes the explanation (that can be clear) is not always obvious / not obvious for everyone (the 80/20 rule: 80% of the readers understand and 20% needs more explanation to understand).
Sometimes, a rephrased explanation is enough !
Emile… not sure how much more clear I can make it… if you do not know what a constant is, perhaps a little research?
As far as you statement
I must admit… I rarely can understand any of the posting you make, and I realize it is a language barrier… but still, perhaps try and heed your own advice?
