Efficiency, form bloat

[CharlesHerrick]

Hello all, I have two brief questions:

1. I have a DB with many reports and use SQL instead of a query as the Record Source. It makes for a lean Query list but does this increase the DB size or make for less efficient operation?

2. In the same DB, I recently switched form a 2-form menu system with multiple command buttons for selecting update queries and reports, to a 6-tab control form with option groups. I was amazed to see the DB size zoom from 5 Mb to 11 Mb after this change. Is there that much additional behind-the scenes code for tabbed controls and/or option groups?

Thanks for any replies,

--Charles

 

[The_Doc_Man]

I have a DB with many reports and use SQL instead of a query as the Record Source. It makes for a lean Query list but does this increase the DB size or make for less efficient operation?

Increase size? No, shouldn't.

Less efficient? Somewhat. Pre-stored queries are also pre-analyzed by Access and stored in a semi-compiled state. They "hit the ground running." Using SQL is OK but has to be re-analyzed each time. Still, if it is not too complex a Query in SQL, it's not that bad.

Is there that much additional behind-the scenes code for tabbed controls and/or option groups?

Maybe a little extra, but first let's ask whether you have compressed that DB before you made the size comparisons.

Also, don't forget you said you went from two forms and a bunch of controls to one form with six tabs. Each tab has the potential to take up as much space as a form, since it stands in the place of a form.

 

[CharlesHerrick]

Thanks for your reply Doc man. Yes, the DB was compressed before and after the change, so I guess those "extra" forms did make a big difference. Still, I think it makes for a much cleaner look.

Thanks again and happy holidays to all!

 

[Pat Hartman]

I have a DB with many reports and use SQL instead of a query as the Record Source. It makes for a lean Query list but does this increase the DB size or make for less efficient operation?

This doesn't increase size per se, but it does contribute to database bloat. Every time your SQL string needs to run, Jet needs to "compile" it and generate a plan for how it will go about obtaining the requested data. This takes time (not enough to notice) and work space. The work space is not recovered until the db is compacted. So, the more SQL strings you run during a session, the more the db will bloat. Querydef's are "compiled" when they are saved so that overhead is avoided at runtime. Think about what you hear regarding stored procedures in regard to other RDBMS'. Stored procedures are the querydefs of SQL Server, etc. and they are more efficient than embedded SQL which has to be "compiled" on the fly every time it is executed.