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.

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();
}

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.