"SELECT Count ( name a primary key field here or it will slow down the query ) FROM \"Inventory\" WHERE \"Date Purchased Year\" = ? AND RecordOwnerField=?"
; "" ; "" ; DateValue ; RecordOwner )
This leverages arguments that can be supplied to ExecuteSQl as variables ( the ?'s in the statement) the first ? looks for the first variable DateValue and the second ? looks for the second variable RecordOwner. Variables can be fields, calculations, or text literals.
Best ref IMHO
Thanks for this coherentkris - I altered your suggestion to reflect my field names:
"SELECT Count ( Inventory::Record Owner ) FROM \"Inventory\" WHERE \"Date Purchased Year\" = ? AND Record Owner = ?"
; "" ; "" ; Purchase Year ; Record Owner )
but it's still not working for some reason. This field is in the Client table, not the Inventory table. Not sure if that makes a difference in this case.
COUNT(*) is THE standard way to count result set, not slow in FM (at least used in such a simple query that use only one table).
You need all table and field names in quotes if you use space in the names.
"SELECT Count ( * ) FROM \"Inventory\" WHERE \"Date Purchased Year\" = ? AND \"Record Owner\" = ?"
FileMaker uses :: to separate table & field, however SQL uses the single dot (.)
in addition, you need to quote your names with spaces:
" SELECT COUNT(\"Inventory\".\"Record Owner\")
try Count (*) on a table with 50 columns ( 1 number pk and 49 cols of text ) and 1 million rows vs count ( pk ) on the same table. I would be astonished if the performance was the same.
Arguments of "speed" aside, by using the actual field(s)/column(s) in the COUNT sql function, makes it very clear what you are counting (not just rows, but rows with not null values in the column(s) listed).
but I agree with co..., the number of columns and rows (fields and records) may be a deciding factor.
FWIW, I read that the count(*) tells the SQL parser not to worry about column names to help optimize query performance.
I have yet to hear an FMI engineer chime in on any ExecuteSQL discussion. Perhaps the generic COUNT(*) is using the built-in recordID? I don't know, I don't much care. I'll still use the column name(s).
I guess you'd have to benchmark if it ever became an issue. I doubt it would make much of an issue for most cases.
I retried it on FM14, sadly there is no meaningful difference between COUNT()
*, pk (there is no real primary key. validated as unique, not empty), global, stored calculation Get(RecordID), simple indexed field
and the results are unusable for large found set, about 50 seconds on 500,000.
Only COUNT(*) without WHERE clause is optimized (some milliseconds).