Comment by phartenfeller
3 months ago
Nicely done. I know this seems crazy at first but in my opinion big SQLs are one of the best ways to store complexity.
The problem being complex is the issue. SQL is a standard, condensed, extremely performant, actually testable, and logical language. Sure, not anybody can instantly maintain it but that would be the same as if it was a lot of lines and functions in Java. The more lines the more risk for bugs.
I also love how deep SQL goes. And that makes totally sense. It powers the world of data since 40+ years so of course people asked for niche features. One of my favorite is the model clause in Oracle with which you can implement multidimensional arrays. A friend implemented Conway's game of life with it in way less lines you expect.
Once upon a time, as an intern, I had the 'fun' task of optimizing the performance of a stored procedure written by someone with a math phd. It was more than 6 pages when printed, and took more than 30 minutes to run (it was used in billing), and had no tests.
Ended up rewriting it in native code, and it run in less than a second. Most of the work was proving it produced same results... and writing and documenting test cases, so next person wouldn't have to go through that.
After that experience, I have generally avoided putting a lot of business logic in SQL...
I definitely see how this happens. SQL is easy to start with but sup hard to master. And it requires good data models.
But if both are fine there is no way that any native code will be faster than a database. No network latency, set operations, index usage, etc. DBs have all the the info to make data access fast.
I wrote a 120 line sql procedure that replaced a 8k line java royalty payment processor.
> in my opinion big SQLs are one of the best ways to store complexity.
Only if, ONLY IF, you have a lot of people that are well versed in SQL. It's very easy to write bad SQL. It's difficult to unravel thousands of lines of bad SQL spread across hundreds of procedures / views / functions. Ask me how I know...
I get this feeling as well, but then again debugging large SQL queries can be very opaque. Things like pl/pgsql help, but then it starts becoming more and more like a normal programming language.
> but in my opinion big SQLs are one of the best ways to store complexity.
It seems crazy at first, and then I continue thinking about it and it still seems crazy for wanting complexity to be in SQL.
Personally, I want anything complex to ideally easily testable, both manually and automatically. SQLs is easy to test manually, but harder to test automatically, especially compared to lines of code in a programming language. At least you can somewhat untangle balls of spaghetti code into less dense, then attack parts of it. How would you deal with a tangled ball of SQL spaghetti?
> The more lines the more risk for bugs.
I don't fully agree with this either, not all lines are equal. One big fat line of a 400 character long SQL query has higher chance of containing issues not easy to glance compared to 400 lines of Java code, and I say this even as a person who despises Java for various reasons.