Hello all,
I just joined in this conversation, and also need something similar :
I have a table and I need to count the number of records of the table, devided into several types. What is the fastest way to make my count? I have an index to all the appropriate fields.
method 1 : dynamic query & query-prepare with "preselect each table where..."
methed 2 : select count(*) into iCount from table where..
method 3 : do preselect each table where...
Other suggestions are of course also welcome.
Edit : I did some small tests on a small table (2500 records)
The fastests & easiest way seems to be method 1, given the fact that you use the fields(fieldname) option. When I omit the fieldname, and use empty brackets, I have a small performance drop.
Test 1 : empty where statement
Some test results with data in cache :
method 1 : dynamic query : results vary from 40 to 60 ms to count.
method 2 : sql select count : results vary from 100 to 110 ms to count
method 3 : static query : results vary from 50 to 90 ms to count.
method 4 : static for each : results vary from 50 to 100 ms to count.
Some test results without data in cache (no run in the last minutes):
method 1 : dynamic query : result : 80 ms on the first run.
method 2 : sql select count : result : 151 ms on the first run.
method 3 : static query : result : 120 ms on the first run.
method 4 : static for each : result : 100 ms on the first run.
Test 2 : Indexed where statement -- result = 400 rows
Some test results with data in cache :
method 1 : dynamic query : results vary from 10 to 20 ms to count.
method 2 : sql select count : results vary from 10 to 10 ms to count
method 3 : static query : results vary from 10 to 10 ms to count.
method 4 : static for each : results vary from 0 (!) to 20 ms to count.
Some test results without data in cache (no run in the last minutes):
method 1 : dynamic query : result : 10 ms on the first run.
method 2 : sql select count : result : 10 ms on the first run.
method 3 : static query : result : 10 ms on the first run.
method 4 : static for each : result : 20 ms on the first run.
Test 3 : where statement field <> "" ==> gives all results = 2500 rows
Because of the huge speeds I encountered, I wanted to try something strange. In fact, I wanted the same result set as in the first test, but using a where statement. The results :
method 1 : dynamic query : results vary from 50 to 80 ms to count.
method 2 : sql select count : results vary from 50 to 80 ms to count
method 3 : static query : results vary from 70 to 80 ms to count.
method 4 : static for each : results vary from 50 to 100 ms to count.
Some test results without data in cache (no run in the last minutes):
method 1 : dynamic query : result : 70 ms on the first run.
method 2 : sql select count : result : 80 ms on the first run.
method 3 : static query : result : 80 ms on the first run.
method 4 : static for each : result : 90 ms on the first run.
Conclusion
Dynamic query gives for my tests the most reliable and fastests results. All other queries gain performance when you mention a where clause.