3 ms·

I mean, this sounds narrow, but going off of what you think are 10x multipliers, in terms of getting more bang for your hour/buck: * Fully understanding how SQ

I mean, this sounds narrow, but going off of what you think are 10x multipliers, in terms of getting more bang for your hour/buck: * Fully understanding how SQL prepares queries; optimization strategies * Embracing garbage collection in JITted runtimes, mainly JS for now * Being able to diagnose and replace a squeaky belt in your car * Know what a cat is saying when it meows at you and indicates something in the vicinity of its food / water / the door / a dead mouse. These have all saved me a ton of time.

bluGill 5y ago
> Fully understanding how SQL prepares queries; optimization strategies That might be very useful for me, but my career has taken a different track, and I have never needed that. I almost never touch SQL, and never in a situation where performance matters. However I know that I can look that up if I need it (or more likely find someone else - I expect looking it up will take months)
- noduerme 5y ago
  eh, I only brought it up because SQL is a funny beast. You have to think like the optimizer in some cases. I recently found a report query that was taking >90 seconds to execute. It was looking at something like 2 billion records, but it shouldn't have taken that long. It really tangled my mind. Each of the records had a `start` and `end` timestamp, and were being collated in a join on a separate key and sorted by MAX(`end`). So there's a joint key on the table that is start+end, but not a separate key for each. I figured out the query could be speeded up by about 80% if I added a parameter for `start`, a meaningless WHERE `start`>0 even though I didn't need one. Basically any start would be OK, I just had to have it there because even forcing key on the start/end index didn't kick the optimizer into realizing it needed to search that as a hash space. I don't know. It was a feeling. This is just in the vein of someone asking, "what is jujitsu to you?" But no, don't learn it if you don't need it.
  - warrenm 5y ago
    reminds me of helping a friend at work years ago (the halcyon MySQL 3.23 days) they had a query that ran every 5 minutes, but on non-trivial data sets (network monitoring data for more than ~200 devices on a network), the query would take more than 5 minutes to run ... throwing hardware at the problem didn't help noticeably (double the CPU speed? run time would drop by ~5%) converting their query from OR form to IN() cut runtime by 90% All because of how the SQL engine thought it should run comparisons (MySQL, at the time, at least, used the IN() as functionally equivalent to a jump table, whereas the OR format was treated as a series of it...then...elseif...else statements)
- rawgabbit 5y ago
  Understanding the architecture of your database engine is important. I've had numerous "I told you so" moments in my career when devs didn't believe me, and when ahead and do things like: a) Let's speed up the SQL processing by creating 40K connections and each connection running a stored procedure. Unless your particular SQL instance has enough memory and cpu to support 40K connections, you are performing a denial of service attack on your own server. b) I hate stored procedures and scripting; I am going to write a big ass SQL query that is 200 lines long. Your giant SQL query is too complex for the SQL engine to create an optimize query plan. It will effectively reduce your database engine from using set based logic to one row at time. One time, a developer complained SQL sucks and blow big time blah blah blah. It took me four hours to figure out his ungodly query. I was able to performance tune the query by breaking it into many small steps using temp tables and putting it all into a stored procedure. Execution time improved from 10 minutes to 9 seconds.
brianhorakh 5y ago
Replacing the cat with a obedient well trained dog, may increase happiness. Seriously, well trained dogs are perhaps the biggest life hack a person can do.
- chess_buster 5y ago
  The German-style Dachshund is a more powerful weapon than any fleet or army on earth.

bluGill 5y ago

> Fully understanding how SQL prepares queries; optimization strategies That might be very useful for me, but my career has taken a different track, and I have never needed that. I almost never touch SQL, and never in a situation where performance matters. However I know that I can look that up if I need it (or more likely find someone else - I expect looking it up will take months)

noduerme 5y ago
eh, I only brought it up because SQL is a funny beast. You have to think like the optimizer in some cases. I recently found a report query that was taking >90 seconds to execute. It was looking at something like 2 billion records, but it shouldn't have taken that long. It really tangled my mind. Each of the records had a `start` and `end` timestamp, and were being collated in a join on a separate key and sorted by MAX(`end`). So there's a joint key on the table that is start+end, but not a separate key for each. I figured out the query could be speeded up by about 80% if I added a parameter for `start`, a meaningless WHERE `start`>0 even though I didn't need one. Basically any start would be OK, I just had to have it there because even forcing key on the start/end index didn't kick the optimizer into realizing it needed to search that as a hash space. I don't know. It was a feeling. This is just in the vein of someone asking, "what is jujitsu to you?" But no, don't learn it if you don't need it.
- warrenm 5y ago
  reminds me of helping a friend at work years ago (the halcyon MySQL 3.23 days) they had a query that ran every 5 minutes, but on non-trivial data sets (network monitoring data for more than ~200 devices on a network), the query would take more than 5 minutes to run ... throwing hardware at the problem didn't help noticeably (double the CPU speed? run time would drop by ~5%) converting their query from OR form to IN() cut runtime by 90% All because of how the SQL engine thought it should run comparisons (MySQL, at the time, at least, used the IN() as functionally equivalent to a jump table, whereas the OR format was treated as a series of it...then...elseif...else statements)
rawgabbit 5y ago
Understanding the architecture of your database engine is important. I've had numerous "I told you so" moments in my career when devs didn't believe me, and when ahead and do things like: a) Let's speed up the SQL processing by creating 40K connections and each connection running a stored procedure. Unless your particular SQL instance has enough memory and cpu to support 40K connections, you are performing a denial of service attack on your own server. b) I hate stored procedures and scripting; I am going to write a big ass SQL query that is 200 lines long. Your giant SQL query is too complex for the SQL engine to create an optimize query plan. It will effectively reduce your database engine from using set based logic to one row at time. One time, a developer complained SQL sucks and blow big time blah blah blah. It took me four hours to figure out his ungodly query. I was able to performance tune the query by breaking it into many small steps using temp tables and putting it all into a stored procedure. Execution time improved from 10 minutes to 9 seconds.

brianhorakh 5y ago

Replacing the cat with a obedient well trained dog, may increase happiness. Seriously, well trained dogs are perhaps the biggest life hack a person can do.

chess_buster 5y ago
The German-style Dachshund is a more powerful weapon than any fleet or army on earth.