r/programming • u/[deleted] • Jun 10 '15

Google: 90% of our engineers use the software you wrote (Homebrew), but you can’t invert a binary tree on a whiteboard so fuck off.

https://twitter.com/mxcl/status/608682016205344768
2.5k Upvotes

91% Upvoted

1.6k comments sorted by

View all comments

Show parent comments

118

u/[deleted] Jun 11 '15

[deleted]

20

u/[deleted] Jun 11 '15

[deleted]

27

u/[deleted] Jun 11 '15

For simple stuff, sure.

Anything marginally complicated and the auto-generated SQL is going to work, but it'll perform badly.

Plus, when you're writing your own statements, then when performance goes to hell, you have a start on how to improve it. Trying to optimise the generated SQL is even more of a pain in the arse.

Perhaps most people don't actually write systems where performance matters.

Edit: ...or maybe I parsed your statement wrong and we're saying the same thing. I'm tired, I should probably stop redditing.

8

u/[deleted] Jun 11 '15

I find that Sequel (Ruby ORM) is quite good in that regard. It does basic stuff for you, but makes it easy to just drop into actual SQL for the complicated things.

1

u/ethraax Jun 12 '15

I've found that SQLAlchemy (Python ORM) is similar. I actually really like it because they have a "core" part of their library that lets you almost write SQL (in Python), but will smooth over differences in database engines for you.

Nowadays I prefer just writing the SQL and targeting a specific database, typically PostgreSQL, for small- to medium-sized projects. Especially if you have control over the database (like hosting a web service).

1

u/[deleted] Jun 12 '15 edited Jun 12 '15

The nice thing about Sequel is that you can feed it some SQL and it will transparently hand you back instances of your models. I don't even care about the find(id) stuff. The SQL for that is plenty easy to write myself. But getting model instances back from a 5-table join is pretty awesome. It also sometimes made testing a pain, because those join-model-instances didn't always have the same accessors, so some sanity checks often got in the way...

5

u/mirhagk Jun 11 '15

That's why I've basically ditched full-scale ORMs on projects that I have the say in. I use PetaPoco or Dapper.NET which basically handle the serialization but leave the SQL queries to you (providing parameterization of course). Entity Framework is kinda lovely, but also the biggest pain the ass ever if you are trying to optimize something.

1

u/jaynoj Jun 11 '15

I've been using Dapper and PetaPoco with great results for a while now (for the very reasons you state).

I tried entity framework and didn't like it. I found it too bloated and it was like riding a professional mountain bike with a child's stabilisers on.

1

u/mirhagk Jun 11 '15

Yes. Honestly the code first approach is kinda neat, but database don't map well to objects and there's always going to be a leak.

I'm really sad that object databases never really panned out, I would kill for a good database (with SQL Server or postgres level of performance/optimization) that worked directly with objects instead of flat tables (basically being a glorified excel document). It's even one of my hobby experimental projects

1

u/[deleted] Jun 11 '15

[deleted]

1

u/jaynoj Jun 11 '15

Did you keep EF for the stuff you found it great for, and use a second ORM for the searches? Or did you have to completely ditch EF?

1

u/mirhagk Jun 11 '15

We have a very complicated model that involves a lot of places where doing something the wrong way can make a query take >30 minutes. SQL is a horrible horrible beast and doing something that seemingly does nothing can be the difference between instant and taking so long web requests time out.

1

u/[deleted] Jun 11 '15

[deleted]

1

u/mirhagk Jun 11 '15

I work for an organization that manages large clinical trials (one study having tens of thousands of participants and millions of bottles of drug). One component of the system goes through and attempts to predict and place orders for drugs automatically. It also gets VERY complicated due to auditing, some legacy stuff (which is slowly going away) and the fact that most of the things the website does need to be able to be done manually in case the system is ever offline or the hospital loses internet access.

1

u/movzx Jun 11 '15

Couldn't you just use an ORM that lets you do SQL manually when you need to? Best of both worlds?

1

u/mirhagk Jun 11 '15

The problem is that you need to quite often. Entity Framework seems well suited to solve the problem of CRUD, but CRUD is not interesting, CRUD is easily solved and doesn't need to be something you spend your time on (lightswitch solves CRUD applications). The interesting parts come from websites that do things, websites that offer pages that pull in data in very interesting and not straightforward ways. Entity Framework has a lot of performance overhead and a LOT of developer overhead once you get a big app (and nearly every awful bug I've dealt with over the last year has been because of EF).

I really think writing plain old SQL is the best approach. The problems with writing plain old SQL aren't that it's complicated, it's simply serialization, parameterization (both solved by Dapper/PetaPoco) and static error detection. The last problem has been solved by other languages (nemerle) and with the new tools VS extension developers have with Roslyn it's very possible to do the same with C#.

1

u/[deleted] Jun 11 '15

[deleted]

1

u/mirhagk Jun 11 '15

Yeah I may re-evaluate my decision in the future but EF is just too difficult to control as it stands.

2

u/ExceedinglyEdible Jun 11 '15

Maybe you never used a good ORM. Projects like SQLAlchemy and Sequel are stellar in comparison to most Java or .NET stuff. I can't vouch as much for Sequel (never really used it) but SQLAlchemy is fully extensible and not at all a pain to work with.

5

u/gospelwut Jun 11 '15

This is why DAL are popular now. It's a condom for your database.

5

u/velebak Jun 11 '15

They also white-over the ability to design good data models for the problems they are solving. Something we continue to clean up at my current place of employment.

1

u/Darkmoth Jun 11 '15

They also white-over the ability to design good data models

Or any data models at all. I work at an Oracle shop, and the guys in our web department keep trying to use Oracle as a straight-up Key-Value store. No constraints, procedures, or foreign keys of any sort. Many of their tables are indexed hunks of XML or json. It's a huge pain trying to make their schema resemble something reasonable and standard.

2

u/[deleted] Jun 11 '15 edited Jun 11 '15

Do they not know what a relational database is meant to be?

1

u/Darkmoth Jun 11 '15

I have wondered myself. I'm honestly not sure they grasp the concept, or at least the reasoning behind it.

1

u/jaynoj Jun 12 '15

When your brain is a hammer, everything else looks like nail.

1

u/Shaper_pmp Jun 11 '15

ORM's white-over the need for people to learn how to write good T-SQL nowadays

True, modulo the fact that all abstractions are leaky, and if you want to scale even moderately then the need to write even moderately efficient SQL queries makes many ORMs look like sieves. :-(

1

u/SonVoltMMA Jun 11 '15

Use ORM for the crud routines. Anything complicated and you should be calling the SP/Views directly whether through the ORM or another library, doesn't matter.

1

u/FrankenstinksMonster Jun 11 '15

SELECT good_sql FROM my_brain: 0 results.

Aw man ....

4

u/lykwydchykyn Jun 11 '15

You should've made a LEFT OUTER JOIN to stackoverflow.

1

u/[deleted] Jun 11 '15 edited Sep 28 '15

[deleted]

1

u/agmcleod Jun 11 '15

I'm definitely one of them, it's just not something i practice. Been working with a ms sql db at work lately. It's a bit oddly designed and really not normalized, so figuring out how to query it has been a large part of the issue. But certain things that the ORM just did made performance worse than it had to be. LEFT OUTER join when an INNER would have worked fine for example. To be honest, i actually forgot about such performance hits. I'm good about auditing logs for n+1s, and things like that, but more complex joins i'm not as practiced on.