Maintaing a connection across pages (Persistent)

I've only been workign with Database programming for 6 months or so, specfically SQL Server. I've setup a site through a hosting company that is pretty reputable and allows me plent of space/bandwidth etc. However, they've recently been having problems with one of their servers (the one I'm co hosted on) as an "abornamlly" large amount of connections are being opened up on their server.

I realize that SQL Server is designed to handle large amounts of traffic and my humble little site probably isn't killing it with shear traffic, but I'm wondering if my programming practicies are sub par and causing problems.

I'm coding in ASP (work hasn't given us the chance to migrate to .net). Anyway my site is getting some 30 - 40k hits a day on the actual web portion and probably another 10-20k on backed stuff - all which open up database connections to do their work.

I've had locks and blocks routinely because of this and I'm afraid I'm just going about this all wrong.

So I'm wondering if there is a way to maintain an established connection in ASP across multiple pages and/or sessions so I don't have to constantly drop and reconnect to the db?

Any help would be great!So I'm wondering if there is a way to maintain an established connection in ASP across multiple pages and/or sessions so I don't have to constantly drop and reconnect to the db?

I don't think that this is what you would want to do. Database connection pools would normally be the responsibility of an applications server (something like BEA's WebLogic). You could write your own custom (COM+) connection pool manager, but it's a fair amount of work and would only make a difference if your site were generating an order of magnitude more traffic.

Check out the following link for some "best practices" when coding in ASP.

ASP Best Practices (http://www.valtara.com/csc123/WhitePaper/ASPBestPractices.htm#15).

Probably your best starting point is to tune your T-SQL, avoid using dynamic SQL (use stored procs), ensure you have adequate indexing and avoid mistakes like pulling back an entire recordset when you only need one or two records (ie, use a WHERE clause).

Regards,

hmscott