Speed of retrieving a blob from Oracle using NextResultRow

ROOT
1

NextResultRow is extremely slow reading blobs. Is this a known problem, or am I doing something wrong?

Details:
On lxplus (lx32slc4) using either of the following ROOTSYS
ROOTSYS=/afs/cern.ch/sw/lcg/external/root/5.15.08/slc3_ia32_gcc323/root
ROOTSYS=/afs/cern.ch/sw/lcg/external/root/5.14.00c/slc3_ia32_gcc323/root
and oracle
ORACLE_HOME=$ORACLE_CERN/instantclient/10.2.0.3/slc3_ia32_gcc323

I find that trying to read back takes almost 3 minutes at the NextResultRow step.

std::string q1(“SELECT Profile_1,Profile_2,Profile_3,Profile_4 FROM DCOPS_PROFILES WHERE LINE_TIMESTAMP=”);

std::string q2(" AND DLC_INDEX=");
…

std::string query=q1+other+q2+mother;
pstmt = static_cast<TOracleStatement*> (db->Statement(query.c_str(),1) );
OK=pstmt->Process();
if(!OK) {std::cout<<"Failed to Process "<<query<<"\n";return 1;}
else{std::cout<<"Process "<<dc<<" OK\n";}
std::cout<<query<<"\n"<<std::flush;
pstmt->StoreResult();
std::cout<<"Between\n"<<std::flush;
time1 = time(0);
pstmt->NextResultRow();
time2 = time(0);
std::cout<<"After NextResultRow "<<time2-time1<<"\n"<<std::flush;
unsigned char prof[4][3072];
unsigned char* poof;
void* testa=0;
Long_t size=3072;
Int_t wh=0;
OK=pstmt->GetBinary(wh,testa,size);
if(!OK){std::cout<<"Failed to get binary 0\n"<<std::flush;return 1;}

etc

2

Hi

It is not normal, that GetNextResultRow takes so much time (several minutes). Actually, beside several buffers cleanups, this method contains just direct OCCI call (Oracle C++ Call Interface). And during this call no data is retrieved from database. Only when you use GetBinary(), buffers are created and filled from database.
Therefore, can you elaborate following:

  1. How long it takes for next calls of GetNextResultRow() method.
  2. Can you test your statement performance without WHERE condition.

Regards,
Sergey

3

Thank you for your quick reply!

After posting the question I tried collecting a group of rows and stepping through them, so in answer to your question 1:

Subsequent NextResultRow calls on the same statement are very fast–less than a second.

I will try to come up with a way of testing the second question–there’s a lot of data in the database.

Thanks,
jim bellinger

4

Hi, Jim

It can be the main reason for long delay - if you have a lot of data in database and tries to select with WHERE statement over the field, which is not indexed.

Sergey

5

That’s good to know. Both DLC_INDEX and LINE_TIMESTAMP are required to be non-NULL, but I gather that isn’t quite enough.

6

Hi

non-NULL property only helps you to avoid mistakes when you insert data into database. But it has nothing to do with indexing. Indexes should be created with extra SQL commands and may take as many disk space on server as data base itself.

Sergey

7

Fortunately I don’t seem to have the same problems with the fit information table, which is (for now) much smaller. It will grow at about 30 MB/day in full production. But if size is an issue, perhaps I should look at redesigning in order to take advantage of indexing.

The problematic table contains BLOBs of laser beam profile information, which we’re using to debug the peak fitting. In the final system we’ll only turn it on now and then for spot checking.