oci读取blob字段的效率问题
cmoth 2014-07-21 11:30:21 如下代码:
geoCursor.Open();
geoCursor.Prepare(strGeoSQL); // select id, st_asbinary(shape) from a_points
geoCursor.DefineByPos(1, dfnhp[0], &m_OnlyIDs[0], 4, (void *)&sOnlyIDIndicator, SQLT_INT);
geoCursor.DefineByPos(2, dfnhp[1], &m_GeometryArray[0], -1, (void *)&sGeoIndicator, SQLT_BLOB);
OCIDefineArrayOfStruct(dfnhp[0], m_pConnection->errhp, 4, 0, 0, 0);
OCIDefineArrayOfStruct(dfnhp[1], m_pConnection->errhp, sizeof(OCILobLocator*), 0, 0, 0);
geoCursor.Execute(nLoadStep); //!<---------------nLoadStep是10000,此处特别耗时
while ( nLoadedCount < m_slRecordCount )
{
try
{
TRACE0("\n");
for ( int i = 0; i<nLoadStep; i++ )
{
ub4 loblength = 0;
OCILobGetLength(m_pConnection->svchp, m_pConnection->errhp,
m_GeometryArray[i], &loblength);
if ( loblength > m_nBuffSize )
{
m_nBuffSize = loblength;
if ( m_pBuff ) delete m_pBuff;
m_pBuff = new char[m_nBuffSize];
}
ub4 amtsize = loblength;
OCILobRead(m_pConnection->svchp, m_pConnection->errhp,
m_GeometryArray[i], &amtsize, 1, (dvoid *) m_pBuff,
loblength, (dvoid *)0,OCICallbackLobRead(),(ub2) 0, (ub1) SQLCS_IMPLICIT); //!<-----这里也比较耗时
//对ST_GEOMETRY数据进行处理
TRACE1("%d,", m_OnlyIDs[i]);
}
nLoadedCount += nLoadStep;
if ( m_slRecordCount - nLoadedCount < nLoadStep )
{
nLoadStep = m_slRecordCount-nLoadedCount;
}
geoCursor.Fetch(nLoadStep);
}
catch (...)
{
break;
}
}
BLOB字段存储的内容不大,每条都几十字节。
如果不取BLOB字段,单独ID字段的效率很高,40w条记录,每次读取10000条,一共耗时不到0.5s,如果取BLOB字段的话,需要15分钟左右,这效率差的也太多了。
不知道以上还有那些地方可以做优化?