Performance Analysis of Addnew and Execute Command in CRecordset to Insert data

• Download source - 74.8 KB

Introduction

While working on a video database server project using C++, I found that adding data entries to a database in real time is performance-critical. This is especially the case if the data rate is high. In my case, I was getting subtitle information or sentences at four subtitles/sentences per second -- which is 345600 per day -- and I had to keep a record of the whole year. I was entering the data coming from socket to my database server. I was locally entering the data to avoid any remote disconnection and bottleneck. It may be disastrous if the connection breaks, otherwise. I have more than two dozen instances of this software running on a single server, but on different ports. Adding data to databases at this rate is a performance issue in both the time and memory domains.

Background

Some background of database programming via C++ or C# is good enough to understand this article.

Using the code

//SQL Command
--INSERT INTO [DEV3].[dbo].[DEV303]([longdate],[longtime],[ColeDateTime],[TEXT])
--VALUES ('1921212','121212','2007/3/30 17:08:44','Some Text ')

The following two functions have been written to insert some data in real time after receiving it from recevData.

SaveinSQLInsert

void ServerRecev::SaveinSQLInsert(char * recevData )
{
    // Some Code to do with recevData

    ////////////////////////Adding to Database//////////////////////////
    CString csUser = "sa";
    CString csPwd = "";
    CString csServer = "(local)";
    CString csDatabase = clientname ; 
    strConnection.Format(
        "Provider=sqloledb;Data Source=%s;Initial Catalog=%s;" + 
        "Integrated Security=SSPI;",
        csServer,csDatabase);
            

    CString Query = 
        "INSERT INTO [DEV3].[dbo].[DEV303]([longdate]," + 
        "[longtime],[ColeDateTime],[TEXT] )" + 
        " VALUES ('1921212','121212','2007/3/30 17:08:44','Some Text ' )";
    try
    {
        if(m_pDb.Open(strConnection))
        {    
            CADOCommand pCmd( &m_pDb , Query , CADOCommand::typeCmdText);
            m_pRs = CADORecordset(&m_pDb);
            m_pRs.Execute( &pCmd );
        }
    }
    catch (CADOException _e)
    {
        _e.GetErrorMessage();
        AfxMessageBox(_e.GetErrorMessage());
    }
}

SaveinSQL

void ServerRecev::SaveinSQL(char * recevData )
{
    //Some Code to do with recevData

    //////////////////////////Adding in Database/////////////////////////
    CADORecordset m_pRs;
    CADODatabase m_pDb;
    CString strConnection ;
    CString csUser = "sa";
    CString csPwd = "";
    CString csServer = "(local)";
    CString csDatabase = clientname ; 
    strConnection.Format(
        "Provider=sqloledb;Data Source=%s;Initial Catalog=%s;" + 
        "Integrated Security=SSPI;",
    csServer,csDatabase);
    try
    {
        if(m_pDb.Open(strConnection))
        {    
            m_pRs = CADORecordset(&m_pDb);
          //if(m_pRs.IsOpen() == TRUE)
            {
                CString strtable = clientname + Date;
                if(m_pRs.Open( strtable , CADORecordset::openTable))
                {
                  //if(m_pRs.IsOpen() ==TRUE)
                    {
                        m_pRs.AddNew();
                        m_pRs.SetFieldValue(0, longdate );
                        m_pRs.SetFieldValue(1 , longtime );
                        m_pRs.SetFieldValue(2 , oledt);
                        m_pRs.SetFieldValue(3 , strdata );
                        m_pRs.Update();
                    }
                  //m_pRs.Close();
                }
              //m_pDb.Close();
            }
        }
    }
    catch (CADOException _e)
    {
         _e.GetErrorMessage();
         AfxMessageBox(_e.GetErrorMessage());
    }
}

Points of interest

I wrote two functions. The first, SaveinSQLInsert, uses the INSERT command and executes it via the ADO library. The second, SaveinSQL, uses the Addnew command to enter data into the database. The thread that held these functions was running at 20 times per second. Data communication is synchronous, but data registration to the database is asynchronous, as it checks that the data is not duplicated. I recommend using the INSERT command to insert data in the database and execute it through the CRecordset class' execute command.

The pictures show how CPU and memory usage have been affected by these two functions. The upper picture shows the INSERT command and the lower picture shows the AddNew command to add the data. The percentage of CPU usage can be compared visually as well as numerically in both cases. I have intentionally run both processes in processor1, as I have dual core technology and a good comparison can be done on single processor machines. My machine has the following specifications: Dell Precision PWS690 Intel Xeon CPU 5130 @ 2.00GHz and 2.00GHz 2.00GBRAM.

Libraries used: ado2.h and ado2.cpp Version 2.20 by Carlos Antollini. This piece of code was written at Intelligent Media in London, UK.

History

• 28 June, 2007 -- Original version posted
• 29 June, 2007 -- Source download added to article