Connection Pooling


  • Discourse touched me in a no-no place

    So my company's web app (written in classic asp), for some bizarre reason, creates a single ADODB Connection in global.asa and then each page just uses the object as needed. I'll let you all pause and contemplate that for a few seconds, and just say I didn't write it, and historically, the user base who used it didn't have a lot of simultaneous users--I assume/hope access to the object is serialized or something. But now we've got a couple of customers who have a lot more simultaneous users, and we're starting to see some weird problems--my "favorite" is one page crashing with the rather helpful "Error in row" from the driver. I think I've eliminated all the regular causes, and am thinking at this point I need to enable connection pooling or something to allow for higher simultaneous user counts, because this error is probably something that boils down to a timeout from too many people waiting for the object. Does this seem like a reasonable theory?

    If so, from what I've read so far, it seems like if I enable connection pooling in the driver, the thing to do is simply create/open the a connection object at the top of each page and close it at the bottom, and make sure I create it the same way every time, and ODBC will do the rest of the heavy lifting for me. Is this close to true? I don't have the resources (both in terms of people and # of computers) here in the office to test that, but I have a customer I can use as a guinea pig.

    One last thing--this will cause extra connections to the database, right? It seems like it must have to. I'll have to make sure the customer has enough licenses.



  • Side note: I know nothing specific to ODBC / ASP connection pooling.

    Connection pooling, particularly a server-managed connection pool, is generally a good idea.

    I don't know about ODBC/ASP Classic in particular, but how connection pooling generally works is:

    • When you open a connection, it removes that connection from the connection pool.
    • When you close the connection, the connection is returned to the connection pool.

    ...so make sure you close the connection at the end of the page.

    @FrostCat said:

    One last thing--this will cause extra connections to the database, right? It seems like it must have to. I'll have to make sure the customer has enough licenses.

    Yes. The ODBC Connection Manager control how many connections are in the connection pool.

    As far as I can tell, to enable Connection Pooling for classic ASP, you have to modify a registry setting... specifically changing HKEY_LOCAL_MACHINE\System\CurrentControlSet\Services\W3SVC\ASP\Parameters's StartConnectionPool key to 1.


  • Discourse touched me in a no-no place

    @powerlord said:

    so make sure you close the connection at the end of the page.

    The funny thing is, they had these two standard includes to be used at the top and bottom of the page, that would create/open and then close the connection object. At some point they commented that out everywhere and went to the "one connection object stored in the Application object". Seems like somewhat of a WTF and the people who did that are long gone--my only guess is that they figured at the time that there wouldn't be so many simultaneous users that it would be a problem, and that way they could cut down on database connections--and Progress database servers are licensed on # of simultaneous connections.



  • @FrostCat said:

    One last thing--this will cause extra connections to the database, right? It seems like it must have to. I'll have to make sure the customer has enough licenses.

    Not really. It creates the minimum number of connection you need for your workflow. As for licensing, I'm not aware of any major RDBMS that licenses web app usage by actual connection made to the database. Generally, connection pooling doesn't change your licensing obligation (nor does a global connection). Licensing is almost always by the processors/cores on the box, or the number of people (not connections) accessing the data.


  • :belt_onion:

    @Jaime said:

    As for licensing, I'm not aware of any major RDBMS that licenses web app usage by actual connection made to the database.

    @FrostCat said:

    Progress database servers are licensed on # of simultaneous connections.

    Sounds like he should be using something other than Progress 🚎

    Seriously though, that actually sucks. You might have to increase the license count then...


  • Discourse touched me in a no-no place

    @Jaime said:

    Not really. It creates the minimum number of connection you need for your workflow.

    Currently his application is creating one. Moving to a connection pool will (unless someone does something stupid) create more than one.
    It'll create more than it currently does.



  • @sloosecannon said:

    @FrostCat said:
    Progress database servers are licensed on # of simultaneous connections.

    Sounds like he should be using something other than Progress 🚎

    I did say "major RDBMS". It seems Progress is the WTF here. Any database vendor that licenses by connection is begging their customers to create middleware that both reduces the income to the vendor and makes the application shittier.



  • @loopback0 said:

    Currently his application is creating one. Moving to a connection pool will (unless someone does something stupid) create more than one.It'll create more than it currently does.

    Yea, but every time his application is in a situation where pooling would create two, his application errors. That's why I said "not really" instead of "no". It only creates more in the cases where the only sane solution to the problem he is having is to create more. It creates fewer (or the same number of) connections than any other conceivable solution that doesn't error.


  • Discourse touched me in a no-no place

    @Jaime said:

    As for licensing, I'm not aware of any major RDBMS that licenses web app usage by actual connection made to the database.

    Generally speaking, Progress data server licensing is by concurrent number of users, i.e., db connections. I know that Oracle, MSSQL etc., normally aren't. But I have to work within the constraints I have, so if a client has a 5-user license I can't let him make 10 connections.

    Yes, this licensing model may be TRWTF.


  • Discourse touched me in a no-no place

    @loopback0 said:

    Moving to a connection pool will (unless someone does something stupid) create more than one.

    This is not likely to be a problem for most clients--for most of them I can probably get away with the current system, since they haven't had problems. I have a couple with "lots of" simultaneous, users relatively speaking, that are having problems nobody else is. For them, either they already have enough connections or the cost of buying a few more licenses won't be a problem.


  • Discourse touched me in a no-no place

    @Jaime said:

    Yea, but every time his application is in a situation where pooling would create two, his application errors.

    Well, I can't say I have seen that so far. I just suspect that if, say, five or ten or 20 people try to hit the application all at once, this is what causes the problem I'm seeing. These are all short-lived pages that take a fraction of a second to generate.



  • @FrostCat said:

    for some bizarre reason

    @FrostCat said:

    classic asp

    Found your bizarre reason.



  • @FrostCat said:

    Well, I can't say I have seen that so far. I just suspect that if, say, five or ten or 20 people try to hit the application all at once, this is what causes the problem I'm seeing. These are all short-lived pages that take a fraction of a second to generate.

    You still want pooling. When there are no more connection available in the pool and the pool is at its maximum size, a call to get a pooled connection blocks until one is available. This serializes your use of the connections. Just set the pool max to one and you can use pooling without increasing license count.

    Most drivers don't support multiple active record sets on the same connection. Using a global connection runs the risk of one page issuing a query while another is running. Switching to pooling solves this problem.


  • Discourse touched me in a no-no place

    @rc4 said:

    Found your bizarre reason.

    *shrug* I didn't write it, I just have to maintain/improve it. In my Copious Free Timeβ„’ later this year I might try to lift it to asp.net or something.


  • Discourse touched me in a no-no place

    @Jaime said:

    You still want pooling.

    That's what I'm coming to believe, yes, but some genius before me thought it was a good idea to set it up the way I described it above, like I said, probably to conserve database connections.



  • It's been decades, but assuming the database is MS SQL, I'm 99.5% sure Classic ASP did connection pooling by default, the same way ASP.NET does.

    So I would guess your supposition is correct; you merely need to create a connection for each page load and let the framework do all the connection pooling heavy-lifting.



  • @loopback0 said:

    Currently his application is creating one. Moving to a connection pool will (unless someone does something stupid) create more than one.It'll create more than it currently does.

    You can cap how many it'll use, but ... yes. It's kind of worthless to enable Connection Pooling unless you're willing to use more than one connection.



  • @FrostCat said:

    Generally speaking, Progress data server licensing is by concurrent number of users, i.e., db connections.

    Number of users and number of connections aren't the same thing.

    I'm confused. Progress makes it impossible for a single user to have more than one open connection at any given time? Is that what you're trying to convey?


  • Discourse touched me in a no-no place

    @blakeyrat said:

    You can cap how many it'll use, but ... yes. It's kind of worthless to enable Connection Pooling unless you're willing to use more than one connection.

    That would just be for testing purposes for me. Clients would have to set the cap so as to not exceed their license.

    Some of these guys just buy the minimum count, which is 5 concurrent connections, because it's not an application that a lot of people will use normally. The employee self-service web site is starting to strain that model, though, which was not anticipated 15 years ago or whenever they started writing it.



  • I'm still confused whether the limit is a USER limit or a CONNECTION limit. EXPLAIN!


  • Discourse touched me in a no-no place

    @blakeyrat said:

    I'm confused. Progress makes it impossible for a single user to have more than one open connection at any given time? Is that what you're trying to convey?

    No, not at all. It's licensed by # of concurrent connections. The way most GUI Progress applications are written, you connect to the database on application startup, release the connection on exit, and consume one connection the entire life of the process. Doesn't have to be that way, though. And .Net apps that connect via ODBC, for example, could certainly open multiple connections.

    Things can get more complex with web applications, but let's not talk about that at the moment.


  • Discourse touched me in a no-no place

    @blakeyrat said:

    I'm still confused whether the limit is a USER limit or a CONNECTION limit. EXPLAIN!

    The terminology is sloppy, sorry. The limit is generally on connections.



  • Ok, now it makes more sense.



  • That licensing mode will just push users to invent all sorts of "smart" ways to be able to stick to at most 5 concurrent connections... because it saves some bucks.


  • Discourse touched me in a no-no place

    @AlexMedia said:

    That licensing mode will just push users to invent all sorts of "smart" ways to be able to stick to at most 5 concurrent connections... because it saves some bucks.

    I'm sure you don't think all sorts of people aren't aware of that.

    FWIW at my company we wouldn't help our clients exceed their licenses...but those clients are also pretty willing to buy more licenses as needed. One reason I've stayed here as long as I have is that while a lot of them are about as sharp as a sack of wet kittens, they're by and large good people.



  • Btw, I think you can also get a free ride of connection pooling if you connecting the database via ODBC (requires ODBC v3.0+ and MDAC 2.0+, that means anything above IIS3 has support this way).



  • @cheong said:

    I think you can also get a free ride of connection pooling

    You absolutely get connection pooling for free... but the app mentioned in the OP has one global connection that it never closes. So, even though connection pooling is there and functional, it never has an opportunity to put the connection back into the pool.


  • Discourse touched me in a no-no place

    @Jaime said:

    You absolutely get connection pooling for free... but the app mentioned in the OP has one global connection that it never closes. So, even though connection pooling is there and functional, it never has an opportunity to put the connection back into the pool.

    That is correct. My understanding from what I've read elsewhere and here, though, is that if I open a connection with the same settings at the top of each individual page, and close it at the bottom, that gets me pooling.



  • Sound like a good time to use "copy file1+file2 file1" to append the include directive for a .INC file that 'includes a code snippet that close the connection" on each page.

    Use batch or write simple program to run it would be good enough.


  • Discourse touched me in a no-no place

    Can't do it that way--there's stuff that has to be first.

    The funny thing is there's already connect/disconnect include files, and most pages have them; all that stuff was commented out before I was hired. I have to assume it was done to kill connection pooling for licensing reasons because I can't think of any other reason.

    Now here's a horrifying question--can I use connection pooling and non-pooling access in the same application? To avoid a huge amount of work, I'd like to take the one page that's giving one particular client a lot of problems--I assume their problems are from too many page requests overwhelming the single connection--and let it have its own connection. Then (hopefully) all access to that page would get pooled, and (for now) every other page still shared the global connection. Over time I could work pooling back into the application, except that if I can do so I'd like to make it optional somehow so that customers who aren't seeing any problems don't have to buy more licenses.



  • @FrostCat said:

    can I use connection pooling and non-pooling access in the same application?

    Yes. The global connection is simply a "hogged connection" from the connection pool. If you migrate a handful of pages over to a sane model and set the max pool size to two, you'll have a single connection used for old style code and one for the new style code.



  • See, I would answer "no", and then give the exact same explanation you did.

    If you "hog" a single connection for the entire runtime of the web app, you're still using connection pooling for any reasonable definition of "using". You're just using it in a weird and not-recommended way. But it works, which I think is the important bit.


  • Discourse touched me in a no-no place

    I think I might do this, then. I could set the pool size to 3 or 4, as a start, and effectively give that one page a couple extra connections.


  • Discourse touched me in a no-no place

    That's not helpful, Ben. It's funny, yes, but it's not helpful. Please don't mess with the title of a serious thread.



  • Yeah start with minimal code changes is a good idea.


Log in to reply