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