Home » RDBMS Server » Performance Tuning » Performance difference BOLB vs. LONG RAW
Performance difference BOLB vs. LONG RAW [message #109975] Wed, 02 March 2005 08:55
AllanJones
Messages: 1
Registered: March 2005
Location: uk
Junior Member
Hi
Has anyone experienced a significant difference in performance between insert with BLOBs and LONG RAW columns.

I'm using Oracle 10g on Windows 2000. Oracle ODBC driver 10.01.00.02. 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.

I suspecec that the difference may be due to Oracle returning BLOB locators for each of the blobs that I insert. Is it possible to turn off this behaviour?

Any ideas?

Test code is below;

_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();
------
Previous Topic: on consumption of resources
Next Topic: Indexes not working after analyzing tables
Goto Forum:
  


Current Time: Sun Sep 27 21:11:10 CDT 2020