PetaPoco Insert - Fastest Method?

Question

What is the fastest option for inserting records into the database: using which of these:

• Database.Insert(poco)
• Database.Insert(tableName, pkName, poco)
• Database.Save(poco)
• Database.Save(tableName, pkName, poco)

Which one should I use for performance reasons? One is not less convenient to me than the other...

Thanks.

Incredulity

Answer 1

Which one should I use for performance reasons?

The Database.Save methods retrieve the value of the primary key field using GetValue, then calls Database.Insert or Database.Update accordingly.

Therefore, you should only use Database.Save when your code really does need to save changes on an object that might be either new or preexisting. Also, note that your table must have an auto-incrementing primary key column for Database.Save to work.

Even without the slight performance difference, I'd prefer the correct semantics - using Insert or Update over Save.

One is not less convenient to me than the other...

That is not really true.

Database.Insert(poco) will look for values for tableName and pkName in custom attributes on the definition of your poco class. If you use the T4 templates, these values will be kept in sync with your database automatically, and they will only be specified in one location. On the other hand, if you pass them in each method call they will be repeated innumerable times throughout your code base. DRY. What if you need to change one of the values later?

Now, Database.Insert(poco) will be slightly less performant due to that lookup. However, PetaPoco caches the result of that lookup in a static dictionary, so the performance impact will be very small after the first lookup:

RWLock.EnterReadLock();
PocoData pd;
try
{
    if (m_PocoDatas.TryGetValue(t, out pd))
        return pd;
}
finally
{
    RWLock.ExitReadLock();
}

Braud

Yes, by this statement "One is not less convenient to me than the other...", what I did to remedy it was this: blogs.msmvps.com/bmains/2011/12/31/… Violation of DRY, yes, but it's better for performance according to this: toptensoftware.com/Articles/94/PetaPoco-More-Speed. I might try to take the customization a step further too... – Incredulity

@BrianMains: Do you really have a need to squeeze that extra little bit of speed out? I worked with customized SubSonic templates for a while (we still use them in an app we maintain), and it was a drag. In any case, I think I've laid out the tradeoffs clearly in my answer. You can certainly choose the faster route over the easier to maintain route, but few usages would really need it. The biggest gains will be from batching operations, prepared statements, proper indices, etc. – Braud

But you're talking about 9ms or so over 500 iterations, so the performance hit is pretty negligible. The database will be the bottleneck before the mapping code. – Photokinesis

@BrianMains: One other thing I notice as I read your blog post is that with the modification you're using, you are giving up your ability to operate on poco's polymorphically. For example, I've implemented a UnitOfWork class in a project using PetaPoco that relies on tracking all changed objects polymorphically - as object's, and submitting all the changes at once. Obviously, you may not need that ability - but it is a tradeoff having to know the poco class to use Users.GetTableName(), for example. – Braud

@qes Thanks for looking at it from all angles. Seriously, I appreciate it. Yes the the unit of work is implemented in many popular frameworks like entity framework L2SQL, NHibernate, and is a nice feature. It can cause some performance issues for large object graphs and tracking a lot of changed objects (experienced that with EF). In my scenario, it's a simple app, and all that would be overkill. But yes something to consider for future readers of this post. – Incredulity

Answer 2

In all 4 of the methods you listed, for inserts, it looks like PetaPoco always calls the following method of the Database class:

public object Insert(string tableName, string primaryKeyName, bool autoIncrement, object poco)

And the Database.Insert(tableName, pkName, poco) does the least amount of work (it is basically just a pass through method), so I would assume it is the one with the best performance.

Here is the code for Insert(string, string, object):

public object Insert(string tableName, string primaryKeyName, object poco)
{
    return Insert(tableName, primaryKeyName, true, poco);
}

It would probably be slightly (and unnoticeably) faster to just call the Insert(string, string, bool, object) overload directly.

Smelly