Monday, August 28, 2006

Of databases and connections

A few notes to self regarding SqlClient and OleDb connections:
  • link between program and data

  • opening a connection is expensive

  • hence connection pooling: ADO does not destroy the connection object even after you close it

  • the connection is kept in a pool

  • it is destroyed after a time out interval (60 seconds => disconnect in SQL Trace)

  • reusable connection: which matches the connection details(data store, user name, password)

  • to turn connection pooling off in OLE DB: append to ConnectionString 'OLE DB SERVICES = -2':

    • significant differences: 6 seconds for 100 000 connections to ....?

    • not using this leaves the connection in SQL logged in at the time of the initial logon even after it is closed and reopened in code

    • the connection disappears from Activity Monitor when the program exits

    • however, if a connection is closed and the program is still running, after a while it disappears from the Activity Monitor (after the time out)

    • if the connection is not closed, it stays open in the Activity Monitor

    • multiple connections are opened if a Connection.Open is issued even if they have the same authentication and data store

  • setting the connection to null/Nothing clears it from the pool (? does not seem to affect the Activity Monitor)

    • if the connection is set to nothing without closing it, it shows in Activity Monitor

    • not closing the connection causes it not to time out even when set to nothing

    • it is not clear what effect has setting the connection to Nothing/Dispose-ing in OleDb

  • in ODBC: use the control panel (how do you turn it off???)

  • using the SqlClient instead of OleDb shows the application in Activity Monitor as .Net SqlClient Data Provider

  • using SqlClient seems to keep the connection alive even after closed for longer than OleDb (does it ever time out?)

  • using OleDb shows the application as the exe not the OleDb Data Provider

Ok that is a lot of notes to self. I'm investigating this stuff: it's fairly well known but when you have to debug performance problems every little details counts and you have to be considerably more careful reading the fine print.

Which reminds me, each OS should provide some kind of relational/transactional storage service. Unix/Linux/Mac OS already does - SQLite.