oci读取blob字段的效率问题
如下代码:
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分钟左右,这效率差的也太多了。
不知道以上还有那些地方可以做优化?
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分钟左右,这效率差的也太多了。
不知道以上还有那些地方可以做优化?
...全文
相关推荐
load blob clob
anysql 免费工具合集 为方便使用 留存
VC 中利用OO4O接口从Oracle数据库中读写图像.pdf
VC 中利用OO4O接口从Oracle数据库中读写图像.pdf
利用oci对Oracle数据库进行写图片
利用OCI接口,往Oracle数据库的BLOB里写入图片
oracle中blob 字段类型的应用
oracle中blob 字段类型的应用oracle中blob 字段类型的应用oracle中blob 字段类型的应用oracle中blob 字段类型的应用oracle中blob 字段类型的应用oracle中blob 字段类型的应用oracle中blob 字段类型的应用
blob_oracle.rar_blob_blob oracle_c++ oracle blob_oracle_oracle
oracle 的 blob大字段类型操作例子


