News for hackers. Stuff that matters.
Re:ORMs don’t kill databases. Developers do. by kevingadd on Tuesday August 30, 2011 @08:16AM (#2941079)
"Error establishing a database connection"

Heh.

Anyway, cached text from Google:

----

Following a recent post I wrote Phillip Haydon made an excellent argument about not using LINQ in ORMs like nHibernate and Entity Framework without consideration to what they are doing.

Something I definitely agree with.

    Just as guns don’t kill people, ORMs don’t kill databases. Developers do.
I should have picked up on this because it’s happened to me on a project I was working on. Death by ORM During development everything was performing well. So well that even the most sceptical developers decided to use ORMs instead of stored procedures.

The application was responsive and users were happy.

However in production there has no happy ending. The application went from running like sh!t off a proverbial shovel to just sh!t.

It was a nightmare and the finger pointing began.

The problem was we assumed (always a bad thing) the queries generated by the ORM would be as good as if we had written them ourselves.

So when we looked at what was being generated it came as a bit of a shock to see what was going on.

But while it was easy to use the ORM as a scapegoat the reality was developers were at fault.

Here’s a simple example of how even the smallest of changes can make a difference in the SQL ORMs generate.

In the code below all we want nHibernate to do is select the of the fruit that is NOT also a color.

    [Test]
    public void Tale_Of_Two_Queries()
    {
        var fruitDatabase = new FruitDatabase();
        using (ISession session = fruitDatabase.Session)
        {
            using (ITransaction txn = session.BeginTransaction())
            {

                var apple = new Fruit() { Name = "Apple", IsAlsoAColor = false};
                var orange = new Fruit() { Name = "Orange", IsAlsoAColor = true };

                session.Save(apple);
                session.Save(orange);
                txn.Commit();
            }

            var firstQuery = session.Query<Fruit>()
            .Where(f => !f.IsAlsoAColor)
            .Select(f => f.Name)
            .ToList();        

            var secondQuery = session.Query<Fruit>()
                    .Where(f => f.IsAlsoAColor == false)
                    .Select(f => f.Name)
                    .ToList();
        }
    }
For the query that uses !f.IsAlsoAColor the SQL query looks like this

    select fruit0_.Name as col_0_0_
    from   Fruits fruit0_
    where  not (fruit0_.IsAlsoAColor = 1)
For the query that uses f.IsAlsoAColor == false the sql generated looks like this

    select fruit0_.Name as col_0_0_
    from   Fruits fruit0_
    where  case
             when fruit0_.IsAlsoAColor = 1 then 1
             else 0
           end = case
                   when 0 /* @p0 */ = 1 then 1
                   else 0
                 end
While this is a trivial example it highlights the fact you don’t know what the generated SQL will be, let alone the execution plan.

And if your query returns the results you want are you really going to spend time finding out what’s going on ‘under the hood’? How comes nobody talks about this?

I can’t ever recall a presenter warning about this during any ORM talk I’ve been to. The focus always on what it can do and how it compares to other ORMs rather than best practices. It’s not just a .Net problem either

After talking to Ruby on Rails developers this is a problem for them too, because Active Record doesn’t work 100% of the time. When it goes wrong they have to implement workarounds to execute queries without using active record. So how can I make sure it doesn’t happen to me?

The first thing is to know what issues to look out for. The nHibernate profiler alerts page is an excellent resource for this.

With this information you can use profiling tools to see what queries your ORM is generating and how your application is performing.

My last tip is to always use the best data access method for what you’re doing. It’s easy to become complacent or aim to everything using an ORM, but if it makes more sense to use a stored procedure then that’s what you should do.

It would be good to hear what your experiences are with issues like this and to share any tips/tools you have used to avoid ORM performance issues.

In the meantime I’ll leave you with this excellent DBA vs. Developer (Star Wars Style) video

Re:ORMs don’t kill databases. Developers do. by clarkevans on Tuesday August 30, 2011 @09:05AM (#2941233)
What seems to be suspicious SQL (the CASE/WHEN/ELSE) is probably not the problem. This is classic boilerplate SQL "cast" code to make a boolean value for databases that don't support booleans. Distracting, perhaps, but not a smoking gun. Likely the problem with something like this is that there's a full-table scan on the Fruits table, and that the function doing the scanning is called frequently without caching.
Re:ORMs don’t kill databases. Developers do. by dmethvin on Tuesday August 30, 2011 @10:33AM (#2941588)
> What seems to be suspicious SQL (the CASE/WHEN/ELSE) is probably not the problem.

It certainly could be the problem. Like you say, a CASE becomes a computed field and requires a table scan. A simple check for IsAlsoAFruit can be done with an index scan, assuming it has that index. This can be a huge difference in data scanning and memory load. Of course, there are also plenty of times when SQL will spurn a boolean index because it's not selective enough.

Re:ORMs don’t kill databases. Developers do. by jeffdavis on Tuesday August 30, 2011 @10:59AM (#2941718)
"Likely the problem with something like this is that there's a full-table scan on the Fruits table"

What do you think the DBMS should do for a table with two tuples?

Regardless, it should be easier for the DBMS to use alternate plans if simple expressions aren't hidden behind a bunch of useless CASE statements.

Re:ORMs don’t kill databases. Developers do. by trebor on Tuesday August 30, 2011 @09:36AM (#2941344)
One Rails project I had got sidelined for a good 6 hours because ActiveRecord requires IDs on everything. The documentation says that you can skip IDs, but when you try to save it crashes every time.

Go figure whose fault that one is.

Re:ORMs don’t kill databases. Developers do. by rahoulb on Tuesday August 30, 2011 @10:05AM (#2941464)
I'm interested - where did you learn that ActiveRecord does not require IDs?

The only case where I can think of where it doesn't require IDs is when building a join table (for a many-to-many relationship).

In every other case, you need to specify a primary key field (although it does not need to be an auto-incrementing integer called "id") and I'm sure it says as much in the docs.

EDIT: You can do a select and omit the ID field in the resultset, but you cannot save the results back again - simply because it does not know where to write it back. Again, I'm pretty sure I remember reading that in the docs, but I may have imagined it.

Re:ORMs don’t kill databases. Developers do. by trebor on Tuesday August 30, 2011 @01:17PM (#2942311)
http://api.rubyonrails.org/classes/ActiveRecord/ConnectionAd...

    create_table(:categories_suppliers, :id => false) do |t|
      t.column :category_id, :integer
      t.column :supplier_id, :integer
    end
I did this and indexed the table by date, and it works for everything except saving a record. Like I said, an ID/primary key is required though that section of the documentation implies that an ID is optional.
Re:ORMs don’t kill databases. Developers do. by rahoulb on Tuesday August 30, 2011 @03:45PM (#2942951)
Hmm - yeah the docs are sort of unclear, if you're not familiar with the convention.

:categories_suppliers implies that the table is a join table (a many-to-many join between categories and suppliers) - which is why it has no ID and why it only has the two foreign key fields with no other data.

Re:ORMs don’t kill databases. Developers do. by trebor on Tuesday August 30, 2011 @06:41PM (#2943539)
Yet the assumption that all models require an ID is a fallacy. I was generating reports unique to the day, therefore a simple Date field was adequate as an index/ID. I don't know about other ORMs but when working directly with the SQL it'd be no issue.

I hold out that it's a problem in the design of ActiveRecord to assume that there will always be a primary key. That said, if I can just tell the model "identify yourself by this key" WITHOUT setting it as the primary key I would be happy.

Re:ORMs don’t kill databases. Developers do. by trebor on Tuesday August 30, 2011 @01:12PM (#2942296)
Are you saying that because I didn't set the date field as my primary key, it crashed?
Re:ORMs don’t kill databases. Developers do. by EGreg on Tuesday August 30, 2011 @10:51AM (#2941675)
If you are contemplating using or not using an ORM, chances are you are just starting to use a data store. At this point, perhaps you should contemplate whether to use a SQL based database or not!

You can always use a NoSQL database, such as:

* Riak * MongoDB * Redis

or any of the other ones. And if you go with them, you won't need an ORM. If you go with a database that needs SQL, may as well explicitly specify the model methods and their SQL, yeah.