I don't know how much more efficient it would be but I often find myself wanting a quick count of a table (generally with a WHERE clause thrown in) and I have to use SELECT somefield rather than COUNT(*) since the latter just returns 1, and always will.

I can easily make up a generic query myself (and probably will) but it might be useful to others to have it built in something like

session/database.HowManyRows(tablename, whereclause)

OTOH if COUNT(*) is no more efficient then forget it

Roy Lambert

Roy,

<< I don't know how much more efficient it would be but I often find myself
wanting a quick count of a table (generally with a WHERE clause thrown in)
and I have to use SELECT somefield rather than COUNT(*) since the latter
just returns 1, and always will. >>

I'm not sure what you're saying here.  I just tried SELECT COUNT(*) FROM
MyTable on a table with 5 rows, and it returned 5, as expected.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim

>I'm not sure what you're saying here. I just tried SELECT COUNT(*) FROM
>MyTable on a table with 5 rows, and it returned 5, as expected.


database.Execute('SELECT COUNT(*) FROM....') = 1

Roy Lambert

Roy,

<< database.Execute('SELECT COUNT(*) FROM....') = 1 >>

Just skip the COUNT(*) then, and run the query as normal.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim


Right now we both know what I'm talking about  - back to the original suggestion


Roy Lambert

Roy,

<< Right now we both know what I'm talking about  - back to the original
suggestion>>

Well, the original suggestion is to have EDB put in the SELECT, *, FROM, and
WHERE keywords for you.  That's not exactly saving a lot of effort.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim

><< Right now we both know what I'm talking about - back to the original
>suggestion>>
>
>Well, the original suggestion is to have EDB put in the SELECT, *, FROM, and
>WHERE keywords for you. That's not exactly saving a lot of effort.

Two points:

1. I've just remembered that COUNT(*) and COUNT(field) may not give the same results (it will be the same if you choose a key field)
2. As you word it you are correct, but if you want to use COUNT(*) then the correct effort analysis is:

a) instantiate a query
b) link the query to the session
c) link the query to the database
d) add the sql to the query
e) run the query
f) read the query results into a local variable
g) close the query
h) free the query

OK I know I can easily write a function and add it to my pack to do the same

The other part of the original suggestion was an implied question: is it more efficient to do COUNT(*) and extract the result than COUNT(field) and recordcount the results?

Roy Lambert

Roy,

<< The other part of the original suggestion was an implied question: is it
more efficient to do COUNT(*) and extract the result than COUNT(field) and
recordcount the results? >>

You can't use COUNT(Column) like that since it works just like COUNT(*),
except that it counts a specific column instead of the rows.  If you want to
use the RecordCount approach, you only need to use the SELECT * FROM MyTable
WHERE... approach and execute it with the Execute method.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim

>You can't use COUNT(Column) like that since it works just like COUNT(*),
>except that it counts a specific column instead of the rows. If you want to
>use the RecordCount approach, you only need to use the SELECT * FROM MyTable
>WHERE... approach and execute it with the Execute method.

That is deadly slow since the .Execute methods don't ask for a sensitive result.

Fear not I'll roll my own. I just need to decide wether to use a little stand alone function, subclass TEDBSession & TEDBDatabase or hack edbcomps.

Roy Lambert

Roy,

<< That is deadly slow since the .Execute methods don't ask for a sensitive
result. >>

They do now.

--
Tim Young
Elevate Software
www.elevatesoft.com