SQLite建立索引后插入变慢问题
这个是看网上牛人写的CppSQLiteDemo的例子，小弟稍稍改了一下发现建立索引后速度很慢，但是不建立索引就很快，请高手释疑。下楼为cppsqlite3.h和cpp文件
- C/C++ code
////////////////////////////////////////////////////////////////////////////////// CppSQLite3 - A C++ wrapper around the SQLite3 embedded database library.//// Copyright (c) 2004 Rob Groves. All Rights Reserved. rob.groves@btinternet.com// // Permission to use, copy, modify, and distribute this software and its// documentation for any purpose, without fee, and without a written// agreement, is hereby granted, provided that the above copyright notice, // this paragraph and the following two paragraphs appear in all copies, // modifications, and distributions.//// IN NO EVENT SHALL THE AUTHOR BE LIABLE TO ANY PARTY FOR DIRECT,// INDIRECT, SPECIAL, INCIDENTAL, OR CONSEQUENTIAL DAMAGES, INCLUDING LOST// PROFITS, ARISING OUT OF THE USE OF THIS SOFTWARE AND ITS DOCUMENTATION,// EVEN IF THE AUTHOR HAS BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.//// THE AUTHOR SPECIFICALLY DISCLAIMS ANY WARRANTIES, INCLUDING, BUT NOT// LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A// PARTICULAR PURPOSE. THE SOFTWARE AND ACCOMPANYING DOCUMENTATION, IF// ANY, PROVIDED HEREUNDER IS PROVIDED "AS IS". THE AUTHOR HAS NO OBLIGATION// TO PROVIDE MAINTENANCE, SUPPORT, UPDATES, ENHANCEMENTS, OR MODIFICATIONS.//// V3.0 03/08/2004 -Initial Version for sqlite3//// V3.1 16/09/2004 -Implemented getXXXXField using sqlite3 functions// -Added CppSQLiteDB3::tableExists()////////////////////////////////////////////////////////////////////////////////#include "CppSQLite3.h"#include <time.h>#include <iostream>using namespace std;int main(int argc, char** argv){ try { CppSQLite3DB db; db.open("E:\\test.db"); int nRowsToCreate(20000000); int i=0; db.execDML("create table CALLHISTORY(CalledNumber varchar(20) ,CallingNumber varchar(20) ,TimeStamp datetime)"); [color=#FF0000]db.execDML("create index hisindex on CALLHISTORY(CalledNumber)"); //此处加上索引后插入速度变慢，刚开始几十万的时候还可以，后来1万条要几十秒。但是没有索引就一直都很快[/color] cout << " rows please wait..." << endl; int times = 0; srand(time(NULL)); while(times <1) //这里本来想测试多次的 { CppSQLite3Statement stmt = db.compileStatement("insert into CALLHISTORY values (?, ?,?);"); db.execDML("begin transaction;"); for (i = 1; i <= nRowsToCreate; i++) { if( 0 == i %10000) { cout << " Already insert" << i << " lines" << endl; db.execDML("commit transaction;"); db.execDML("begin transaction;"); } int j =rand()%500000; time_t nowTime = time(NULL); struct tm* pTm=NULL; pTm = localtime(&nowTime); char bufTime[32]; memset(bufTime,'\0',sizeof(bufTime)); sprintf(bufTime,"%04d-%02d-%02d %02d:%02d:%02d",pTm->tm_year+1900,pTm->tm_mon+1, pTm->tm_mday,pTm->tm_hour,pTm->tm_min,pTm->tm_isdst); char buf[16]; sprintf(buf, "lys%06d", j); stmt.bind(1, j); stmt.bind(2, buf); stmt.bind(3,bufTime); stmt.execDML(); stmt.reset(); } db.execDML("commit transaction;"); } } catch (CppSQLite3Exception& e) { cout << "error code =" << e.errorCode() << "msg = " << e.errorMessage()<< endl; } catch(...) { cout <<"Unknown error"; } return 0;}
[解决办法]
先不管你的程序，直接在SQLITE中测试看看速度如何。
你添加了哪些索引，索引肯定会影响插入，更新，删除的速度，但不至于有显著的影响。
[解决办法]
建立索引，很影响插入性能。
[解决办法]