Open form filter vs. recordsource

[IqbalS]

Hi all,

Has anyone tested which method is faster to open up. I currently have a form which when I open I am filtering it by adding to the where condition on the docmd.openform method.

I noticed that the form opens quickly however it takes some time to limit it to the result required. I did a little test by setting the recordsource in the on load event of the form, however this seems to have very little difference to the time taken to fully load. (i would have expected this to be faster as its not loading all the data first)

Has anyone tried and tested this with a larger dataset? Is there something I could do to improve performance?

Thanks

 

[namliam]

Only thing you can do to increase performance (that I know) is add indexes to your search fields.

Also clearing out obsolete data can help a lot as well as running the DB on your computer instead of from the network. Both these options are not always a viable option.

If you trully have masive amounts of data, consider upgrading your backend to SQL Server or Oracle.

Good Luck

 

[Pat Hartman]

If the be is Jet, in theory, a stored querydef with parameter driven selection criteria should be marginally faster since the query is already bound and doesn't need to be "compiled" on the fly. As to the running of the query itself, there shouldn't be any difference. If you want to investigate, do a search for show plan. I don't have the directions handy. You can set a registry key that tells Jet to log all its plans so you can see if there is any difference in how Jet approaches the stored querydef with the OpenForm where argument. After examining the log file, don't forget to turn show plan off again.