Performance Question, Applying Filter to Recordset

[TheSmileyCoder]

I have a read only DAO.Recordset, which I apply a filter on.

Will the application of the filter cause communication between my Access frontend and Access backend?

I would like to optimize my application to reduce stress on the backend, and therefore in general trying to understand when working with recordsets when my frontend communicates with the backend. For instance, if I do FindFirst or a MoveNext will that cause Access to just look in the recordset I allready have open, or will it attempt to contant the backend and refresh the data?

Is there any way to determine when communication goes on between backend and frontend?

[ADezii]

1. You have 2 choices if you want to create a Filtered Subset of Data via the Recordset Object:
   1. Use an SQL WHERE Clause
   2. Use the Filter Property of a DAO/ADO Recordset
2. If you use the Filter approach, it will take effect immediately.
3. To the best of my knowledge, it is faster to Open a New Recordset based on an SQL Expression with a WHERE Clause then it is to use the Filter Property.
4. The New Filtering will never retrieve additional Rows from the original Source Tables but will only Filter Rows that are already in the Recordset.
5. I'm pretty confident that that same will hold true using a WHERE Clause, namely no additional data will be retrieved from the Source Tables.
6. You can also supply an Array of Bookmarks that are valid in the Current Recordset to Filter the Recordset to show only specific Records.
7. Hope this helps.

[TheSmileyCoder]

As always thanky you for your input!

How would you go about adding a Where clause AFTER the recordset is opened?

[ADezii]

The WHERE Clause would be applied prior to creating the actual Recordset, thus the Filtering.

[TheSmileyCoder]

I have a table with a field called ID_Severity. For 10 values I need to count how many times that variable occurs in my table.

I need to count how many times ID_Severity=1, how many times it equals 2 and so on. My approach to this, in order to avoid running 10 Dcount was to retrieve the full recordset, then apply a filter (for example: ID_Severity=1) , do a recordcount, apply new filter, do another recordcount and so, so I only need to run 1 query against the server, and handle the rest locally. Im using DAO recordset, dbOpenDynaset, dbReadOnly.

However, it seems that the recordcount is not accurate until I do a MoveLast, Movefirst, and it seems to count independent of my filtering (or I mucked something up :P)

Whats the best approach to getting my 10 Counts done, with the least amount of traffic/work to the server? Recordset OR SQL solution

[ADezii]

Unless I am not reading the thread correctly, wouldn't the following SQL work?

SELECT tblSmiley.ID_Severity, Count(tblSmiley.ID_Severity) AS [Severity Count]
FROM tblSmiley
GROUP BY tblSmiley.ID_Severity
ORDER BY tblSmiley.ID_Severity;

However, it seems that the recordcount is not accurate until I do a MoveLast, Movefirst

Unfortunately, this is often correct, in that you must explicitly Traverse the Recordset in order to obtain an accurate Count of Records. I would imagine that this would more than outweigh any potential benefits from your Filter approach.

[TheSmileyCoder]

THANK YOU!
Sometimes the obvious stares you so hard in the face it blinds you....

I think I was so focused on getting it in seperate columns (see example), I didn't see the simple solution.

CountOf1   CountOf2   CountOf3   CountOf4
   1          5          3           6