Hacker News Reader

Comment by roenxi | Hacker News Reader

There is something to be said for having an advanced knowledge of SQL and then carefully staying a very long distance from complicated queries. I can't think of a language where the syntax buckles worse than SQL when things get complicated.

Sometimes it makes sense to roll up the sleeves and do unspeakable things with SQL, but that isn't some sort of win.

View on HN

Having occasionally wrangled with relativley complicated queries and jungles of views ans procedures, I have to ask that what would be a better way to get complicated stuff out from databases?

I can think of two other options, which I personally both dislike:

1. Some fancy graphical ETL monster which takes ages to learn and where the learned skills are more or less untransferable to anywhere else. And which, at the end, makes same things as raw SQL but just in a more opaque way.

2. Build the complexity outside database. Yes, you avoid complex SQL, but you also lose quite a lot. SQL prohibits you from doing quite a many different stupid mistakes with your data. At least 99 times out of 100 databases have better performance making the complicated calculations than your home brewed solution outside the database (Yes, I agree. The exceptions can be notable...) Finally, reusability of the results is way easier if you keep as much calculations as possible in the database.

But I can't say that I love hairy SQL, so if there are better ways, I would be keen to have a look.

Re: point 2. I'm not necessarily advocating it, but one advantage of doing work outside of SQL is testability. Esp. unit tests. It's still relatively difficult to test SQL, for a variety of reason. I'm working with dbt now, which offers some simple validation out of the box (verify such and such is non-null, for example). More complex tests, such as "verify that some rows/columns have some expected values," require writing a query (returning 0 rows on success, 1 or more on failure.) It works, but still rather clumsy because a) requires an actual database (ie, not really unit testing) and b) writing tests this way is pretty tedious and error prone compared to using a typical assertion framework a la python's unittest.

I totally agree the options are all somewhat unpleasant. I've found dbt to be a nice step in the right direction.

Yep, and another is that version control support of databases is still a bit limited. (There are some solutions, at least redgate is doing something for SQL server on this.) I have a workflow where I recreate all views and procedures in the beginning of the query batch from text files and that gives some ways to version control my views, but as you say, it is clumsy.

I've been having a bit of joy using Dataform [] recently to "modularise" SQL code into more manageable views. It brings some of the best practices of software development to the SQL hellscape, making management of complicated SQL a pleasure.

[] https://dataform.co/product

(this is a plug from a happy customer!)

Completely agree with this.

It's a pity that Ingres's QUEL[1] has disappeared; the competition would be good for SQL.

[1] https://en.wikipedia.org/wiki/QUEL_query_languages

I recall seeing some gnarly SQL at British Telecom over 2k lines.

Id agree with you about keeping it as simple as possible

People do dumb things with SQL, but depending on the query/set of columns, 2k is a pretty quick line count to reach. Just dont use select *, have a wide table, do more than a few trivial operations moving data around, and you'll get to 2k pretty quick.

That being said, people do dumb stuff with SQL all the time; The worst query I have ever seen was 11MB, megabytes. The worst query plan I have ever seen generated a plan that was ~130MB. I regularly have people ask questions in #sqlhelp (https://sqlps.io/slack/ SQL Server focused) like "Why can I only have 2100 parameters to a stored procedure?" (dude was super mad that I asked if his design might use some rework.)

I've told SQL learners before

There is no Advanced SQL; there's only "a very large amount of SQL", "exotic SQL", and "SQL no one should have written."

Are you sure it wasn't either:

+ a stored procedure

+ the end-product of an ORM tool?

Reply on news.ycombinator.com