![[personal profile]](https://www.dreamwidth.org/img/silk/identity/user.png){kind=small}
Well, I got my developer's SQL beaten into submission. The bottle neck was not where I expected. Nor did I believe anyone would actually want those three outer joins, but she did; the nulls mean something to the report. Final result, the 20 minute query ( yes 20 minutes, those tables were thrashing like clubbed baby seals ), cut to about five seconds.
SQL is interesting to work with. It's not really programming; more like function construction. You start out with a pool of data points and always end up with a single result set. The vocabulary of SQL is sparse compared to most code, so the challenge is purely in the logic. You have the data, you have the result, you get to make the guts of the black box in the middle. And, unlike most programming, there are a finite number of solutions.
The strange thing is, the query is chopped up by the database using this beast called a parser that breaks down the request into component parts that all the little mice in the machine understand. Theoretically, given the question, the database should be able to figure out the optimal way to spit out the answer. But, it doesn't.
SQL has been around for over 30 years ( well, Codd described RDBMS architecture in 1970, but it would take about a decade for the idea to catch on ). It's basic syntax is probably less than twenty words. You'd think, after all this time, that a computer based optimizer would be able to automatically pick the best execution plan. But, it can't.
Ironically, almost counter intuitively, computers write terrible code. There are books and classes and millions of dollars spent training people how to tune a SQL statement; because computers simply aren't good at. And SQL is simple. Other languages can be seriously complex and every code generator I've ever seen doesn't do the best job.
Sometimes, computer written code does the worst job. On a more scifi tack, AI hasn't moved much for a few decades either. While the dream of a smart machine is intriguing, it's kind of reassuring to know humans will be smartest critter on the planet for the foreseeable future.
SQL is interesting to work with. It's not really programming; more like function construction. You start out with a pool of data points and always end up with a single result set. The vocabulary of SQL is sparse compared to most code, so the challenge is purely in the logic. You have the data, you have the result, you get to make the guts of the black box in the middle. And, unlike most programming, there are a finite number of solutions.
The strange thing is, the query is chopped up by the database using this beast called a parser that breaks down the request into component parts that all the little mice in the machine understand. Theoretically, given the question, the database should be able to figure out the optimal way to spit out the answer. But, it doesn't.
SQL has been around for over 30 years ( well, Codd described RDBMS architecture in 1970, but it would take about a decade for the idea to catch on ). It's basic syntax is probably less than twenty words. You'd think, after all this time, that a computer based optimizer would be able to automatically pick the best execution plan. But, it can't.
Ironically, almost counter intuitively, computers write terrible code. There are books and classes and millions of dollars spent training people how to tune a SQL statement; because computers simply aren't good at. And SQL is simple. Other languages can be seriously complex and every code generator I've ever seen doesn't do the best job.
Sometimes, computer written code does the worst job. On a more scifi tack, AI hasn't moved much for a few decades either. While the dream of a smart machine is intriguing, it's kind of reassuring to know humans will be smartest critter on the planet for the foreseeable future.