3 ms·

SQL optimization is a bit overrated, IMO. I code in SQL and R daily, and my experience is that most SQL scripts, it doesn't matter if it takes 6 seconds or 60 s

SQL optimization is a bit overrated, IMO. I code in SQL and R daily, and my experience is that most SQL scripts, it doesn't matter if it takes 6 seconds or 60 seconds. What matters is deeply understanding what your row is, how to join other data onto those rows, and how to effectively communicate/analyze the data after you get your final dataset. If you can't do the last step, it doesn't matter how fast your code runs, unless you're specifically tasked with speeding up legacy code or something like that.

perl4ever 7y ago
I think I have a good understanding of SQL optimization, and it came from an environment of writing endless adhoc SQL for database updates and reports, where, no, it didn't matter in the least if your query took 6 seconds or 60 seconds. But the challenge was to write and run a query in a matter of minutes and not hours, or days, or weeks, and not to spend time on false starts or elaborate queries that left you uncertain if your results were correct. When I first started, I would write something, and start it running, and wait. And then after five minutes, you're thinking, well, I have to finish this within half an hour, is it going to take 10 minutes or weeks, in which case I need to try something different? But then, if I cancel it, was it about to finish, in which case I wasted five minutes, and if I do that three times, it's going to be late? I've seen queries where the time estimate goes in reverse and it starts out predicting 2 minutes to finish and 10 minutes later it's saying days or weeks. And the results were needed ten minutes ago. Optimization becomes important when you're talking about several orders of magnitude, and opportunities are all around in a business environment. I was given a report script in T-SQL once that did the job it was supposed to adequately on a few hundred (maybe thousand) documents but my manager wanted to run it on all the things - millions of documents on hundreds of databases. It turned out to be very doable, but required changing a largely procedural script that ran SQL in a loop to a single query that was something like 10,000 times faster. Worrying about 6ms -> 6s -> 6min isn't the environment I'm used to. It was always about human scale time - will it be done in 15 minutes, will it be done by the court deadline tomorrow, will it be done this week, or this month. What I learned from an inhumanly talented analyst was that the Oracle optimizer is the enemy of performance and predictability, and the way to get reliable, fast results is to break down things into simple queries and temp tables and ignore gurus like Tom Kyte that say all those fancy database features are there for a reason and you should write beautiful multipage masses of SQL. (someone like Kyte would also say if the optimizer is fighting you the database is configured wrong, which might be true but is utterly unhelpful in what I consider the real world where the admins exist to tell you "no" or ignore you)
- pmiller2 7y ago
  Re: human timescales, I have also run into this in my career. I wrote calibration and test software a few years ago, and it didn’t matter if a test took 1 minute, 3 minutes, or 10 minutes, because those were all “push the button, sit there and wait” scale. 15-30 minutes is “push the button and take a short break.” 60-90 minutes is “push the button, go to lunch,” and so on. We had tests that ran for up to 24 hours, but, when optimizing, it didn’t matter if I could make it run faster, unless I could get it down below 8 hours, because that was the next fastest time scale.
  - perl4ever 7y ago
    Nobody cared if I made something that took 1 second run in 0.1 seconds, but it was a great feeling to make something that would take a month run in an hour, because that made something "impossible" possible in most contexts.

perl4ever 7y ago

I think I have a good understanding of SQL optimization, and it came from an environment of writing endless adhoc SQL for database updates and reports, where, no, it didn't matter in the least if your query took 6 seconds or 60 seconds. But the challenge was to write and run a query in a matter of minutes and not hours, or days, or weeks, and not to spend time on false starts or elaborate queries that left you uncertain if your results were correct. When I first started, I would write something, and start it running, and wait. And then after five minutes, you're thinking, well, I have to finish this within half an hour, is it going to take 10 minutes or weeks, in which case I need to try something different? But then, if I cancel it, was it about to finish, in which case I wasted five minutes, and if I do that three times, it's going to be late? I've seen queries where the time estimate goes in reverse and it starts out predicting 2 minutes to finish and 10 minutes later it's saying days or weeks. And the results were needed ten minutes ago. Optimization becomes important when you're talking about several orders of magnitude, and opportunities are all around in a business environment. I was given a report script in T-SQL once that did the job it was supposed to adequately on a few hundred (maybe thousand) documents but my manager wanted to run it on all the things - millions of documents on hundreds of databases. It turned out to be very doable, but required changing a largely procedural script that ran SQL in a loop to a single query that was something like 10,000 times faster. Worrying about 6ms -> 6s -> 6min isn't the environment I'm used to. It was always about human scale time - will it be done in 15 minutes, will it be done by the court deadline tomorrow, will it be done this week, or this month. What I learned from an inhumanly talented analyst was that the Oracle optimizer is the enemy of performance and predictability, and the way to get reliable, fast results is to break down things into simple queries and temp tables and ignore gurus like Tom Kyte that say all those fancy database features are there for a reason and you should write beautiful multipage masses of SQL. (someone like Kyte would also say if the optimizer is fighting you the database is configured wrong, which might be true but is utterly unhelpful in what I consider the real world where the admins exist to tell you "no" or ignore you)

pmiller2 7y ago
Re: human timescales, I have also run into this in my career. I wrote calibration and test software a few years ago, and it didn’t matter if a test took 1 minute, 3 minutes, or 10 minutes, because those were all “push the button, sit there and wait” scale. 15-30 minutes is “push the button and take a short break.” 60-90 minutes is “push the button, go to lunch,” and so on. We had tests that ran for up to 24 hours, but, when optimizing, it didn’t matter if I could make it run faster, unless I could get it down below 8 hours, because that was the next fastest time scale.
- perl4ever 7y ago
  Nobody cared if I made something that took 1 second run in 0.1 seconds, but it was a great feeling to make something that would take a month run in an hour, because that made something "impossible" possible in most contexts.