ADO and UpdateBatch with SQL Server

[macgowan]

Hi ...

I have read a lot on codeguru about ADO and UpdateBatch, but there seems to be no conclusions drawn on implementing UpdateBatch and AddNew() with parameters (AddNew(vFieldList, vValueList));

My goal is to increase the performace of reduntant inserts into a table. Example: I am loading a table with about 100K - 200K rows.

I have read about the use of the Prepare Property in the Command Object (See below). Does the use of AddNew(vFieldList, vValueList) / UpdateBatch() also cache the SQL statement to improve performace as the Command Object does?

Stored Procedures - Stored Procedure vs Transact-SQL Performance
by Wrox Press
http://www.developerfusion.com/show/91/2/

Stored procedures are more efficient than SQL statements, when executed from a program. The reason why this is true is that when the stored procedure is created and saved it is compiled. During the compilation process, SQL Server creates and optimizes an execution plan for the stored procedure. Also, once a stored procedure is executed, it is placed in SQL Server cache. This means that subsequent executions are executed from
cache, which also provides improved performance.

SQL statements executed from your programs, however, are not as efficient. Each time the SQL statements are executed by your program, SQL Server must compile and optimize them before they actually get executed. While this performance decrease is not that noticeable with small numbers of SQL statements, or on your personal computer running the Desktop edition of SQL Server, it becomes more evident on large scale SQL Server systems with multiple databases and hundreds of concurrent clients.

If you must execute SQL statements from your program, and you are executing the same SQL statements in a loop, then ADO can help. The ADO Command object provides a property called Prepared which, when set to True, will instruct SQL Server to compile and save a copy of your SQL statements in cache. After you execute the SQL statements the first time, subsequent executions are executed from the SQL Server cache. While this
will help subsequent executions of your SQL statements, you still take a performance hit on the first execution.


Below is a sample of what I am doing with ADO. This code should add three rows to the table. The RecordId field is being incremented in the for loop. All the other data will be the same for each row.

Result: The RecordId is 1000 for all rows and the StationID and and Frequency are not being written at all. Anyone see a problem with this code? Am I passing the vFieldList and vValueList into the AddNew() wrong?

Also when I change the Open() parameter from adLockOptimistic to adLockBatchOptimistic when I get the following error on the second pass of AddNew():

"Row handles must all be released before new ones can be obtained."


try
{
m_pConnection.CreateInstance(__uuidof(Connection));
m_pConnection->Open("DSN=SQLDEV;UID=cmacgowan;PWD=cmacgowan","","",-1);

m_pRecordset.CreateInstance(__uuidof(Recordset));

m_pRecordset->Open("SELECT * FROM dbo.DICastRawData",
m_pConnection.GetInterfacePtr(),
adOpenDynamic,
adLockOptimistic,
adCmdText);


VARIANT vFieldList[25];
VARIANT vValueList[25];

int nFieldIndex = 0;
int nValueIndex = 0;

// Setup the fields
vFieldList[nFieldIndex].vt = VT_BSTR;
vFieldList[nFieldIndex].bstrVal = ::SysAllocString(L"RecordId");
nFieldIndex++;

vFieldList[nFieldIndex].vt = VT_BSTR;
vFieldList[nFieldIndex].bstrVal = ::SysAllocString(L"StationID");
nFieldIndex++;

vFieldList[nFieldIndex].vt = VT_BSTR;
vFieldList[nFieldIndex].bstrVal = ::SysAllocString(L"Frequency");
nFieldIndex++;

for(i = 0; i < 3; i++)
{
// Setup the data
nValueIndex = 0;
vValueList[nValueIndex].vt = VT_UI4;
vValueList[nValueIndex].lVal = 1000 + nValueIndex;
nValueIndex++;

vValueList[nValueIndex].vt = VT_BSTR;
vValueList[nValueIndex].bstrVal = ::SysAllocString(L"KSTP");
nFieldIndex++;

vValueList[nValueIndex].vt = VT_R8;
vValueList[nValueIndex].dblVal = 102.003;
nValueIndex++;

// Add the record to the recordset
m_pRecordset->AddNew(vFieldList, vValueList);

}

m_pRecordset->UpdateBatch(adAffectAll);
m_pRecordset->Close();

}
catch(_com_error *e)
{
CString Error = e->ErrorMessage();
AfxMessageBox(e->ErrorMessage());
}



Any comments are appreciated!
Thanks
Chris Macgowan

[XiXiX]

Hi,

This post is old, and I don't even know if my reply gets to the right person but I'm experiencing the same ADO problem with AddNew and UpdateBatch: of the f.ex. 5 records I recuperate from one database and want to insert into another, only the last record ends up where it should, the other 4 are gone...

Did you ever find a solution for your problem?

Tnx!

K

[macgowan]

Hi ...

Yes I was surprized to see a reply this this OLD article. I never did get this to work. I have read that ADO.NET supports the disconnected recordset better than ADO 2.8 ...

Good luck,
Chris

[macgowan]

Boy oh Boy -
It's been some time since we talked about ADO Disconnected Recordset. I have returned to this issue and still have some problems.

In the code below only the last value is updated (222). I'm not sure if the MoveNext and Update commands are working with the disconnected recordset.

Also I could not get the insert to work using the AddNew() / Update()

Here is some code to chew on.
Comments are welcome.

    // Define ADO connection pointers
    _ConnectionPtr pConnection = NULL;
    _RecordsetPtr  pRecordset = NULL;

    try
    {

        // When we open the application we will open the ADO connection
        pConnection.CreateInstance(__uuidof(Connection));

        // Replace Data Source value with your server name.
        bstr_t bstrConnect("Provider='sqloledb';Data Source='BVLSQLTEST1';"
                           "Initial Catalog='AlphaNumericData';"
                           "User Id=cmacgowan;Password=cmacgowan");

        // Open the ado connection
        pConnection->Open(bstrConnect,"","",adConnectUnspecified);

        // Create an instance of the database
        pRecordset.CreateInstance(__uuidof(Recordset));
        
        // Select the correct sql string.  Note that we are creating an
        // empty string by doing a select on the primary key.  We are only
        // doing inserts and we do not want to bring data back from the
        // server

        pRecordset->CursorLocation = adUseClient;

        pRecordset->PutRefActiveConnection(pConnection);
        pRecordset->LockType = adLockBatchOptimistic;

        csSQL = "SELECT * FROM dbo.MacgowanDCR1H";

        // pRecordset->Open(csSQL.AllocSysString(), vNull, adOpenStatic, adLockOptimistic, -1);
        pRecordset->Open(csSQL.AllocSysString(), 
                         pConnection.GetInterfacePtr(), 
                         adOpenForwardOnly, 
                         adLockOptimistic, 
                         -1);

        // spRS->Open(OLESTR("select * from Table1"),
        // spConn.GetInterfacePtr(),
        // adOpenForwardOnly, adLockBatchOptimistic, -1);

        // Move to the top oc the recordset
        // pRecordset->MoveFirst(); 

        // Disassociate the connection from the recordset.
        pRecordset->PutRefActiveConnection(NULL);

        // Change some data
        sval=((long) 111); 
        pRecordset->Fields->GetItem(L"DIcastId")->PutValue(sval);

        pRecordset->Update(); 
        pRecordset->MoveNext(); 

        // Change some data
        sval=((long) 222); 
        pRecordset->Fields->GetItem(L"DIcastId")->PutValue(sval);



        /* 

        // Scroll through all the records and update the DIcastId
        while (pRecordset->GetadoEOF() == VARIANT_FALSE)
        { 
            // Change some data
            sval=((long) 888); 
            pRecordset->Fields->GetItem(L"DIcastId")->PutValue(sval);

            // Update 
            //pRecordset->Update();
            pRecordset->MoveNext(); 
        }

        */


        // Now we will add some new recorrds
        int nCount = 0; 
        int i = 0; 

        for (i=0; i < nCount; i++) 
        { 

            // insert a new row
            pRecordset->AddNew();

            // Get the DIcastId data and set the recordset
            // strTemp = pMxTextParse->GetParseData("DIcastId"); 
            // nTempLong = atol(strTemp.c_str());
            sval=((long) i); 
            pRecordset->Fields->GetItem(L"DIcastId")->PutValue(sval);

            // Get the DIcastId data and set the recordset
            strTemp = (string)"ABCD"; 
            sval.SetString(strTemp.c_str()); 
            pRecordset->Fields->GetItem(L"StationID")->PutValue(sval);

            // Get the StationLatitude data and set the recordset
            strTemp = (string)"232.645"; 
            nTempDouble = atof(strTemp.c_str());
            sval=((double) nTempDouble); 
            pRecordset->Fields->GetItem(L"Latitude")->PutValue(sval);

            // Get the StationLatitude data and set the recordset
            strTemp = (string)"123.59"; 
            nTempDouble = atof(strTemp.c_str());
            sval=((double) nTempDouble); 
            pRecordset->Fields->GetItem(L"Longitude")->PutValue(sval);

            // COleDateTime is a wrapper for VARIANT's DATE type. COleVariant is 
            // a wrapper for VARIANTs themselves. If you need to create a 
            // variant, you can say:
            COleDateTime oledtCurrentDate2 = COleDateTime::GetCurrentTime();

            // Convert the OleDateTime to the varient
            COleVariant vCurrentDateTime2(oledtCurrentDate2);

            // Set the ModelRunDateTime and ValidDateTime 
            pRecordset->Fields->GetItem(L"ModelRunDateTime")->PutValue(vCurrentDateTime2);
            pRecordset->Fields->GetItem(L"ValidDateTime")->PutValue(vCurrentDateTime2);

            // Get the Temperature data and set the recordset
            strTemp = (string)"54"; 
            nTempInt = atoi(strTemp.c_str());
            sval=((short) nTempInt); 
            pRecordset->Fields->GetItem(L"Temperature")->PutValue(sval);

            // Get the WindDirection data and set the recordset
            strTemp = (string)"180"; 
            nTempInt = atoi(strTemp.c_str());
            sval=((short) nTempInt); 
            pRecordset->Fields->GetItem(L"WDIR")->PutValue(sval);

            // Get the WindSpeed data and set the recordset
            strTemp = (string)"900"; 
            nTempInt = atoi(strTemp.c_str());
            sval=((short) nTempInt); 
            pRecordset->Fields->GetItem(L"WSPD")->PutValue(sval);

            // Update 
            pRecordset->Update();

        }


        // Re-connect.
        pRecordset->PutRefActiveConnection(pConnection);
        
        // Send updates.
        pRecordset->UpdateBatch(adAffectAll);
        
        pRecordset->Close();
        pConnection->Close();

        // End of test 

	}
	catch(_com_error *e)
	{
		CString Error = e->ErrorMessage();
		AfxMessageBox(e->ErrorMessage());
        pView->WriteLog("Error processing TestDatabase()."); 
	}
	catch(...)
	{
        csMessage = "Undefined exception handled.  Error message details \n\n"; 

        hResult = GetAdoErrorMessage(pConnection, 
                                     &csErrorMessage);

        csMessage += csErrorMessage; 
        csMessage += "\nmethod: CTestMeteorlogixApp::TestDatabaseUpdateBatch3()"; 

		AfxMessageBox(csMessage);

	}

    pView->WriteLog("End TestDatabaseUpdateBatch3."); 

}

[XiXiX]

hey mac,

just to assure you that i'm still listening...

i dismissed this update(batch)() approach and now use the row by row way to update or insert

it's been a while, so i don't exactly remember anymore what i tried and what not, but i didn't find anything to help you on your quest...

K