Most of my websites I develop in modules. Meaing I will create a function that generates, members online, advertisements, menubar, news, ext.. Each function uses helper functions to interact with the database. I have one helper function for single selects, and one for queries, and one for inserts. The current logic is open connection, do logic, close connection. The result is I open and close around 100 DB connections to MySQL 5.0 on a single page request. My question is would it be better to pass 1 database connection byRef to all my functions, or have 1 global DB connection for each page request? What I am currently doing has worked so far, but I am not sure if its scalable once the number of visitors increases. The only reason I wondering about this is because I have a website coming up that will need the ability to scale out as traffic increases. It will start out on a VPS and eventually be running on multiple load balanced Dedicated Servers. Any thoughts?

ASP.Net uses Connection Pooling, so you don't have to worry about using the same Connection for multiple database operations (which can be tricky and difficult at best with ADO.Net, for several reasons). Instead, the recommended practice is to open and close your Connections explicitly whenever you need to connect to the database. As long as the Connections all use the same Connection String, they will be pooled, and you wan't actually be creating a new one each time you "create" one; instead, you'll be using one you already created previously, which was pooled.

Thanks! That is exactly what I wanted to know.

See the old school scripters are used to opening a DB connection "at the beginning of the file" (one of the first includes) and closing it at the end. And lets not even get into those ColdFusion people They don't explicitly do anything lol

Contrary to those practices, I've been opening and closing connections on a need-to-use basis since beta was running (before version 1) and I've never had any performance issues. Ever.

Actually the problem I ran into was when doing repetitive DB calls ( where a connection is opened and closed ) ASP.NET is not able to free up the connection pool fast enough. I had to explicitly open the connection at the beginning of my script and then close it at the end. Rather than trying to do ( open close ) thousands of times on a script. Not sure if this was due to the script running on a shared hosting account where resources are limited, or if this was an architectural problem with my code itself.

Any thoughts on this?

Actually that SHOULDN'T be an issue. But on the same note, at times, I've had 5+ pools remain active at times, but it shouldn't be causing any problems. If it's that big of a deal to you then disable pooling. But realize that it's not leaving open connections... just pools waiting to be used. There's a difference, and having pools in a "sleep state" isn't hurting anything.

I'm also going to advise you to NOT disable connection pooling, however. There can be serious performance implications for doing so. What I would recommend is fooling with your "min pool size" and "max pool size" properties and seeing if that gives you the results you're looking for.

http://msdn2.microsoft.com/en-us/library/8xx3tyca(vs.71).aspx

Good article, but it doesn't mention MySQL once. Most of my sites are using MySQL 5.0. Were you talking about SQL server or MySQL? As my issue is with MySQL. Here is an example of one of my helper functions.

Okay. I use both MSSQL and MySQL. With MySQL I'll notice 2-3 pools left open sometimes. I really don't think much of it to be quite honest. The recycle every so often.

Your function looks fine. I use similar functions, but I use the USING block ALOT...



In the end these samples are very similar and should produce the same results (differences aside, of course).

The only thing that I see that I'm not too sure about is the GetConnection() function call... does that pass a connection object back or just a string? If it's passing an object it could be causing problems.

getConnection just returns the connection string. I think the issue is with the shared hosting servers, as they are not able to keep up when thousands of connections are being open and closed in a short amount of time.