Record Count on Filtered Query

Are you using a TQuery component? What database (paradox, other?)?

One should be careful to use the Recordcount as it could result in uncecessary traffic. Some datasets determine the recordcount by re-running the query. A better way is to include the count in your query and use a where clause instead of a filter. This way traffic is further reduced i.e. instead of loading all records and then filtering based on your needs, you filter before retrieving the records on the server side.

e.g.

Select EmployeeName, Count(EmployeeName) FROM Employees WHERE Department = 'ACCOUNTS'

Regards
Pierre

Yes I'm using Tquery.

I basically want to show the count total of the query in a label I am refreshing.

How could I do this with your example? Thanks.

You should use another Query as:

function  GetRecCount: Integer;
var
  RCount: Integer;
begin
  Result := 0;
  Query2.Active := False;
  Query2.SQL.Text :=' SELECT COUNT(*) AS RCOUNT FROM FRIENDS WHERE'
                         +  ' FILTER CONDITION';
  Query2.Active := True;
  Result := Query2.FiledByName('RCOUNT').AsInteger;
end;

Just from Head

That would work....though I don't really want to add another query to the load...if it is the only way then so be it.

Any other ideas before I use that one?

function  GetRecCount: Integer;
var
  RCount: Integer;
begin
  RCount := 0;
  try
    Query2.Active := False;
    Query2.SQL.Text :=' SELECT COUNT(*) AS RCOUNT FROM FRIENDS WHERE'
                           +  ' FILTER CONDITION'; // here is the filter condititon of Query1 in SQL format
    Query2.Active := True;
    RCount := Query2.FiledByName('RCOUNT').AsInteger;
  finally
    Result := RCount;
  end;
end;

Say you have a grid listing all the employees, their department, phone numbers etc. you would do something like:

Query1.SQL.Text:= 'SELECT Count(*) AS NumRecs, EmpName, Dept, TelNum FROM Employees WHERE Dept = 'SomeDept';
Query1.Open;
(*If you linked this to the grid using a datasource, then opening the query would show the relevant records.*)
(*Obviously you wouldn't show the Numrecs field in the grid - you can select what you want to see using the grid's columns editor*)

In the Query1's AfterOpen event you type:

YourLabel.Caption:= Format('Number of Employees = %d', Query1['NumRecs']);

Sorry, Last line should have been:

YourLabel.Caption:= Format('Number of Employees = %d', [Query1['NumRecs']]);

If you really have to use filters, a better way to get the filtered record count would be:

function GetRecCount(DataSet: TDataset): integer;
var i: integer;
begin
  i:= 0;
  if Not Dataset.Active then DataSet.Open;
  DataSet.First;
  While Not Dataset.eof do
  begin
    Inc(i);
    Dataset.next;
  end;
  result:= i;
end;

This function will only count filtered records.

I don't have a DataSet I have DataSource....do I swap them?

DOH! Nevermind...that was dumb..excuse the newb

why not:

Query1.Filtered := False;
i := Query1.RecordCount;
Query1.Filtered := True;

Yup... hehe

Your TQuery descends from TDataset, so you could just use as follows:

YourLabel.Caption:= Format('Number of Employees = %d', [GetRecCount(Query1)]);

PierreC:  Your function, I used the second one, works but is kinda slow because it has to run the query again it seems.

Any idea on how to speed it up if possible? Thanks.

select count(*) into recno, tenmstacs.name1,units.OCP1, units.unit, units.build#, unitextr.alpha30_1
FROM TENMSTACS,UNITS,UNITEXTR  
WHERE units.BUILD# = unitextr.BUILD# and units.UNIT = unitextr.UNIT and units.ocp1 = tenmstacs.tenant

It doesn't like this query. I'm using DB2 as my database.

SORRY...it doesn't like this query...not the one above:

select count(*) asrecno, tenmstacs.name1,units.OCP1, units.unit, units.build#, unitextr.alpha30_1
FROM TENMSTACS,UNITS,UNITEXTR  
WHERE units.BUILD# = unitextr.BUILD# and units.UNIT = unitextr.UNIT and units.ocp1 = tenmstacs.tenant

I doesn't actually run the query again. It simply steps through all records if not filtered and all filtered records if filtered and counts them. Since these are already in memory, this should be pretty fast. How many records are we talking about?

You say you used the second GetRec function i.e. the one that Disables Controls first? If not, this could result in much slower results.


If you need it faster, then my first suggestion is the way to go i.e. use a select query that includes the count(*) AS Numrecs and use a where clause instead of using a filters. This should be the quickest.

As a matter of reference, I counted 50,000 records in 120 milliseconds on my Pentium 4 1.8GHz machine using one text field of length = 50; Seems pretty fsat to me. Tell me about your results...

Nevermind...it was slow in DEBUG mode. When run in the compiled application it worked very fast,

Thanks PierreC...

I'm not familiar with DB2 but here are my observations:

1.) Missing space after AS:

SELECT COUNT(*) AS NumRecs

2.) Maybe DB2 doesn't like the *:

Just try using any other field e.g. Count(tenmstacs.name1) instead

Glad I could help