Prepared Statements

[ChrisLandowski]

A desperately needed addition to AsyncPoco would be some new methods that return prepared parameterized commands for inserts, updates and selects along with a new method for mapping a poco onto the parameters of these commands.

These new methods would be similar to ServiceStack OrmLite's PrepareParameterizedInsertStatement, PrepareParameterizedUpdateStatement and SetParameterValues methods on their DialectProvider object.

Prepared statements make a huge difference in performance when inserting or updating a large number of records in a loop.

[tmenier]

A couple questions:

1. How have you used OrmLite's Prepare* methods in the past? I would think using them directly from user code would be very unusual. Or are you using InsertAll/UpdateAll? Those appear to be the only methods that could benefit performance-wise since Commands are re-used after Prepare* is called. A better understanding of the use case (an example would be great) will help me better understand the request.
2. As far as this making a "huge" difference in performance, have you done any sort of benchmarking to support that? I have to say I'm skeptical that it will make much of a difference at all. Bear in mind that this is (typically) in the context of sending large amounts of data over the network, into the RDMS and persisted to disk. (I'm reminded of a transatlantic flight analogy I saw recently that stuck with me.) If you google micro-ORM benchmarks, you'll see PetaPoco tends to rank at or near the top for iterative inserts and updates, typically ahead of OrmLite.

So I'm not dismissing this request, but I need some convincing that there are real performance gains to be had here.

[ChrisLandowski]

By using prepared statements the overhead of compiling and optimizing the statement is incurred only once, regardless of the number of times the statement is executed. The benefits of using prepared statements is common knowledge and no convincing should be necessary.

Please feel free to dismiss both of my requests, I spent the weekend writing my own micro-ORM which I plan on releasing as an open source project once I’ve had a chance to use it on a few projects.

On Mar 9, 2014, at 9:54 PM, Todd Menier notifications@github.com wrote:

A couple questions:

How have you used OrmLite's Prepare* methods in the past? I would think using them directly from user code would be very unusual. Or are you using InsertAll/UpdateAll? Those appear to be the only methods that could benefit performance-wise since Commands are re-used after Prepare* is called. A better understanding of the use case (an example would be great) will help me better understand the request.

As far as this making a "huge" difference in performance, have you done any sort of benchmarking to support that? I have to say I'm skeptical that it will make much of a difference at all. Bear in mind that this is (typically) in the context of sending large amounts of data over the network, into the RDMS and persisted to disk. (I'm reminded of a transatlantic flight analogy I saw recently that stuck with me.) If you google micro-ORM benchmarks, you'll see PetaPoco tends to rank at or near the top for iterative inserts and updates, typically ahead of OrmLite.

So I'm not dismissing this request, but I need some convincing that there are real performance gains to be had here.

— Reply to this email directly or view it on GitHub.

[tmenier]

Closing this as I'm not convinced there's a "huge" performance gain to be had. Feel free to submit a PR if you'd like to implement it yourself. I'm interested in seeing your ORM if you've published it.