View Single Post
Old 05-02-2003, 10:12 AM   #3
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 27,246
Thanks: 13
Thanked 1,382 Times in 1,317 Posts
Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light
Jet needs to do a lot of work to parse an SQL string and figure out how to obtain the requested recordset. It has to analayze the available indexes and determine if any of them are usable, it needs to look at the row counts and decide which table to process first, and so on. It takes time and workspace. And as we know, Access does not clean up its workspace until the db is compacted. That's why you see so much bloat in your databases. When you use querydefs, the process of binding the query happens once when the query is saved (and also again when the db is compacted) rather than EVERY time the query is executed.

This also applies to SQL strings used as recordsources in forms and reports and to domain functions. All of these should be referring to stored querydefs, usually with parameters. The only time you should be building SQL on the fly is when the actual SQL string needs to be variable. For example a TOP n query where n needs to vary must be built in code because n can't be a parameter, but a query with a where clause that is fixed except for the actual value being searched for should be a stored querydef.

Splitting your db won't solve your bloat problem since you'll be bloating the be by constantly adding and deleting large numbers of rows and the front end will still bloat due to your use of VBA to build SQL strings on the fly. However, if you change your approach to SQL, you can substantially reduce the bloat in the fe.

The db should be split regardless since it will make change management easier and reduce the potential for corruption in a shared environment.

Pat Hartman is offline   Reply With Quote