querydefs

[wazz]

this is from an old thread:

"You don't have enough querydefs. Every form and report should be based on a querydef so I would expect a minimum of 23 for your application. Forms and reports should not be based directly on tables and SQL strings as RecordSources contribute to database bloat so they should be avoided."
- P.Hartman, i believe, in response to a question on 'what is the size of an average database...'

I'm just looking for some more info on why it's important to follow this suggestion (which i'm sure is correct). any comments or suggested readings appreciated.

 

[daveUK]

I may be wrong (and I'm sure I'll hear about it if I am ), but querydefs are temporary/dyanmic queries. The only reason I can see for using a query, which is being created everytime it is required is if the criteria for the query could drastically change everytime. i.e. You have a form, which allows users to select several cities and view the staff working within those cities. The criteria could be completely different each time. But if the user could only select one city, or all cities and view the workers within that city, why not create a normal select query?

I've have recently taken on a project, which someone else has been doing in their spare time. They were creating querydefs for everything and also creating temporary tables. Even though there were only about 2500 records, some of the reports, which were based on the queries and/or tables, were taking up to 2 minutes to open when run over a network.

After changing a lot of the system and creating some static queries and some dynamic queries (where required), the reports now open within 2 seconds on the same network.