SQL Server Performance

ODBC and SQL Server 2005: Question about open and close connection!

Discussion in 'SQL Server 2005 General Developer Questions' started by huynhtuanh, May 5, 2010.

  1. huynhtuanh New Member

    Hi All,
    I have a VC++ application that using ODBC connect to SQL Server 2005. Some functions in my app need to get data many times (20-50 times per second), I wonder that I should always keep the connection or close it after using and open it again.
    Ex: Here is my example code (two ways for open and close connection), with the function "CMyClass::GetSummaryInfo(CString sObj)" is called a lot of times at once (this function only get data from DB, not update or delete).
    CDatabase g_db;
    The first way: Only open connection one time in Constructor and close connection in DestructorCMyClass::CMyClass()
    {
    g_db.OpenEx(m_sConnectionString.GetBuffer(), CDatabase::noOdbcDialog);
    }
    CMyClass::~CMyClass(void)
    {
    g_db.Close();
    }
    bool CMyClass::GetSummaryInfo(CString sObj)
    {
    CSingleLock sLock(&g_MutexSQLQuery);
    sLock.Lock();
    if(!g_db.IsOpen())
    return false;

    //Do my job here
    sLock.UnLock();
    return true;
    }
    The second way: open and close connection every time the function is called

    bool CMyClass::GetSummaryInfo(CString sObj)
    {
    CSingleLock sLock(&g_MutexSQLQuery);
    sLock.Lock();
    if(!g_db.OpenEx(m_sConnectionString.GetBuffer(), CDatabase::noOdbcDialog))
    return false;

    //Do my job here
    g_db.Close();
    sLock.UnLock();
    return true;
    }
    Using: how to using GetSummaryInfo function
    for (int i = 0; i < MAX_NUMBER_OBJECT; i++) //MAX_NUMBER_OBJECT maybe 20, 50, 100,...
    {
    if (GetSummaryInfo(GetObjectName(i)))
    {
    //Show all info here
    }
    else
    {
    //Show error: cannot get object info
    }
    }
    I don't know which way is better, can you help me? Sorry for a long message [:)]
    Thank you very much for reading my message!
  2. patel_mayur New Member

    The best pratice says that you should close your database connection as soon as you are done with it so that all the resources acquired by the connection cab be freed.
    In your case, as you are saying that you are querying the database 20-50 times per second. I would go with the first option.
    But, I will consider this as a unit of work and as soon as you done, release the connection.
  3. huynhtuanh New Member

    Thank you so much for the reply!
    Yes, I will reconsider my code base on your ideas: separate it into "unit of work" and release the connection every unit done!
    Thanks again!
  4. Mike.Rewnick New Member

    I think the answer above is far too simplified
    I know it from my own practical expirience.
    The only way to get proper answer is to do some profiling and measure performance
    If your application works 24/7 there is no need to disconnect because it will slow down your application,
    If it does not run all the time may be it is a good idea to disconnect when it is idle.
    We develop ETL tools and we do not disconnect until process is completed
    We also run thousands of inserts/updates with parameters per minute when Loading data into sql server via ODBC
    Mike
    DB Software Laboratory
    ETL Tools for everybody
  5. moh_hassan20 New Member

    When you close connection , it will be in the connection pooling , and if the connection string is the SAME , you re-use it again if it is available.
    So close connection as you finish.
    you can tune odbc performance , have a look to:
    http://www.sql-server-performance.com/tips/odbc_oledb_p1.aspx
    Performance Tuning SQL Server ODBC
    be sure you have last SP, have a look:
    http://support.microsoft.com/kb/318606
  6. Mike.Rewnick New Member

    It takes time to disconnect and connect even if connection is in the pool, because you are calling ODBC api functions when you do not have to
    If you do
    connect
    run sql
    Disconnect
    10000 times
    instead of
    Connect
    run sql 10000 times
    Disconnect
    It will be slower
    Why make unnecessary ODBC calls?
    Mike
    DB Software LaboratoryETL Tools for everybody
  7. moh_hassan20 New Member

    Connection pooling can be used by an ODBC application exhibiting ODBC 2.x behavior, as long as the application can call SQLSetEnvAttr.
    When using connection pooling, the application must not execute SQLstatements that change the database or the context of the database,such as changing the <database name>, which changes the catalog used by a data source.
    http://www.vieka.com/esqldoc/esqlref/htm/odbcodbc_connection_pooling.htm

Share This Page