PetaPoco - More Speed!

Wednesday, 6 April 2011

Last night I noticed Sam Saffron's dapper-dot-net project and thought I'd modify its benchmark program to see how PetaPoco compares... a little more tuning and PetaPoco is now right up there.

This post is about PetaPoco - "a tiny ORMish thing for your POCOs". Read more on the PetaPoco Project Page.

Firstly, I neglected to mention in yesterday's post that the idea for PetaPoco's DynamicMethod support came from Sam Saffron's post (see here) on what he was doing to squeeze some more performance for Stack Overflow. Nice work Sam.

Secondly, Sam has open sourced his work as dapper-dot-net which includes a benchmark program for comparing various ORM's. Of course, I couldn't resist updating it to support PetaPoco and it quickly highlighted a few small bottlenecks. A little bit of tuning and these are some typical results:

Running 500 itrations that load up a post entity
hand coded took 65ms
PetaPoco (Fast) took 67ms
Mapper Query took 67ms
Dynamic Mapper Query took 74ms
PetaPoco (Normal) took 78ms
Dynamic Massive ORM Query took 85ms
Linq 2 SQL Compiled took 119ms
Linq 2 SQL ExecuteQuery took 256ms
Linq 2 SQL took 841ms
Entity framework ExecuteStoreQuery took 898ms
Entity framework took 1286ms

You'll see I'm running two benchmarks for PetaPoco - Normal and Fast.

  • Normal - all default options and "smarts" enabled
  • Fast - smarts like automatic select clauses, force conversion of DateTimes to UTC and named parameters disabled.

Normal mode is how I expect PetaPoco would typically be used, but disabling these extra features is always an option for when you're really try to squeeze everything you can.

Note that the results from the benchmark tend to jump around quite a lot. It's not uncommon for the top 6 contenders to come out in a completely different order. eg: I've seen the hand coded test come in last out of the top six and I think this is just a side affect of all these being very close.

Shared Connection Support

The main fix I made to get this performance was the ability to re-use a single database connection. In previous versions each query resulted in a new connection. By making the OpenSharedConnection method public, you can now call this up front and all subsequent queries will re-use the same connection. Calls to OpenSharedConnection and CloseSharedConnection are reference counted and can be nested. They're also called automatically internally when a transaction is is flight.

Opening the shared connection would probably be a good idea for the duration of a HTTP request. I haven't tried this yet but I going to try hooking this in with StructureMap's HttpContextScoped.

One final note about the shared connections. PetaPoco will automatically close the shared connection once on being disposed. This means if you call OpenSharedConnection once, and the Database is disposed everything should be cleaned up properly.

Other Optional Behaviour

The other functionality I've introduced is the ability to disable some of PetaPoco's behaviour:

  • EnableAutoSelect - controls whether PetaPoco will automatically put in select clauses. When disabled, this won't work:

    var a=db.SingleOrDefault<article>("WHERE article_id=@0", id);
    
  • EnableNamedParams - controls whether arguments to the Database class are processed. When disabled, this won't work:

    var a=db.SingleOrDefault<article>("WHERE article_id=@id", new { id=123 } );
    
  • ForceDateTimesToUtc - when disabled, DateTimes are returned exactly as the database provider returns them. When enabled, PetaPoco will set the Kind property of returned dates to DateTimeKind.Utc.

Being able to disable these featues gives a little performance boost, but also provides to possibility to work around these features if they're causing some undesirable effects.

Other Optimizations

I've also made some other optimizations:

  • First(), FirstOrDefault(), Single() and SingleOrDefault() - these aren't used by the benchmark program but I've also provided optimized versions of these that return just a single record - saves building a list, or an enumerable for just a single record.

  • Replaced using clauses with try/finally - internally PetaPoco was using a disposable object and using clauses to ensure the connection gets closed. I've replaced these with direct calls and a finally block to save instantiating an extra object.

Anyway, all this is up in GitHub and NuGet now. I'll also submit all of this back to Sam as soon as I figure out how.

« PetaPoco - Performance Improvements using DynamicMethods Benchmarking SubSonic's Slow Performance »

1 Comment

Wondering what these results would be like with EF 5 now in the works.... would it be possible to update this? Newer versions of Peta have also been released since this post.

20 April 2013 02:01 AM

Leave a comment

Name (required)
Email (required, not shown, for gravatar)
Website (optional)
Your Message
Leave these blank: