Re: Performance differences, BLOB vs LONG RAW

DA Morgan <damorgan_at_x.washington.edu> wrote in message news:<1108998931.908925_at_yasure>...

> I don't know if it is expected because I don't know what version you
> are using or what you are storing. I've not seen enough of a delta
> to make me investigate further.

I'm using Oracle 10g on Windows 2000. Oracle ODBC driver 10.01.00.02. I've spent a few minutes today settings up a simple test. I have two tables JUNK1 and JUNK2.

JUNK1 contains;
 COL1 NUMBER
 COL2 NUMBER
 COL3 BLOB JUNK2 contains
 COL1 NUMBER
 COL2 NUMBER
 COL3 LONG RAW Apart from the different data type for COL3, both tables are identical. I have listed below a bit of code which uses ADO to insert 20000 rows. For JUNK1 this runs in about 31 seconds. If I change the command string so it inserts into JUNK2 the time is around 15 seconds.  I've tried the same thing using a recordset to do the insert and have seen similar difference in performance.

Any ideas?

   	_ConnectionPtr     pConnection    = NULL;
	_CommandPtr	   pCommand1      = NULL;
	_CommandPtr	   pCommand2      = NULL;
	ParametersPtr  pParameters        = NULL;
	_variant_t  vtEmpty (DISP_E_PARAMNOTFOUND, VT_ERROR);

	HRESULT hr = CoInitialize (NULL);

	pConnection.CreateInstance(__uuidof(Connection));
	hr = pConnection->Open(bstrConnectionString,"","",
adConnectUnspecified);
	CheckHR(hr);


	// Create the command
	hr = pCommand1.CreateInstance(__uuidof(Command));
	pCommand1->putref_ActiveConnection(pConnection);
	hr = pCommand1->put_CommandText(_bstr_t(_T("INSERT INTO DBO.JUNK1
(COL1, COL2, COL3) VALUES(?,?,?)")));
	hr = pCommand1->put_CommandType(adCmdText);

	hr = pCommand1->get_Parameters(&pParameters);

	long lTrans = pConnection->BeginTrans();

	const int nBatchSize = 1000;

	// Do 20000 inserts
	for (int n = 0; n < 20000; n++)
	{
		VARIANT varEmpty;
		varEmpty.vt = VT_NULL;

		// Put some dummy data into the first two columns
		_ParameterPtr pParam;
		hr = pParameters->get_Item(variant_t(0l), &pParam);  //col1
		hr = pParam->put_Value(variant_t(long(n)));
		hr = pParameters->get_Item(variant_t(1l), &pParam);  // col2
		hr = pParam->put_Value(variant_t(long(666)));

		// Put some dummy data into the binary data column
		const int nBlobSize = 200; // number of bytes
		VARIANT vBlob;
		vBlob.vt = VT_ARRAY|VT_UI1;
		vBlob.parray = ::SafeArrayCreateVector(VT_UI1, 0, nBlobSize);

		BYTE* pArrayData = NULL;
		hr = ::SafeArrayAccessData(vBlob.parray, (void**) &pArrayData);
		if (SUCCEEDED(hr))
		{
			memset(pArrayData, n, nBlobSize);
			::SafeArrayUnaccessData(vBlob.parray);
		}

		hr = pParameters->get_Item(variant_t(2l), &pParam);  // col3
		hr = pParam->put_Value(vBlob);//);  varEmpty//

		variant_t vtRecordsAffected;
		VariantInit(&vtRecordsAffected);
		long lOptions = adExecuteNoRecords;

		hr = pCommand1->Execute(&vtRecordsAffected, &vtEmpty, lOptions);

		::SafeArrayDestroy(vBlob.parray);

		if (!(n % nBatchSize))
		{
			hr = pConnection->CommitTrans();
			lTrans = pConnection->BeginTrans();
		}

	}

	hr = pConnection->CommitTrans();