OLE DB Resource (session) pooling (using connection pooling in ADO development)

Source: Internet
Author: User
Tags connection pooling include odbc odbc connection ole resource
Ado|session

Oracle:oledb Resource (session) pooling
by Eric Ma EMa@ompus.jnj.com

Database connectivity is a prerequisite to today's dynamically generated Web pages. However, connection to databases are one of the most expensive operations one does from within an ASP page, because of the Overhead involved in database user authentication and allocation of the database, handle user interaction with the Database, among other things. All this can add significant latency to your Web site. Based on the "recent discussions in" This list, it is apparent ' everyone is keenly aware of ' this issue and would Minimize the negative impact on performance by have to connect to databases. For a programmer with a client/server programming background, it are natural for the person to design a solution based on U Sing a database connection that persists through a entire user session. However, for a web-based application, this are a big no-no ("the article at/advice/dbsessionapp.asp  for reasons W Hy you should never doing this).

The best practice for database connection (we limit my discussion to Oracle) from a ASP application is to open t He connection at late as can, with close it as soon as you can, which means with open and close database connection on E Very ASP page. The encouraged practice is to use just one set of Oracle Username/password for your entire application, not one set for EA CH user as you are used to does with client/server applications. Access control are no longer performed by the database, but by your application. You can save the ADO connection string in a application variable in the Global.asa file. Some other alternatives include saving it in the Windows Registry, or use a include file that has the ADO connection info Rmation, and include that file in the pages where database connection is required.

Opening A new database connection for each ASP page may is as bad as you do, because from ADO 2.0 up can UTI Lize the "resource pooling" feature offered by Microsoft's OLE DB Provider for Oracle. Resource pooling is similar to ODBC connection pooling, where a connection was returned to a pool instead of being D immediately after it are closed and set to nothing in your code. The following article for more details:
http://msdn.microsoft.com/library/techart/pooling2.htm.

The purpose of this FAQ are to demonstrate how to properly set the environment on your IIS/NT server to take advantage O F OLE DB resource pooling. Unlike with ODBC, where your can enable pooling and set the timeout parameter from the 32-bit ODBC utility into the control P Anel, with OLE DB your have to directly work on the Registry itself. Again, this is not as scary as it sounds. To did so, your determine the version of OLE DB Provider you are using by running the script found At:http://www.lear Nasp.com/learn/connectioninfo.asp, then dependent on the version of your have, you could do the following steps:

? If you are using the OLE DB 2.0 provider can do resource pooling but you cannot modify the default timeout Configurati On (seconds). To set up resource pooling, add the following subkey in the Registry:
Hkey_classes_root\clsid\{e8cc4cbe-fdff-11d0-b865-00a0c9081c1d}\oledb_services. Make sure the DWORD type, and enter a hexadecimal value of 0xFFFFFFFF.

However, I suggest that you don ' t use the 2.0 version of the Provider any more because of potential memory. The following KB article for more:
Http://support.microsoft.com/support/kb/articles/q194/3/87.asp

? If you use the OLE DB 2.1 or 2.5 provider, in addition to doing the above step, you can configure the pooling timeout Meter by adding the following subkey:
Hkey_classes_root\clsid\{e8cc4cbe-fdff-11d0-b865-00a0c9081c1d}\sptimeout. Again This entry was of a DWORD type and you enter a decimal value in seconds. It is up to decide the number of second for you want to set, depending on how busy your site is and how frequent the PA GES need to access Oracle data.

A KB article describing the above step can is found at:
Http://support.microsoft.com/support/kb/articles/q237/9/77.asp

After your make the above changes, reboot your NT Server to make Registry changes. This is the necessary.

Congratulations, now you are using the wonderful feature of OLE DB resource pooling! I hope from "Today" on "You'll Never ever" about caching connection objects in session variables anymore! Treat it as evil!

Some Side Notes and observations:

? With Oracle, in order to use resource pooling, your need to add the Registry subkey. Has only the OLE DB Services =-1 "In your connection the string is not enough.

? I came across the following KB article in MSDN saying OLE DB resource pooling are Automatica



Large-Scale Price Reduction
  • 59% Max. and 23% Avg.
  • Price Reduction for Core Products
  • Price Reduction in Multiple Regions
undefined. /
Connect with us on Discord
  • Secure, anonymous group chat without disturbance
  • Stay updated on campaigns, new products, and more
  • Support for all your questions
undefined. /
Free Tier
  • Start free from ECS to Big Data
  • Get Started in 3 Simple Steps
  • Try ECS t5 1C1G
undefined. /

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.