0

We are using the petapoco repository pattern (similar to this blog post). As the page loads we open up the repository, run the query, dispose and then carry on processing. This is fine on light pages, but when this happens a few times in the page we get quite significant performance degradation.

I had, perhaps wrongly, assumed that connection pooling would cope with this, which is enabled.

I ran a couple of tests.

The page it's on (it's an aspx page) takes around 1.2 seconds to load as it is at the moment. The page is running around 30 database queries...and, looking at the profiler, is doing a login and logout per query (even with connection pooling).

If I persist the connection and don't close until the page ends, this drops to around 70ms, which is quite a significant saving.

Perhaps we need to keep the Database object hanging around for the request, but I didn't think PetaPoco had this much of an overhead...particularly with the connection pooling.

I have created a test app to demonstrate it.

This demonstrates that loading a user 1000 times takes 230ms if the repository is reused, but takes 3.5seconds if the repository is recreated every time.

Paul
  • 9,409
  • 13
  • 64
  • 113

2 Answers2

0

Your usage of connection pooling is breaking best practices.

It says nowhere to get rid of it after every statement. I normally keep a connection / repository around while doing processing and only close it when my function is finished (with MVC).

Yes, even the connection pool has overhead and you seem to be really bound on making that show.

TomTom
  • 61,059
  • 10
  • 88
  • 148
  • Ok, well the best practices for this appear to be very unclear - there are many devs doing many different things. Are you opening this connection at the beginning of a view method, and then just passing it around whatever you need to do? – Paul Dec 10 '14 at 11:31
  • http://msdn.microsoft.com/en-us/magazine/cc163854.aspx#S4 appears to suggest opening and closing a connection as and when you require...unless I've misinterpreted this – Paul Dec 10 '14 at 11:35
  • Yes. As and when you require. Start processing page, require connection UNTIL I DO NOT ANYMORE. It does not say "for every single SQL Statement". I normally open the repository when i need it and keep it open until I do not need it anymore (which is normally the end of the function rendering the page). – TomTom Dec 10 '14 at 12:04
0

What I always do is create a single instance of my repository per request. Because I develop almost exclusively using the MVC pattern, that means I create a private class level variable in each of my controllers and use it to service any requests within my Action methods. Translated to WebForms (ASPX), that means I would create one in the BeforeLoad (or whatever the event just before PageLoad is) and pass it around as needed. I don't think keeping a class-level instance is a good idea for Webforms though, but I can't remember enough to be sure.

The rule of thumb is to use one instance of your repo (or any other type of class really) for the entirety of your request, which is usually a page load or Ajax call. And for the reasons you've pointed out.

Remember: information on the internet is free, and you get what you pay for.

Captain Kenpachi
  • 6,960
  • 7
  • 47
  • 68