Select Count(Distinct)

I have an app which allows the user to count records within a database table that matches Criteria that they specify.

The user can select from 3 drop-down list boxes the 'TableName', 'FieldName' and 'Criteria' and also specify a 'Value' within a Textbox.

The App will then Generate a temp stored procedure that generates the required SQL to search the table counting all records where the value of the 'Fieldname' field matches 'Criteria'.

The App returns Both A Gross and Net Totals as shown in the following example SQL:

SELECT @Gross =Count(*) , @Net=Count(DISTINCT Surname + Address1 + Postcode) from myTable
WHERE (Surname='SMITH' and Postcode like 'SE%') OR .....

This all works fine on tables with small number of recs but can be SLOW in returning results wher the table contains Recs between 500,000 and 2 Million Recs.

I Notice that If I Remove the @Net=Count(DISTINCT Surname + Address1 + Postcode) portion of the SQL the results are returned faster.

Is there a way I can SPEED up the Counting of distinct recs for a particular Surname, Address and postcode?

I chose to use a temp stored Proc to generate the sql because I could not find a way of generating the SQL where the table name is passed as a parameter

I am Not sure If I am using the best approach here in order to get the best performance. Advice Appreciated.



Urgent Reply appreciated.


Linval