//讀二進位制資料的函式
BOOL OpenBinDataFile(BYTE **pBUf,UINT &len)
{
if (pBUf == NULL)
{
return FALSE;
}
std::ifstream fs;
fs.open(g_szBinDataPath,ios::binary);
if (!fs.is_open())
{
cout<<"File:"<<g_szBinDataPath<<"not Open"<<endl;
return FALSE;
}
fs.seekg(0,ios::end);
len = fs.tellg();
fs.seekg(0,ios::beg);
*pBUf = new BYTE[len];
if (NULL == *pBUf)
{
cout<<"記憶體分配不足"<<endl;
return FALSE;
}
fs.read((char*)(*pBUf),len);
fs.close();
return TRUE;
}
//#define _USE_TRANSACTION //是否使用事務
#define _DB_TABLE_COUNTS 10 //100張表
#define LOOPSCOUNT 20 //記錄數
#define TABLEFLAG 10 //第十張表寫入1000個記錄
#define RESETEST
BOOL SqlitePerfWithUnicode()//Unicode
{
cout<<"UNICODE TEST:"<<endl;
BYTE *pBuf=NULL;
UINT len = 0;
if (!OpenBinDataFile(&pBuf,len))
{
cout<<"開啟二進位制資料失敗"<<endl;
if (pBuf)
{
delete []pBuf;
}
return FALSE;
}
sqlite3 *db = NULL;
TCHAR *errMsg = NULL;
sqlite3_stmt *pstmt=NULL;
TCHAR *psql = NULL;
int nRet = -1,nRows=-1;
nRet = sqlite3_open16(L"F:\\UnicodeTest.db",&db);
if (nRet)
{
wcout<<L"無法開啟UnicodeTest資料庫:"<<sqlite3_errmsg16(db)<<endl;
//sqlite3_free(errMsg);
sqlite3_close(db);
cin.get();
return 1;
}
else
{
wcout<<L"成功開啟UnicodeTest.db"<<endl;
}
TCHAR szNum[100]={0};
TCHAR table[100]={0};
//std::vector<std::wstring> vecTableNames;
std::vector<std::wstring> vecID;
std::wstring wst;
std::vector<std::wstring> vecInsertStr,vecReadStr;
vecInsertStr.reserve(_DB_TABLE_COUNTS);
vecReadStr.reserve(_DB_TABLE_COUNTS);
for (int j=0;j<LOOPSCOUNT*10;++j)
{
_itot(j,szNum,10);
vecID.push_back(szNum);
}
for (int i=0;i<_DB_TABLE_COUNTS;++i)//建立100張表
{
_itot(i,szNum,10);
//vecID.push_back(szNum);//不夠
wst = L"unicodetest_";
wst += szNum;//表命名:unicodetest_x
//vecTableNames.push_back(wst);
psql = L"create table if not exists %s(id nchar(20) primary key,name blob)";
_snwprintf_s(table,_countof(table),_TRUNCATE,psql,wst.c_str());
//建立 _DB_TABLE_COUNTS 張表
psql = table;
sqlite3_prepare16(db,psql,-1,&pstmt,NULL);
sqlite3_step(pstmt);
sqlite3_reset(pstmt);
psql = L"insert into %s values(?,?);";
_snwprintf_s(table,_countof(table),_TRUNCATE,psql,wst.c_str());
//psql = table;
vecInsertStr.push_back(table);
psql = L"select id,name from %s;";
_snwprintf_s(table,_countof(table),_TRUNCATE,psql,wst.c_str());
//psql = table;
vecReadStr.push_back(table);
}
//psql = L"create table if not exists unicodetest(id nchar(20) primary key,name blob)";
//sqlite3_prepare16(db,psql,-1,&pstmt,NULL);
//sqlite3_step(pstmt);
//sqlite3_reset(pstmt);
DWORD dwBegin = GetTickCount();
#ifdef _USE_TRANSACTION
sqlite3_exec(db,"begin transaction;",NULL,NULL,NULL);
#endif
for(int j=1;j<_DB_TABLE_COUNTS;++j)
{
psql = const_cast<TCHAR*>(vecInsertStr[j].c_str());
#ifdef RESETEST
sqlite3_prepare16(db,psql,-1,&pstmt,NULL);
#endif
for (int i=0;i<LOOPSCOUNT;++i)//寫
{
#ifndef RESETEST
sqlite3_prepare16(db,psql,-1,&pstmt,NULL);
#endif
sqlite3_bind_text16(pstmt,1,vecID[i].c_str(),-1,NULL);
sqlite3_bind_blob(pstmt,2,pBuf,len,NULL);
sqlite3_step(pstmt);
#ifdef RESETEST
sqlite3_reset(pstmt);
#else
sqlite3_finalize(pstmt);
#endif
}
}
psql = const_cast<TCHAR*>(vecInsertStr[0].c_str());//id為"0"的表寫入1000個記錄
for (int i=0;i<LOOPSCOUNT*10;++i)//寫
{
sqlite3_prepare16(db,psql,-1,&pstmt,NULL);
sqlite3_bind_text16(pstmt,1,vecID[i].c_str(),-1,NULL);//這裡也相應的擴大
sqlite3_bind_blob(pstmt,2,pBuf,len,NULL);
sqlite3_step(pstmt);
sqlite3_reset(pstmt);
}
#ifdef _USE_TRANSACTION
sqlite3_exec(db,"commit transaction;",NULL,NULL,NULL);
#endif
DWORD dwEnd = GetTickCount();
cout<<"共有 "<< _DB_TABLE_COUNTS <<" 張表,每張表"<<"插入 "<<LOOPSCOUNT<<" 份記錄(每份大小["<<len<<"B].)"<<endl;
cout<<"其中最後一張表插入"<<LOOPSCOUNT*10<<"記錄"<<endl;
cout<<"總耗時:"<<dwEnd-dwBegin<<endl;
dwBegin = GetTickCount();
#ifdef _USE_TRANSACTION
sqlite3_exec(db,"begin transaction;",NULL,NULL,NULL);
#endif
for (int i=0;i<_DB_TABLE_COUNTS;++i)//讀
{
sqlite3_prepare16(db,vecReadStr[i].c_str(),-1,&pstmt,NULL);
nRet = sqlite3_step(pstmt);
while(nRet == SQLITE_ROW)
{
sqlite3_column_text16(pstmt,0);
sqlite3_column_blob(pstmt,1);//資料先不讀出
nRet = sqlite3_step(pstmt);
}
sqlite3_reset(pstmt);
}
#ifdef _USE_TRANSACTION
sqlite3_exec(db,"commit transaction;",NULL,NULL,NULL);
#endif
dwEnd = GetTickCount();
cout<<"依次讀 "<<_DB_TABLE_COUNTS<<" 張表內的 "<<LOOPSCOUNT<<"份記錄,總耗時:"<<dwEnd-dwBegin<<endl;
sqlite3_reset(pstmt);
sqlite3_finalize(pstmt);
sqlite3_close(db);
if (pBuf)
{
delete []pBuf;
}
//cin.get();
return TRUE;
}