Click here to Skip to main content
Click here to Skip to main content
Go to top

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

, 29 Jun 2007
Rate this:
Please Sign up or sign in to vote.
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 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

License

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

Share

About the Author

kazim bhai
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? Pinmemberfwsouthern28-Jun-07 9:44 
AnswerRe: Are you missing something? Pinmemberkazim bhai29-Jun-07 0:56 
GeneralRe: Are you missing something? Pinmemberfwsouthern29-Jun-07 1:48 
AnswerRe: Are you missing something? Pinmemberkazim bhai29-Jun-07 5:13 

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

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

| Advertise | Privacy | Mobile
Web04 | 2.8.140916.1 | Last Updated 29 Jun 2007
Article Copyright 2007 by kazim bhai
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid