I approach you all humbly as one who is NOT a DBA, and I'm sure that my question is fraught with conceptual shortcomings and "it depends on" land mines. I'm also pretty sure that all of you who choose to answer are going to want a lot more in the way of specifics than I can currently deliver.
That said, I'm curious about the following scenario in general:
- Say that I have two non-trivial queries.
- Query 1 requires 2 minutes to complete on average.
- Query 2 requires 5 minutes to complete on average.
If I run them serially, one right after the other, I'm expecting it will require 7 minutes to complete on average. Is this reasonable?
More than that, however, what if I run the two queries concurrently? Two separate connections at the same time.
- Under what conditions would I expect to see a speedup? (Total time < 7 minutes)
- Under what conditions would I expect to see a slowdown? (Total time > 7 minutes)
Now, if I had 1,000 non-trivial queries running concurrently, I have a hunch that it would result in an overall slowdown. In that case, where would the bottleneck likely be? Processor? RAM? Drives?
Again, I know it's probably impossible to answer the question precisely without knowing specifics (which I don't have.) I'm looking for some general guidelines to think about when asking the following questions:
- Under what circumstances do concurrent queries result in an overall speedup?
- Under what circumstances do concurrent queries result in an overall slowdown?