Is 80 queries too many?

I have large database containing thousands of records which I am required to query on a weekly basis. I then have to create summaries of this data using Access Reports. When I first created the database (which I did very recently), I built several types of queries (about 80) and then created subreports off each, and then put these sub reports in one giant main report.

I have never used a database before and something tells me that maybe I shouldn't have 80 different queries, populating 80 different subreports. I don't know, maybe it's okay. But that's my question, is there any reason why I should go back and redo some of these queries and consolidate them into maybe 20 or 30 (which would require me to redo the sub reports and the main reports), or is it perfectly fine to have this many queries saved in my database.

Keep in mind, each query, though containing only 2 or 3 functions each, has a very specific task that I cannot get rid of completely, I would only be able to add these functions into a larger query by combining several types of queries. Also, like I said, I have to use these queries on a weekly basis, so I don't really want to build them on the fly like some people choose to do.

Anyways,开发者_运维百科 is there some unforeseeable problem that I am missing with having this many queries, or is this normal?

---

If it performs to your requirements (i.e. it's "fast enough") and doesn't hinder others (i.e. it doesn't slow everyone else down), then it should be fine.

I would just make sure that all of the queries run well individually against your target data set. It may run great against 50 rows in development, but you have 1M rows in production.

But if it runs fast enough for you on your production DB, then be done with it and go solve another problem.

If you want to take a learning experience from this, then apply it on a future project rather than repaving a road you've just laid.

---

Without knowing all the details, it's impossible to answer whether you should have 20 or 80 or 200 queries. However, having more queries doesn't hurt anything, and as a general database matter it's better for your queries to be very focused on what they are specifically needed for, as opposed to having a smaller number of "general purpose" queries that return more data than is really necessary.

If you have fewer, less focused queries, and you need to change a report/query to display additional data, you will likely have to go back and re-test all the reports that rely on the same query for their data to make sure they weren't broken by the change.

---

That's one heck of a report. Having 80 queries in a database isn't that bad. Having 80 queries for one report...

If the performance is not an issue, then you might not have an issue.

Do some of these queries return basically the same column information but with different results? If so, look into refactoring your queries to use parameters.

---

Why do the queries need to be saved? You might very well have a lot of report/s subreports, but you can just save the SQL SELECTs as the Recordsource properties of the reports in question, with no need to save the SQL out as a saved query.

I try to minimize the number of saved queries, because the more you have, the more difficult it is to keep track of them. And then you end up needing to come up with some kind of naming conventions so you can tell what's what, and then eventually you end up with a mess.

Also, re-use is not a reason to do this. In general, it's good to re-use objects, but the problem with saved QueryDefs is there's no easy way to test the dependencies (though if you turn on Name AutoCorrect you can see where a query is used). Thus, it's really easy if you're re-using queries to make a change for one context that breaks the query in the other context(s) in which it's used. So, in general, I don't re-use saved queries except when the dataset involved really is going to be identical 100% of the time (e.g., a form and a report that present exactly the same data).

So, basically, I would say that 80 saved queries is no big deal in a decent-sized app, but from what you've described, I see no reason why the SQL should be stored in saved queries at all. In that case, ZERO saved queries would be the appropriate number.

See my recent post on the question on Query Design Practices in SQL.