Click here to Skip to main content
15,867,308 members
Articles / Database Development / SQL Server
Article

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

Rate me:
Please Sign up or sign in to vote.
2.67/5 (6 votes)
29 Jun 2007CPOL2 min read 29.8K   238   15   4
An article on efficiently adding rows in a database

Sample Image - maximum width is 600 pixels

Sample Image - maximum width is 600 pixels

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
//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

C++
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

C++
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

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Software Developer (Senior)
United Kingdom United Kingdom
Love C++, SQL, C#, directX, COM
design Distributed Applications

BrainBench C# 4.11/5.00, am in top 7% test taker

working in SunGard

MSc in Computer Graphics Programming from University of Hull
BS in Electronics Engineering from GIK Institute, Pakistan

Likes Psychology

Comments and Discussions

 
QuestionAre you missing something? Pin
fwsouthern28-Jun-07 9:44
fwsouthern28-Jun-07 9:44 
AnswerRe: Are you missing something? Pin
kazim bhai29-Jun-07 0:56
kazim bhai29-Jun-07 0:56 
GeneralRe: Are you missing something? Pin
fwsouthern29-Jun-07 1:48
fwsouthern29-Jun-07 1:48 
AnswerRe: Are you missing something? Pin
kazim bhai29-Jun-07 5:13
kazim bhai29-Jun-07 5:13 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.