|
Home > Archive > SQL Anywhere database > August 2005 > Slowness storing BLOBs in ASA 8.03 - 9.02
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Slowness storing BLOBs in ASA 8.03 - 9.02
|
|
|
| Hello,
We are moving from ASA 8.02 to more recent versions and
discovered a problem with saving binary fields to the
database with versions 8.03 and 9.02. On 7.xx, 8.00 and 8.02
it works fine. Please help, i got completely lost...
Application - Win32, VC++, MFC.
Connection - ODBC.
Standard MFC classes used - CDatabase and
CRecordset-derived CDataStorage.
The 30-40 second delay is happening on closing the recordset
after updating the binary field in the table, in
CRecordset::OnClose(
).
Here is more details on implementation and timing:
The table definition:
create table DRAW
(
DrawID integer not
null default -1,
DrawName char(64) not
null default '?',
DrawData long binary not
null default '0',
primary key (DrawID)
);
Recordset:
class CDataStorage : public CRecordset
{
....
long m_DrawID;
CString m_DrawName;
CLongBinary m_DrawDa
ta;
....
}
the storing code:
CDataStorage rs(pDatabase);
rs.m_strSort="";
rs.m_strFilter.Format("[DRAWID]='%d'",tempID);
pDatabase->BeginTrans();
rs.Open();
if(rs.IsEOF()) //if not exist, create new.
{
rs.AddNew();
rs.m_DrawID=tempID;
rs.m_DrawName=strName;
rs.Update();
rs.Close();
rs.m_strFilter= "DrawName = '" + strName + "'";
rs.Open();
rs.Requery();
}
rs.Edit();
GlobalFree(rs.m_DrawData.m_hData);
rs.m_DrawData.m_hData =
GlobalAlloc(GMEM_MOV
EABLE|GMEM_NODISCARD
, 0);
//reallocation of memory(m_hData) goes here, approximate
size is 1 mb
rs.SetFieldDirty(&rs.m_DrawData);
rs.SetFieldNull(&rs.m_DrawData, FALSE);
rs.m_DrawData.m_dwDataLength = statstg.cbSize.LowPart;
rs.Update();
pDatabase->CommitTrans();
rs.Close();
Tracing of time between operations:
Before Open() - 11:36:57
GlobalAlloc() finished - 11:36:57
SetDirty, SetNULL finished - 11:36:58
Update finished - 11:37:00
comit finished - 11:37:00
CLOSE finished - 11:37:34
ALL FINISHED - 11:37:34
BeginTrans() and CommitTrans() doesnot change anything, were
included for test. With smaller BLOBS it goes really quick,
except the case when the previous big "slow" blob is
owerwritten with small one. In that case it also goes
extremely slow, but after big blob was replaced with small
one (on next attempt to save), it saves quickly.
Thank you for any info,
Igor
| |
|
| In more detail, the execution of SQLFreeStmt in
CRecordset::Close() is lasting that long.
AFX_SQL_SYNC(::SQLFr
eeStmt(m_hstmt, SQL_DROP))
|
|
|
|
|