As the size of my databases increase, i'm becoming more concerned over speed and memory management. I'm using a client/server arrangement, and my main concern is in opening bound forms and reports, and how the data should be filtered. I can see a number of ways to achieve this:
(1) Using the Me.Filter property
(2) Setting the Me.RecordSource property to an SQL string with a WHERE clause
(3) Opening the QueryDef that the form/report record source points to and updating the SQL with a WHERE clause
(4) Providing a WHERE clause in the DoCmd.OpenForm / DoCmd.OpenReport commands
Am I correct in thinking that if using (1), the entire query is sent over the network to the client and is then filtered? This would then be the option that should be avoided. I have read that you should always use stored queries as they run faster than specifying SQL directly, in which case (2) and (3) are not ideal (would there be any benefit between using method (2) or (3)). Finally, i'm not sure how (4) works but guess it reads the SQL for the record source and appends the WHERE text to it.
Ideally, I would have thought a parametised query would be the ideal record source, but I can't find a way to set the values of the parameters before I open the form or report (it seems you can only do this in a QueryDef for a query you are executing / retrieving a recordset from).
Which of the above methods is the quickest and/or best method to use? Are there other methods I haven't thought of and what does everybody else do?
Thanks for your comments, Andy. Do you have any idea of how the DoCmd.OpenForm WHERE clause works? I would have thought it was slower because it must read the SQL from the forms RecordSource property and append the WHERE clause. I was also worried that it might retrieve all records across the server and apply the WHERE on the client side. Given your experience this doesn't seem the case so I will look into using this option, thanks.
Also, I had been using the Filter property to make the required record current (the filter produced a set with only one record), partly as a means to prevent users accidently changing the currect record when using the mouse scroll wheel. I did some timings on this and found that filter was taking between 1.6-2.0 seconds, while finding the record in a recordset and using Me.Bookmark = rec.Bookmark only took 0.6 seconds. I think i'll stop using filter to select a record!
Does anybody else have any experiences? Which of the four methods in the first post would you use?
Either the WHERE clause or DoCmd parameter will be fine. They perform the same function in the same way. If you experience better performance with one or the other, go with that one.
I wouldnt' recommend filter because you are then pulling the ENTIRE dataset client side. By using a WHERE clause, the server returns a "pre-filtered" dataset, which means the dataset is going to be smaller, and therefore take less time to search in your client-app.
bookmark also works faster the filtering because you're only NAVIGATING to a specific record, as opposed to disabliing everything else. The filter properties intended use (speculation) is for paring datasets down to MULTIPLE records with a given set of criteria. Returning or evaluating single records is best done through other methods.
Naturally, all this is IMO, take what you like and leave the rest.