Queries On Steroids ... Part IV
I was reminded of this article in a discussion in a newsgroup/forum. As I’m always interested in the hard core details of anything computer related but especially anything to do with Access or Jet I thought I’d share this with you.
Queries On Steroids ... Part IV talks about Jet’s ShowPlan and ISAMStats
I will also add an anecdote at a clients. They had one table with 800K transaction records. Performance wasn’t the best in the main inquiry screen which pulled in just the last two weeks transactions. The query had joins to about eight or ten other tables to display data. One of these tables was a job table with about 500 records as well as a Boolean active field. I had a vague memory of a newsgroup posing about ten years ago where someone mentioned indexing a Boolean field made a big difference so I tried that. And the form open dropped from 30 seconds to five seconds.
(The client has since upsized the database to SQL Server so now the form open is just about instantaneous.)