Sqlite3常用的插入方法及性能测试

extern "C" 2 { 3 #include "sqlite3.h" 4 }; 5 6 #include<sstream> 7 #include <string> 8 #include <iostream> 9 #include <stdlib.h> 10 #include <ctime> 11 #include<windows.h> 12 13 14 #define MAX_TEST_COUNT 200 15 16 using namespace std; 17 18 19 int main() 20 { 21 char cmdCreatTable[256] = "create table SqliteTest (id integer , x integer , y integer, weight real)" ; 22 sqlite3* db = NULL; 23 char * errorMessage = NULL; 24 int iResult = sqlite3_open("SqliteTest.db", &db); 25 do 26 { 27 if (SQLITE_OK != iResult) 28 { 29 cout<<"创建InsertTest.db文件失败"<<endl; 30 break; 31 } 32 33 sqlite3_exec(db,"drop table if exists SqliteTest",0,0,0); 34 35 iResult = sqlite3_exec(db, cmdCreatTable, NULL, NULL, &errorMessage); 36 if (SQLITE_OK != iResult) 37 { 38 cout<<"创建表SqliteTest失败"<<endl; 39 break; 40 } 41 DWORD timeStart; 42 DWORD timeStop; 43 timeStart = GetTickCount(); 44 for (int i = 0; i< MAX_TEST_COUNT; ++i) 45 { 46 stringstream ssm; 47 ssm<<"insert into SqliteTest values("<<i<<","<<i*2<<","<<i/2<<","<<i*i<<")"; 48 iResult = sqlite3_exec(db,ssm.str().c_str(),0,0,0); 49 } 50 timeStop = GetTickCount(); 51 cout<< "直接Insert"<<MAX_TEST_COUNT<<"条数据操作执行时间" << timeStart<<"结束时间:"<<timeStop<<"共耗时:"<<timeStop-timeStart<<"ms"<<endl; 52 53 timeStart = GetTickCount(); 54 sqlite3_exec(db,"PRAGMA synchronous = OFF; ",0,0,0); 55 for(int i = MAX_TEST_COUNT; i < MAX_TEST_COUNT*2; ++i) 56 { 57 stringstream ssm; 58 ssm<<"insert into SqliteTest values("<<i<<","<<i*2<<","<<i/2<<","<<i*i<<")"; 59 sqlite3_exec(db,ssm.str().c_str(),0,0,0); 60 } 61 timeStop = GetTickCount(); 62 63 cout<< "同步写关闭+直接Insert"<<MAX_TEST_COUNT<<"条数据操作执行时间" << timeStart<<"结束时间:"<<timeStop<<"共耗时:"<<timeStop-timeStart<<"ms"<<endl; 64 65 66 timeStart = GetTickCount(); 67 sqlite3_exec(db,"PRAGMA synchronous = FULL; ",0,0,0); 68 sqlite3_exec(db,"begin;",0,0,0); 69 for(int i= MAX_TEST_COUNT*2; i< MAX_TEST_COUNT*3; ++i) 70 { 71 stringstream ssm; 72 ssm<<"insert into SqliteTest values("<<i<<","<<i*2<<","<<i/2<<","<<i*i<<")"; 73 sqlite3_exec(db,ssm.str().c_str(),0,0,0); 74 } 75 sqlite3_exec(db,"commit;",0,0,0); 76 timeStop = GetTickCount(); 77 cout<< "事务Insert"<<MAX_TEST_COUNT<<"条数据操作执行时间"<< timeStart<<"结束时间:"<<timeStop<<"共耗时:"<<timeStop-timeStart<<"ms"<<endl; 78 79 80 timeStart = GetTickCount(); 81 sqlite3_exec(db,"PRAGMA synchronous = OFF; ",0,0,0); 82 sqlite3_exec(db,"begin;",0,0,0); 83 for(int i = MAX_TEST_COUNT*3; i < MAX_TEST_COUNT*4; ++i) 84 { 85 stringstream ssm; 86 ssm<<"insert into SqliteTest values("<<i<<","<<i*2<<","<<i/2<<","<<i*i<<")"; 87 sqlite3_exec(db,ssm.str().c_str(),0,0,0); 88 } 89 sqlite3_exec(db,"commit;",0,0,0); 90 timeStop = GetTickCount(); 91 92 cout<< "事务+同步写关闭Insert"<<MAX_TEST_COUNT<<"条数据操作执行时间" << timeStart<<"结束时间:"<<timeStop<<"共耗时:"<<timeStop-timeStart<<"ms"<<endl; 93 94 timeStart = GetTickCount(); 95 //sqlite3_exec(db,"PRAGMA synchronous = FULL; ",0,0,0); 96 sqlite3_exec(db,"begin;",0,0,0); 97 sqlite3_stmt *stmt; 98 const char* sql = "insert into SqliteTest values(?,?,?,?)"; 99 sqlite3_prepare(db,sql,strlen(sql),&stmt,0); 100 for(int i = MAX_TEST_COUNT*4; i<MAX_TEST_COUNT*5; ++i) 101 { 102 sqlite3_reset(stmt); 103 sqlite3_bind_int(stmt,1,i); 104 sqlite3_bind_int(stmt,2,i*2); 105 sqlite3_bind_int(stmt,3,i/2); 106 sqlite3_bind_double(stmt,4,i*i); 107 sqlite3_step(stmt); 108 } 109 sqlite3_finalize(stmt); 110 sqlite3_exec(db,"commit;",0,0,0); 111 112 timeStop = GetTickCount(); 113 cout<< "事务+执行准备+同步写关闭Insert"<<MAX_TEST_COUNT<<"条数据操作执行时间:"<< timeStart<<"结束时间:"<<timeStop<<"共耗时:"<<timeStop-timeStart<<"ms"<<endl; 114 115 116 }while(0); 117 118 cout<<"插入测试结束"<<endl; 119 Sleep(2000); 120 sqlite3_close(db); 121 system("pause"); 122 123 }

 

Sqlite3常用的插入方法及性能测试

标签：