How many is too many?

[dsmaj]

Hey folks...

The database I'm currently working on is not overly large in terms of data (less than 2000 rows in total accross all tables), however it's fairly comprehensive in terms of its interface. I currently have 21 tables--all linked. Two of these tables are the MAIN data items, and the other 19 tables contain all of the supporting information (mostly in one to many relationships). The data entry screens need to make it very easy for the users to select from the existing supporting information (or add new information), so I'm populating various controls on the forms with this supporting information via queries on form load. My question is this: How many queries is too many? To elaborate, for a table structure of this size, at what point does the number of stored queries in my database become a burden? I've started to run into many situations where I have two options for a given query situation for control population... Option A) Store three or four very similar queries that each have a minor structural difference from each other, and call the apropriate one for the given situation when populating my control, or Option B) Build the SQL code at run time based on the current situation and populate the control that way.

I feel like this isn't coming accross the way I intend it too, so I'll illustrate with my current example.
I'm building a Search Form for searching Initiatives (one of the MAIN data items in my database)...the Search Form can be called from 6 unique locations within my database, and each of those locations requires that one of the controls on the Search Form be populated with supporting data based on queries that have STRUCTURAL differences for each location. So, would it be wise to save 6 separate queries in Access, and call each of them only when the Search Form is called on by one of the unique locations that requires it? Or should I just build the SQL dynamically at run time and populate the control that way?

I realize as well that the answer to this question probably depends largely on the size of the table that is populating the control...how does this come into play? Are saved queries better for really big tables that are called only sometimes? Or for small tables that are called frequently? .....

To sum up my question, how many saved queries is too many? Is it ridiculous to start saving queries for all kinds of control-populating routines that all have very slight SQL structural differences?

Thanks in advance for any replies...
Sam

 

[Pat Hartman]

Stored querydefs are the best way to go whenever your SQL is static whether it takes parametes or not. Establish a good naming scheme so that you can easily identify a queyr so that you can reuse it if necessary. If your SQL is dynamic (some element changes when it is run), you don't have any option. You MUST build the SQL with code.

I can't tell from your description if you are creating 1 query for each location where the only difference is the value of location or if there really is something dynamic about the query. If your situation is the former, all you need is ONE querydef that takes a parameter.

 

[dsmaj]

Hey Pat, sorry for the disjointed nature of my description--I knew it sounded a little off. Anyhow I am in fact creating one query right now, but I am modifying its SQL code dynamically (thereby eliminating any advantages of a querydef) because there are definitely structural differences in the SQL code that I need to use. My question however was geared more generally, and not specifically related to this particular situation. The first part of your post hits on where I was going with my question...

Stored querydefs are the best way to go whenever your SQL is static whether it takes parametes or not. Establish a good naming scheme so that you can easily identify a queyr so that you can reuse it if necessary. If your SQL is dynamic (some element changes when it is run), you don't have any option. You MUST build the SQL with code.

My situation in fact DOES call for dynamic, structural SQL changes (I therefore definitely can not use one stored QueryDef). What I'm wondering though, is whether it is worth it to create three, four, five (or however many I need...) stored QueryDef's in order to handle each of the structurally different SQL situations that I have, rather than simply building my SQL query at run-time. If I were to do this, it is likely that I would end up with upwards of 40 or so stored QueryDef's just to handle the data population of controls on my forms. Does it become a burden on my application to have that many stored queries, or is it justified by the speed increases of using a QueryDef?

I hope this makes sense....

Sam.

 

[Pat Hartman]

I can't picture what you are trying to do. It "feels" abnormal. Why would you need multiple queries to populate your form? In general, I would probably build the SQL in code rather than store 40 queries that do the same thing.

 

[dsmaj]

I can't picture what you are trying to do. It "feels" abnormal. Why would you need multiple queries to populate your form? In general, I would probably build the SQL in code rather than store 40 queries that do the same thing.

I need multiple queries because it's not just one form that I'm populating with data. I've got about seven or eight different forms that have up to 10 controls which need populating--and these forms will be opened and closed on a fairly regular basis, making speed and efficiency and issue of importance. As an example to try and illustrate, I have a multi-purpose frm_Chooser form which simply has two listboxes (lstSource and lstDestination) which the user can use to move entries between them. The form is passed the name of the parent form that called it, and based on that arguement, a query is built to populate the two listboxes.

For example: I could have frmMain, frmInitiative, frmStrategy, frmJurisdiction, or frmSponsorOrganization call frmChooser, and frmChooser will then import the apropriate data into its two listboxes based on which form called it. Since the fieldnames and FROM table (structural elements of an sql query, no?) will be different depending on which form called frmChooser, I can not use a single QueryDef to populate these two controls. In this case, is it worth it to store 5 separate QueryDef that are specific to the structural needs of frmMain, frmInitiative, frmStrategy, frmJurisdiction and frmSponsorOrganization, or am I better to simply build my SQL query dynamically at run time and suffer the speed/efficiency consequences?

I am currently building my sql dynamically in the example above, however I'm thinking that due to the frequency with which frmChooser will be called, I may be better to store several QueryDef's for use depending on what the calling form was. I have several other similar situations in my database where a form is called frequently, and loads data into its controls from different tables depending on the circumstances under which it was called. If I built QueryDef's for all of the structurally-unique situations, I would have rather a lot of stored queries, however, I would be gaining the speed benefit of using QueryDef's. I am simply wondering if doing this is a good idea or not?

As a note, I think much of the confusion might have resulted from my neglect to mention that the forms load data from different tables with different field names depending on the circumstances under which they were called. Sorry for that oversight

Sam.

 

[Pat Hartman]

I'm going to ask another silly question. If the input is different (the source data table/query) and the output is different (the controls on the form) and the process is different (how you get from the input to the output), WHY would you be using the same form? There must be some point at which you would elect to build an additional form.

 

[dsmaj]

Good question...the forms that I'm dealing with are in a sense "multi-purpose." At the same time though, they are fairly complex, and backed by a LOT of code. Some of the tables I'm dealing with are very similar in structure (Example: tbl_Strategy******* and tbl_Initiative*******--about 8 tables in total--all have the same structure of (*******ID, *******Text, *******StartDate, *******EndDate). Being that they are so similar, and need to be displayed in exactly the same way, I would like to just use the same forms/code to deal with them. I suppose I could have different forms for all of them, however for a little bit of extra work, I've just made the form multi-purpose. Your point brings up an interesting issue however--if I were to have a different form for all of these tables, that would necessarily imply that I would have a separate saved query for each of them as well, correct? So then would it be just as acceptable to have only one form, but 8 different supporting queries for this particular situation? In my opinion, 8 saved queries isn't a big deal, but if I apply this methodology to my entire project, I would have 40+ saved queries many of which would be redundant but for small structural differences here and there. This brings me back to the topic of this thread--How many queries is too many? In otherwords, is it unreasonable for me to save 40+ queries (in addition to the other queries in my project) for supporting my table-maintenance forms?

Edit: Oh yeah, and the output IS different, but the controls on the form are not. It's the same controls, and in fact, they're being populated identically from a programatic standpoint (via a recordset)...the only thing that changes is how the recordset is populated when the form loads (i.e. what table the data is being pulled from).

Thanks for your continued input, Pat.

Sam.

 

[KKilfoil]

You have otherwise identical tables that differ only because some describe a particular strategy/initiative rather than another?

Have you considered condensing these tables down to one table and adding a 'StrategyType' field? You may also wind up with a one-to-many lookup table to describe the type.

Then your queries are only varying a parameter value (StrategyType) rather than changing from one table to another, so the querydef remains constant.

Appropriate filtering of the forms would make this change invisible from the users' POV.

 

[dsmaj]

You have otherwise identical tables that differ only because some describe a particular strategy/initiative rather than another?

Have you considered condensing these tables down to one table and adding a 'StrategyType' field? You may also wind up with a one-to-many lookup table to describe the type.

Then your queries are only varying a parameter value (StrategyType) rather than changing from one table to another, so the querydef remains constant.

Appropriate filtering of the forms would make this change invisible from the users' POV.

A very valid suggestion, and I did consider doing just that. However some of the tables, while almost identical in structure, serve very different purposes (further than simply having different field names) from an overal design perspective. Dealing with these tables with common code is convenient, however combining them into a common table with an extra field would unacceptably confuse the relational structure of my database.

Thanks for the suggestion nonetheless...

Sam.