4 ms·
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
by roenxi 7y ago
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.
- C1sc0cat 7y agoI recall seeing some gnarly SQL at British Telecom over 2k lines. Id agree with you about keeping it as simple as possible
- hobs 7y agoPeople 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/ 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.)
- kthejoker2 7y agoI'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."
- Pamar 7y agoAre you sure it wasn't either: + a stored procedure or + the end-product of an ORM tool?
- sails 7y agoI'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 https://dataform.co/product (this is a plug from a happy customer!)
- beefield 7y agoHaving 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.
- msluyter 7y agoRe: 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.
- beefield 7y agoYep, 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.
- tuatoru 7y agoCompletely 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 https://en.wikipedia.org/wiki/QUEL_query_languages