4 ms·

We have a general rule on our team that complex SQL is a code smell. In our project complex queries are usually an indication of a poor design. Anything SQL th

We have a general rule on our team that complex SQL is a code smell. In our project complex queries are usually an indication of a poor design. Anything SQL that can be made simpler via dynamic generation (which is safe as long as you use proper parameters for user inputs) is favored over creating logical branches in queries. Anything that can be processed further quickly in memory in the app (mapping operations, string ops, ordering/filtering predictably small data sets, etc.) we tend to offload from SQL into something more suitable. And we tend to solve a class of problem in our data layer and reuse those generalized patterns heavily. This makes our codebase predictable even when dealing with unfamiliar subject matter. Of course there are always places where some complex query is necessary (especially when building reports), but if it’s status quo then you’re doing something wrong—-it’s only a matter of time until you end up with a performance nightmare on your hands.

karmakaze 5y ago
> complex queries are usually an indication of a poor design. Can you give an illustrative example of one. I suspect that framing it this way biases designs away from 'poor ones that use complex queries' into one that foregoes other good aspects such as normalization. Sometimes the best design uses a complex query for something other than a report. Design is not something that should be done by application of dogma and avoiding smells.
- twodave 5y ago
  No, but by treating a SQL server as a way of storing and retrieving data efficiently FIRST then the times when a complex query is actually necessary tend to stand out better. In reality most tables and queries start out simple enough, and poor schema choices are usually accompanied by poor architectural choices. It can be painful to come up with a decent migration scheme when the business needs change, especially if there’s fear/pressure involved, but often that’s going to be better than trying to keep the data layer the same/similar and shoehorning in data to represent new scenarios. This is what leads to a fragmented design IMO and allows the schema to diverge from the actual goal of efficient data storage/retrieval.
CornCobs 5y ago
I think the author makes a valid point about this though - that it is precisely because of the limitations of SQL that we think this way. If SQL was a much more expressive data processing language we may not even require an app layer. I do agree with this - after all SQL is supposed to be the data layer - why should we think that data processing shouldn't happen there?

karmakaze 5y ago

> complex queries are usually an indication of a poor design. Can you give an illustrative example of one. I suspect that framing it this way biases designs away from 'poor ones that use complex queries' into one that foregoes other good aspects such as normalization. Sometimes the best design uses a complex query for something other than a report. Design is not something that should be done by application of dogma and avoiding smells.

twodave 5y ago
No, but by treating a SQL server as a way of storing and retrieving data efficiently FIRST then the times when a complex query is actually necessary tend to stand out better. In reality most tables and queries start out simple enough, and poor schema choices are usually accompanied by poor architectural choices. It can be painful to come up with a decent migration scheme when the business needs change, especially if there’s fear/pressure involved, but often that’s going to be better than trying to keep the data layer the same/similar and shoehorning in data to represent new scenarios. This is what leads to a fragmented design IMO and allows the schema to diverge from the actual goal of efficient data storage/retrieval.

CornCobs 5y ago

I think the author makes a valid point about this though - that it is precisely because of the limitations of SQL that we think this way. If SQL was a much more expressive data processing language we may not even require an app layer. I do agree with this - after all SQL is supposed to be the data layer - why should we think that data processing shouldn't happen there?