MySQL Running multiple queries to keep code DRY, performance concerns?

Question

To keep my code DRY I currently have multiple separate queries that have their own responsibilities. This enables me to perform complex actions using a combination of those queries instead of writing larger queries customized just for those actions.

Similar to having reusable/dynamic methods in your code instead of writing one method for each specific use-case. It makes development easier, but potentially at the cost of some performance.

Note: I am not keeping it DRY just for the sake of DRY. I'm trying to keep things dynamic and easy to read & understand.

Simplified Example:

I need to get the permissions groups for an employee and a department. I then need to get the permission associations for those permission groups. I also need to get the granular permissions assigned to the employee or department (permissions not associated with a permissions group).

I could write 2 queries to get the permission groups and their associated permissions with joins, and 2 more queries to get the permissions associated with the employees and departments directly. These would not be re-usable for other purposes.

Or I could write 1 query to get the permission groups for employees or departments, 1 query that gets the permissions associated with any permission group, and 1 query that can get the permissions associated with the employees/departments themselves.

• 4 Specific Non-Reusable Queries, 4 executions, 4 method calls from api
• VS
• 3 Reusable/Dynamic Queries, 6 executions, 1 method call from api

---

This is a simplified example. In practice I would have 15+ case-specific queries and 15 executions. Vs 5 dynamic queries and ~40 executions.

Is there performance concerns when going about it this way? The code itself seems much cleaner and easier to work with, but I don't want to sacrifice a significant amount of DB performance for a cleaner dev-API.

Answer 1

You should try to use the least amount of queries possible per page request. Even if they are fast, imagine you have 5 query executions on the page vs 40. If 1,000 people hit that page, that's 5,000 queries requested vs 40,000. Yes, you can turn caching on, but I wouldn't rely on it. Even if it is on, your performance will still be better using less queries. If you have to retrieve the results for 5 cached queries, that's obviously faster than retrieving the results for 40. As long as your indexes are defined correctly and up to date, if you have to make a few joins, your queries should still run fast. I think no matter which answers you get here, you should still trial and error it and log the results. If we are talking miliseconds difference here, and your not getting thousands of page requests every minute, then does it really matter?

Answer 2

A few dozen simple queries is not too much for a web page.

Try both ways. Try a third approach to the queries. Let this be a learning exercise. There is not necessarily a "right" way to design/code what you described.

DRY is a good principle, but it can get too complex. So, look for a 'proper balance' between the various approaches.

A common mantra is "eschew premature optimization".