Thursday, September 23, 2010

Connection Strings - Oracle - SharePoint WebParts

Connection strings and connection pool concepts have been around for many years now. Every time, someone searches on the web, people get tons of topics on it, most of them explaining the same set of properties for either Sql Connection string or Oracle Connection string. By now, every one in IT world understand that there are some properties we need to tweak into for properly adjusting Connection Pool settings. But, most of the time, default settings are acceptable.
One of the common problems we figure out after deployment is the "slowness" bug intrusion into some part of the entire architecture. This could be at the web part code level where people use connections and not dispose them off properly or could end up with a stored procedure which has got some code due to our famous "Copy & Paste" feature which is making our lives miserable.

We encountered the same situation recently, in which everyone points fingers at the web part code to say that code is not scalable and we finally ended up fixing up the stored procedure.

No matter how configured Connection Pool, we could never pass 1000-1500 connections, getting data back successfully. Forget about web part code, I am just talking about a simple console application prepared in .NET just to fire up Oracle Stored Procedure on multiple threads.

If we could not get the stored procedure running successfully for about 1500, how on earth, people can just blindly say web part code does not work?

Interestingly, stored procedures were using some LDAP to get some network related settings, which was the actual culprit to slow down the performance and hence deployment delay. I thought I would just let people know various things we could do when figuring out such deployment issues. Here are some things we can do.

  • Do not point fingers and listen to what others say.
  • Make sure always use "Using" block to dispose off Connections and other objects in a timely manner.
  • Never open connections manually wherever possible. For example, we could use data adapter with Fill method without opening up a connection.
  • Make sure connection pool is configured properly.  And also when making connection object, make sure to construct connection strings unique (I mean "Unique" not even a space difference), so that all such connections will use the same connection pool. Otherwise, for each and every connection object, a new connection pool will be created.
  • Always, isolate the situation. For example, if you have any load balanced environment, keep only one server and make other offline and see if load balancing was not configured properly.
  • None the less, DO NOT oversee stored procedures. May be your team has a finest database developers, but after all they are human beings. Mistakes always happen.

No comments:

Post a Comment