Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Learn SQL, dammit

gun.io

Post reply on HN
Print view View original

61–70 of 118 posts

Re: Learn SQL, dammit

#61

Post by jeffasinger »

rheide wrote: post #20
overshard wrote: post #5

Learn caching, dammit

No. First, learn the internals of your system. Learn SQL. Learn NoSQL. Understand why your software is slow. Then, once you've optimized the worst case away with decent SQL and/or a good object-oriented architecture. Then and only then should you start with caching. Too many developers these days are just brainless monkeys who dump everything in memcache.

A coworker mentioned to me that she came in early every day so she could run a report that took about an hour and a half before the CEO got in.

I looked at the code, nothing looked THAT bad, so I did an EXPLAIN, noticed a missing index, added it. I ran the report in 4 minutes.

Clearly, whoever wrote that report didn't know nearly enough about SQL.

Re: Learn SQL, dammit

#62

Post by papsosouid »

d23 wrote: post #54
silverbax88 wrote: post #43

Earlier quoted context omitted.

I'm still amazed that there are developers who would rather spend more time maintaining/debugging/optimizing ORM code in a mature app that just learning a simple INSERT statement.

Because in my experience, the "maintaining/debugging" step is almost non-existent (isn't that the point of the ORM?), and the "optimizing" step hasn't been an issue for the traffic our sites get.

> the "maintaining/debugging" step is almost non-existent (isn't that the point of the ORM?)

Nobody can agree on the point of ORMs, which is a large part of the reason why there's so much debate over whether to use them and how. I must say, if their point is to eliminate maintenance and debugging, then they are failing miserably at it.

Re: Learn SQL, dammit

#63

Post by papsosouid »

nsxwolf wrote: post #60

Everyone uses an ORM. You use a well known, documented, and supported ORM, or you're writing your own wether you realize it or not. Don't believe me? 1. Do you have objects? 2. Do you have relational data? There's the O and the R. How do you get them together? That's where the M comes in. You use a library that knows how to do the M, or you do your own M with a bunch of getters and setters, for loops and case stateme…

>Everyone uses an ORM

Bullshit. You are making absurd generalizations based on your personal view of how the rest of the world operates.

>1. Do you have objects?

No, I do not. That makes it pretty obvious that I do not use an ORM doesn't it? "Everyone" includes more than just people using OO languages.

Re: Learn SQL, dammit

#64

Post by jamesmiller5 »

For those interested in sharpening their SQL skills I have found these two books to be a great resource.

1. Joe Celko's Trees and Hierarchies in SQL for Smarties 2. Joe Celko's Thinking in Sets: Auxiliary, Temporal, and Virtual Tables in SQL

Re: Learn SQL, dammit

#65

Post by zorbo »

archon wrote: post #21
pjungwir wrote: post #10

For a while I used to ask interview candidates to explain the difference between WHERE and HAVING, to see if they'd ever done anything beyond the basics. I'm still not sure if that's too hard, but people who could answer it did tend to do much better in the rest of the interview as well.

This prompted me to go look it up, since I didn't know. HAVING is WHERE for aggregate functions (SUM, etc). Funny thing is, I've used HAVING a lot in the past, but couldn't have explained the difference succinctly without cheating and looking it up.

I deal with pretty complex queries daily, and I completely forgot what HAVING did exactly. I guess it's because I long ago realized that if I had to use HAVING in a query, I'm doing something wrong. That's almost always a Reporting/BusIntel tool's job, not mine.

Re: Learn SQL, dammit

#66

Post by michaelt »

ScottWhigham wrote: post #19
pjungwir wrote: post #10

For a while I used to ask interview candidates to explain the difference between WHERE and HAVING, to see if they'd ever done anything beyond the basics. I'm still not sure if that's too hard, but people who could answer it did tend to do much better in the rest of the interview as well.

It's sad but this difference, although completely fundamental to SQL, is seen as "advanced" by most devs that I know. They would have no clue as to how to answer...

Consider these two queries (untested so they might have typos):

  select order_number,sum(line_value) as order_value from order_line
  group by order_number
  having order_value > 100;
And:

  select order_number,order_value from 
    ( select order_number,sum(line_value) as order_value from order_line
      group by order_number )
  where order_value > 100;
I'd expect them to have the same explain plan and runtime characteristics. Isn't HAVING just syntactic sugar for the fact queries can be arbitrarily nested? Syntactic sugar isn't fundamental IMHO.

Re: Learn SQL, dammit

#67

Post by daigoba66 »

pjungwir wrote: post #13

A class of query I love that scares off a lot of developers is a correlated sub-query, where the subquery references a value from the outer query. For example, finding all employees with at least one assignment: SELECT * FROM employees e WHERE EXISTS (SELECT 1 FROM assignments a WHERE a.employee_id = e.id) For a while in Oracle this was a lot faster than IN/NOT IN. I'm not sure if that's still the case, or if it's tr…

Depending on the query and the optimizer, the resulting plan for IN/NOT IN might be the same as EXISTS/NOT EXISTS. E.g. a left semi join. I know this is true for MSSQL Server, I can't speak for Oracle.

Re: Learn SQL, dammit

#68

Post by phillc73 »

lucb1e wrote: post #28

So I know SQL... but what's that ORM he assumed I'm familiar with?

I agree. I am not a professional programmer. I'm more a technologist with a passing interest in programming. My desktop is Linux and I can basically hack in Bash, Perl and R to achieve certain simple outcomes. One of the things I use R for is to query a MySQL database for statistical analysis. To do this, I write long hand SQL queries. I didn't know there was another way. What is this ORM of which people speak?

Re: Learn SQL, dammit

#69

Post by pjungwir »

msluyter wrote: post #55
pjungwir wrote: post #13

A class of query I love that scares off a lot of developers is a correlated sub-query, where the subquery references a value from the outer query. For example, finding all employees with at least one assignment: SELECT * FROM employees e WHERE EXISTS (SELECT 1 FROM assignments a WHERE a.employee_id = e.id) For a while in Oracle this was a lot faster than IN/NOT IN. I'm not sure if that's still the case, or if it's tr…

I think IN is equally fast in Oracle, or at least it was when I was working with 10.2. In Postgres, it appears that this does the same thing as IN.

I haven't used Oracle since 8, and the only reference I can find now from those days are a couple AskTom articles [0] [1]. They are quite worth reading, although what they say re performance is not quite what I (mis?)remember.

[0] http://asktom.oracle.com/pls/asktom/f?p=100:11:0::NO::P11_QU...

[1] http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUEST...

Re: Learn SQL, dammit

#70

Post by untog »

silverbax88 wrote: post #43
zzzeek wrote: post #31

Earlier quoted context omitted.

it's really not as much about the querying (though there is a lot of time-saving automation to be had there) as it is about integrating the data in your object model with the tuples being shuttled to/from the database. Like, at what point do you get sick of writing redundant "INSERT INTO " over and over again? Are there really people who still don't see the time-wasting, code-cluttering repetition in that?

I'm still amazed that there are developers who would rather spend more time maintaining/debugging/optimizing ORM code in a mature app that just learning a simple INSERT statement.

who would rather spend more time maintaining/debugging/optimizing ORM code

But that's kind of the point- with a decent ORM library there isn't really much work to do.

    model = MyModel.load(id)
Add a new field to your model? It'll handle it. It might even modify the table for you. With raw SQL you'd have to go and edit your UPDATE and INSERT statements each time. Seems like a lot more maintenance/potential debugging to me.
Post reply on HN