Is re-using a Command and Connection object in ado.net a legitimate way of reducing new object creation?

Question

The current way our application is written, involves creating a new connection and command object in every method that access our sqlite db. Considering we need it to run on a WM5 device, that is leading to hideous performance.

Our plan is to use just one connection object per-thread but it's also occurred to us to use one global command object per-thread too. The benefit of this is it reduces the overhead on the garbage collector created by instantiating objects all over the place.

I can't find any advice against doing this but wondered if anyone can answer definitively if this is a good or bad thing to do, and why?

Answer 1

While I'm not sure about reducing the number of command objects, reducing the number of connections is definitely a good plan. They're designed to be relatively expensive to set up (hey, they involve actually opening a disk file!) so keeping them around for a relatively long time is highly sensible. So do the first stage of your plan and retime to see if that makes things good enough, or if you need to do more work optimizing…

Note that it is quite possible that generating the command objects once per connection will be a saving too, since that will allow them to be compiled once and reused multiple times. Not that that matters until you're persisting the connection in the first place!