SQLite3开启事务和关闭事务模式下,性能测试对比
来源:互联网 发布:淘宝店铺怎么修改域名 编辑:程序博客网 时间:2024/05/16 23:45
最近学习了下SQLite数据库基本知识,想了解下这款小巧的数据库,性能到底怎样,于是写个性能测试程序,对 SQLite3 最新发布版(3.7.13)在Linux平台进行了测试。最后发现在开启事务模式和关闭事务模式(默认)下,性能测试结果相差近 1000 倍!
在测试的过程中,得出如下一些结论:
1、对于批量数据操作,建议采用事务模式,批量提交操作。
2、在提交事务之前,若程序发生异常,则所有插入、更新、删除等操作,都不会成功。
3、在操作数据库时,程序发生异常而中断操作,不会对现有的数据库造成任何破坏(SQLite3 的可靠性还不错!)。
一、测试环境
操作系统类型:linux-suse11 2.6.34.10-0.6-desktop, x86-32
操作系统内核:2.6.34.10-0.6-desktop
编译器版本 : gcc (SUSE Linux) 4.5.0 20100604 [gcc-4_5-branch revision 160292]
数据库版本 : SQLite 3.7.13
CPU 配置 :AMD Athlon(tm) II X2 240 Processor, 2812.890 MHZ, 1024 KB cache size,dual core
内存配置 :1017812 kB
二、编译方法:
1、开启事务模式编译: gcc -Wall -lpthread -ldl testsqlite3.c sqlite3.c -o main -DTRANSACTION_ON;
2、关闭事务模式编译: gcc -Wall -lpthread -ldl testsqlite3.c sqlite3.c -o main;
三、测试结果:
测试结果单位:微秒(μs), 1秒=1000毫秒=1000000微秒
1、关闭事务模式(默认)
操作类型 Counts TotalTime(us) AverageTime(us)
插入 1000 31307617 31307.000000
查询 1000 944442 944.000000
更新 1000 32264043 32264.000000
删除 1000 30638604 30638.000000
2、开启事务模式
操作类型 Counts TotalTime(us) AverageTime(us)
插入 1000 23326 23.000000
查询 1000 935739 935.000000
更新 1000 39197 39.000000
删除 1000 24394 24.000000
四、源代码:
- /******************************************************************************
- Copyright by Javacode007, All rights reserved!
- Filename : testsqlite3.c
- Author : Javacode007
- Date : 2012-8-11
- Version : 1.0
- Description : SQLite3 基本功能性能测试
- ******************************************************************************/
- #include "sqlite3.h"
- #include <stdlib.h>
- #include <stdio.h>
- #include <string.h>
- #include <sys/time.h>
- #include <time.h>
- #define PRINT_TRACE(fmt, args...) do {\
- fprintf(stderr, fmt" File:%s, Line:%04d\r\n",##args, __FILE__, __LINE__);\
- \
- }while(0)
- #ifdef TRANSACTION_ON
- #define START_TRANSACTION() sqlite3_exec(db, "begin transaction;", NULL, NULL, NULL)
- #define END_TRANSACTION() sqlite3_exec(db, "commit transaction;", NULL, NULL, NULL)
- #else
- #define START_TRANSACTION()
- #define END_TRANSACTION()
- #endif
- //性别枚举类型
- typedef enum
- {
- UNKNOWN,
- MALE,
- FEMALE,
- }SEX_E;
- //SQL 操作枚举类型
- typedef enum
- {
- INSERT,
- SELECT,
- UPDATE,
- DELETE
- }SQL_OPTYPE;
- typedef struct stEmployee
- {
- unsigned int id;
- unsigned int age;
- SEX_E sex;
- char registertime[26];
- char cellphone[12];
- char email[128];
- char resume[512];
- float salary;
- }Employee_S;
- //全局雇员ID
- static unsigned int g_employeeid;
- long diff_timeval(struct timeval tv1, struct timeval tv2)
- {
- long timecost = 0;
- timecost = 1000000*(tv2.tv_sec - tv1.tv_sec) + (tv2.tv_usec - tv1.tv_usec);
- return timecost;
- }
- void init_employee(Employee_S* employee)
- {
- time_t ttime;
- if(NULL == employee)
- {
- PRINT_TRACE("Invalid parameter: NULL pointer!");
- return ;
- }
- memset(employee, 0, sizeof(*employee));
- g_employeeid ++;
- employee->id = g_employeeid;
- employee->age = 0;
- employee->sex = UNKNOWN;
- time(&ttime);
- sprintf(employee->registertime, "%s", ctime(&ttime));
- sprintf(employee->cellphone, "%0*d", sizeof(employee->cellphone) -1, 0);
- sprintf(employee->email, "persion_%06d@sun.org", g_employeeid);
- sprintf(employee->resume, "Resume:");
- employee->salary = 12345.78;
- }
- void print_employee(Employee_S employee)
- {
- printf("id=%d, age=%d, sex=%d, regtime=%s", employee.id, employee.age, employee.sex, employee.registertime);
- printf("cellphone=%s, email=%s, resume=%s, salary=%6.2f\r\n", employee.cellphone, employee.email, employee.resume, employee.salary);
- }
- sqlite3* open_db(const char* dbname)
- {
- int ret = -1;
- sqlite3 *db = NULL;
- if(NULL == dbname)
- {
- PRINT_TRACE("Invalid parameter: null database name!");
- return NULL;
- }
- ret = sqlite3_open(dbname, &db);
- if(SQLITE_OK != ret)
- {
- PRINT_TRACE("Open database \"%s\" failed: %s.", dbname, sqlite3_errmsg(db));
- sqlite3_close(db);
- return NULL;
- }
- return db;
- }
- int close_db(sqlite3* db)
- {
- int ret = -1;
- if(NULL != db)
- {
- ret = sqlite3_close(db);
- }
- return ret;
- }
- int create_table(sqlite3* db, const char* tablename)
- {
- int ret = -1;
- int cmdlen = 0;
- char *errmsg ;
- char *sqlfmt = "CREATE TABLE %s (id INTEGER PRIMARY KEY, age INTEGER, sex "
- "INTEGER, registertime VARCHAR(26), cellphone VARCHAR(12),"
- "email TEXT, resume TEXT, salary REAL);";
- char *sqlcmd = NULL;
- if(NULL == db || NULL == tablename)
- {
- PRINT_TRACE("Invalid parameter: &db=%p, &tablename=%p.", db, tablename);
- return ret;
- }
- //必须用 strlen 获取字符长度,不能用 sizeof,sizeof 获取的是指针长度,为4.
- cmdlen = strlen(sqlfmt) + strlen(tablename) + 1;
- sqlcmd = (char*)malloc(cmdlen);
- if(NULL == sqlcmd)
- {
- PRINT_TRACE("Not enough memory for sql command!");
- return ret;
- }
- memset(sqlcmd, 0, cmdlen);
- sprintf(sqlcmd, sqlfmt, tablename);
- ret = sqlite3_exec(db, sqlcmd, NULL, NULL, &errmsg);
- if(SQLITE_OK != ret)
- {
- PRINT_TRACE("Create table \"%s\" failed: %s.", tablename, errmsg);
- sqlite3_free(errmsg);
- free(sqlcmd);
- return ret;
- }
- free(sqlcmd);
- return ret;
- }
- char* get_sqlcmd(const char* tablename, SQL_OPTYPE optype, Employee_S employee)
- {
- char* insertfmt = "INSERT INTO %s values(%d, %d, %d, '%s', '%s', '%s', '%s', %10.6f);";
- char* selectfmt = "SELECT * FROM %s where id <= %d;";
- char* updatefmt = "UPDATE %s set age=%d, sex=%d, registertime='%s',cellphone='%s',"\
- "email='%s', resume='%s', salary=%10.6f where id=%d;";
- char* deletefmt = "DELETE FROM %s where id=%d;";
- char* sqlcmd = NULL;
- int cmdlen = 0;
- if(NULL == tablename)
- {
- PRINT_TRACE("Invalid parameter: NULL pointer of tablename!");
- return NULL;
- }
- //为了简化,给 sql 语句申请最大空间
- cmdlen = strlen(updatefmt) + strlen(tablename) + sizeof(employee);
- sqlcmd = (char*) malloc(cmdlen);
- if(NULL == sqlcmd)
- {
- PRINT_TRACE("Not enough memory for sql command!");
- return NULL;
- }
- switch(optype)
- {
- case INSERT:
- sprintf(sqlcmd, insertfmt, tablename, employee.id, employee.age, \
- employee.sex, employee.registertime, employee.cellphone, \
- employee.email, employee.resume, employee.salary);
- break;
- case SELECT:
- sprintf(sqlcmd, selectfmt, tablename, employee.id);
- break;
- case UPDATE:
- sprintf(sqlcmd, updatefmt, tablename, employee.age, employee.sex, \
- employee.registertime, employee.cellphone, employee.email, \
- employee.resume, employee.salary, employee.id);
- break;
- case DELETE:
- sprintf(sqlcmd, deletefmt, tablename, employee.id);
- break;
- default:
- PRINT_TRACE("Unknown operation type:%d\r\n", optype);
- free(sqlcmd);
- return NULL;
- }
- return sqlcmd;
- }
- int test(const char* dbname, const char* tablename, SQL_OPTYPE optype, int count, int istableexists)
- {
- int ret = -1;
- int i = 0;
- int failcount = 0;
- long costtime = 0;
- time_t ttime;
- struct timeval tvStart;
- struct timeval tvEnd;
- sqlite3* db = NULL;
- Employee_S employee;
- char* sqlcmd = NULL;
- char* errmsg = NULL;
- time(&ttime);
- printf("\r\nStart \"%s\" at: %s", __FUNCTION__, ctime(&ttime));
- if(NULL == dbname || NULL == tablename || 0 >= count)
- {
- PRINT_TRACE("Invalid Parameter:dbname=%p, tablename=%p, count=%d.", dbname, tablename, count);
- return ret;
- }
- //打开数据库
- db = open_db(dbname);
- if(NULL == db)
- {
- return ret;
- }
- //判断是否需要创建表
- if( 0 == istableexists)
- {
- ret = create_table(db, tablename);
- if(SQLITE_OK != ret)
- {
- close_db(db);
- return ret;
- }
- }
- //开启事务模式
- START_TRANSACTION();
- for(i = 0; i < count; i++)
- {
- init_employee(&employee);
- sqlcmd = get_sqlcmd(tablename, optype, employee);
- if(NULL == sqlcmd)
- {
- failcount ++;
- continue;
- }
- //开始计时
- gettimeofday(&tvStart, NULL);
- ret = sqlite3_exec(db, sqlcmd, NULL, NULL, &errmsg);
- //结束计时
- gettimeofday(&tvEnd, NULL);
- costtime += diff_timeval(tvStart, tvEnd);
- if(SQLITE_OK != ret)
- {
- failcount ++;
- PRINT_TRACE("Excecute sql: %s failed! Error info:%s.", sqlcmd, errmsg);
- }
- if(NULL != sqlcmd)
- {
- free(sqlcmd);
- }
- if(NULL != errmsg)
- {
- sqlite3_free(errmsg);
- }
- }
- //关闭事务模式
- END_TRANSACTION();
- close_db(db);
- //打印结果
- printf("Operation Type: %d, Database name: %s, Table name: %s. \r\n", optype, dbname, tablename);
- printf("Counts\tTotalTime(us)\tAverageTime(us)\r\n");
- printf("%-8d %-10ld %-10.6f\r\n", count, costtime, (float)(costtime/count));
- time(&ttime);
- printf("Finish \"%s\" at: %s\r\n", __FUNCTION__, ctime(&ttime));
- if(0 < failcount)
- {
- ret = -1;
- }
- return ret;
- }
- int main()
- {
- int count = 1000;
- int istableexists = 0;
- int ret = -1;
- char* dbname = "employee.db";
- char* tablename = "employee";
- SQL_OPTYPE optype;
- optype = INSERT;
- ret = test(dbname, tablename, optype, count, istableexists);
- if(SQLITE_OK != ret)
- {
- PRINT_TRACE("Test failed!");
- return 0;
- }
- istableexists = 1;
- g_employeeid = 0;
- optype = SELECT;
- ret = test(dbname, tablename, optype, count, istableexists);
- if(SQLITE_OK != ret)
- {
- PRINT_TRACE("Test failed!");
- return 0;
- }
- g_employeeid = 0;
- optype = UPDATE;
- ret = test(dbname, tablename, optype, count, istableexists);
- if(SQLITE_OK != ret)
- {
- PRINT_TRACE("Test failed!");
- return 0;
- }
- g_employeeid = 0;
- optype = DELETE;
- ret = test(dbname, tablename, optype, count, istableexists);
- if(SQLITE_OK != ret)
- {
- PRINT_TRACE("Test failed!");
- return 0;
- }
- return 0;
- }
- SQLite3开启事务和关闭事务模式下,性能测试对比
- WCF学习笔记(八)服务模式下的简单事务实例和非事务实例对比
- Windchill 事务的开启与关闭
- 开启事务
- LevelDB、TreeDB、SQLite3性能对比测试
- Mysql 批量插入 事务插入 性能对比
- Mysql批量插入事务插入性能对比
- sqlite3事务和锁的实例
- 性能测试-事务、思考时间和集合点设置
- sqlite3 事务 与锁
- sqlite3事务实例
- SpringMvc+mybatis手动开启和提交事务
- 事务测试
- linux下sqlite3插入性能测试
- ThreadLocal模式下管理的Session会在事务提交后自动关闭
- ThreadLocal模式下管理的Session会在事务提交后自动关闭!
- Hibernate——ThreadLocal模式下管理的Session会在事务提交后自动关闭
- Core Data 和 sqlite3的性能对比【图】3gs,iPhone4,4s,5的性能测试。
- Debug and Release 优化闲谈
- PHP面向对象:接口与抽象方法
- http://blog.csdn.net/shan9liang/article/details/8803989
- Android Make sure other views do not use the same id. 错误解决
- [php] yii时间控件
- SQLite3开启事务和关闭事务模式下,性能测试对比
- Android 关于子线程更新UI
- PageRank算法简介及Map-Reduce实现
- 图文解释XCode常用快捷键的使用
- [刷题]Merge Sorted Array II
- ajax 跨域jsonp
- jQuery选择器大全
- Mac OS X下64位汇编与Linux下64位汇编的一些不同
- 在Django中获取choices的显示值