Managing DB connections, and DTC

[computergeek]

Hello,

I am unsure on when/if I need to close my DTC connections, and when to close my DB connection... I don't explicitly open a connection for my DTC recordsets, each one is defined to use the DB connection defined under the Data Environment/Global.asa. When do you close these connections and recordsets?

Any other information regarding performance issues would be appreciated. Thanks!

Computergeek

 

[MerlinB]

Recordsets used to populate listboxes and label controls should be explicitly opened then closed (empty listbox controls auto populate when the recordset opens). Do this ONCE ONLY - ie when thisPage.firstEntered = true (if you have added the PageObjectDTC). Lists remember the values between round-trips.

You do not need to close recordsets used for grids - indeed it may cause problems as the recordset will reset various settings.

I usually change the recordset 'AutoOpen' option from its default to UN-TICKED. This is because most of my recordsets require parameters - and for Listboxes these should be under YOUR control (as specified above).

http://www.websitedesign.co.uk

http://www.zentext.com

(Content Management)

 

[computergeek]

Hi MerlinB,

Thanks for the information... I just want to make sure I understand this correctly.

Opening DTCRecordsets:

Within thisPage_onenter()
- Open Listbox recordsets
- Do not auto-open these recordsets

DTC used for the main processing (grid, formmanager)
- Use auto-open
On on page I turn off the auto-open as I check to see "who the user is" before opening the recordset, thereby changing the sql string, then opening it. In this circumstance, do I need to explicitly close the recordset?

Closing Recordsets:
Within thisPage_onexit()
- Close Listbox recordsets where the auto-open is not checked
- Close Grid/Formmanager recordsets where auto-open not used?

Recordsets and Grids/Formmanger objects:
You said DTC Recordsets set for a Grid does not require closing, will it automatically be closed when you move off the page? What happens when you execute a function within a included script file? (I assume it thinks it's on the same page and stays open.)

Opening Closing DB Connections:
I establish a connection upon a successful login, and then
Closing it within the Session_OnEnd event. Not sure it the connection object I opened within the login page is accessible from the global.asa Session_onEnd event.

Sub Session_OnEnd
adoConn.close
Set adoConn = Nothing
End Sub

I am trying to figure out why I have 3-4 DB threads going when I am one certain page? (I do a sp_who within SQL tool...) I expected to have one connection to the DB, with 3-4 queries executing at the same time (listboxes, form etc.)

I really need to get a handle on this to ensure our application is not using more resources than necessary.

Thanks Merlin, your assistance is VERY much appreciated.

Computergeek

 

[MerlinB]

You can skip this ramble if its old-hat to you...
Unlike old-style client-server apps, where each user logged into the database (making it easy for the DB-Admin to see who was connected). The client application would typically hold 2 or so connections permanently open for all selects and updates. The more users, the more connections.

Then came the Middle-Tier idea. Pop all db code in a server-based layer. Each client consumed objects, not connections. Whatever the marketing guys said, it was painful for VB developers even with MTS (that hosted this middle tier). One of the (many) downsides was that users no longer connected directly with the database - a 'proxy' user made the connection(s). Now the DB Administrator had no way of knowing who was connected. One 'benefit' of middle-tier is that as the number of users grow, the number of db connections did not (at least, not at the same rate) - it was 'scaleable'. A web-server can be thought of as a 'Middle-Tier' layer, and it exhibits these characteristics.

To make the performance of the middle-tier slightly better, they decided to 'cache' db connections. Although your app closes a connection, ODBC will actually keep it open. ODBC/MTS will establish a minimum of 4 concurrent connections that stay open for about 3 minutes after use. (This is called connection pooling. I cannot remember where this can be configured.)

A single connection can only do one thing at a time - a select or an update, for example. A web page that fills 5 lists must run these 5 SQL selects sequentially. To span them across as many connections that are available, the web page would have to use process threading - only really available in .Net, as it makes the programming model straight-forward. However, each of the 5 SQL selects may actually use different connections - ODBC/ADO decides on this - assuming that you open/close between each command. This explains the multiple connections that you see.

You should close each SQL command before the page is sent to the client - handled automatically by the DTC objects.
An open dtc recordset is 'closed' when the page is complete, and re-opened when a server-round-trip is provoked. This cycle of closing and re-opening seems inefficient - but it is the only way that web forms can work.

Listboxes and Labels 'hold' their values in hidden form fields, and so only need populating once.

Well, I hope this helps - it turned out a bit like a school lesson. Sorry.

http://www.websitedesign.co.uk

http://www.zentext.com

(Content Management)