What to do if you need to execute a large number of complex SQL queries of the same type


Let's fantasize:

We have some hypothetical database from which it is periodically necessary to obtain data on, say, users.

The selection of users must be carried out by a certain set of filters, in general – boredom.

Let's add a set of parameters for each user, which will be somewhat labor-intensive to extract, for example, we will attach information to each user about how many unique visits to the site by other users have been recorded since the last visit by the current user. Or something else like that, i.e. information that requires additional gestures and JOINs.

Let's accept it as true that we have a number of places in the project where it becomes necessary to get information about users from the database, and in each place we have different (strongly overlapping) combinations of the required returned fields.

How to act in such a situation? I see the following approaches:

  1. Write one query that will request all the information, and use it always . Not the smartest approach, it eats up a large amount of resources even when you can get by with a little blood. Perhaps the approach is viable with deep caching of results

  2. Write on demand for each specific task . The most productive, but the code begins to abound with almost identical methods with similar names, and swells a lot. Perhaps the approach is viable with a consistently small number of options for the required combinations of returned fields.

  3. Automatically generate a request by a certain method, directly passing to this method a list of fields that need to be received in response . We get the most productive queries for the full range of variations of the returned fields, but we can get (and probably get) a poorly diagnosed hefty, complex method, which, after writing, is strictly not recommended to be touched.

  4. Create a set of methods with the simplest queries and juggle them in the main language used (in the subject – php). Those. first, all users were selected by filters, then they fastened the necessary data to each … it smacks of an indecently large number of requests and performance death with large numbers of rows. There will also be problems in cases where the filters on which the selection is made relate to additional returned fields

In general, I would like to see your opinion on the question. It is possible to mention some pitfalls that I did not see.

UPD: Thoughts on the option of dynamic query generation are especially interesting. Perhaps there are some materials in which this issue was covered? TC has always been extremely negative about the automatic generation of any executable code (both SQL and JS), but in the context of this approach, it looks quite decent if everything is done carefully. What do you think?


My opinion is the following. The choice of one of the listed options is just one of those decisions for which the developer is needed in the project. He is partly involved in solving the problem for this, so that he, knowing the specific features of the project, can choose the best option based on his experience.

Scroll to Top