Is PetaPoco Insertion the Fastest Way?

Question

What is the fastest option for inserting records into a database: with which:

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

Which one should be used for performance reasons? One of them is no less convenient for me than the other ...

Thanks.

Answer 1

Which should I use for performance reasons?

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

Therefore, you should use Database.Save when your code really needs to save changes to an object that may be new or existed before. Also, note that for your table, you must use the auto-increment primary key column for Database.Save .

Even without a slight difference in performance, I would prefer the correct semantics - with Insert or Update over Save.

One of them is no less convenient for me than the other ...

This is not true.

Database.Insert(poco) will look up the values ​​for tableName and pkName in the user-defined attributes of the definition of your poco class. If you use T4 templates, these values ​​will be automatically synchronized with your database, and they will be specified only in one place. On the other hand, if you pass them in every method call, they will be repeated countless times in your code base. DRY. What if you need to change one of the values ​​later?

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

 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 have listed for inserts, it looks like this: PetaPoco always calls the following method of the Database class:

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

And Database.Insert(tableName, pkName, poco) does the least amount of work (this is basically just a passing method), so I would suggest that it has better 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); } 

Perhaps it would be a little (and inconspicuous) faster just to invoke Insert(string, string, bool, object) overload directly.