DML with or without Index on SQLite(use Random())
来源：互联网 发布：挂机短信软件 编辑：程序博客网 时间：2024/04/30 15:08
// name: DML_sq.c date: 2009/08/24 by JIN RIZE && Minchul
#include <stdio.h>
#include <string.h>
#include <stdlib.h>
#include <sys/time.h>
#include "sqlite3.h"
////////////////////////////////////////////////////////////////////////////////////////////////////////////
int callback(void* data, int ncols, char** values, char** headers);
///////////////////////////////////////////time caculation//////////////////////////////////////////////////
long timecacul () {
struct timeval tv;
struct timezone tz;
gettimeofday(&tv,&tz);
return (tv.tv_sec * 1000 + tv.tv_usec / 1000);
}
////////////////////////////////////////////////////////////////////////////////////////////////////////////
//////////////////////////////////////////////Main FUNC/////////////////////////////////////////////////////
int main(int argc, char **argv)
{
sqlite3 *db;
int rc;
char *sql;
char *zErr;
long starttime, endtime, resulttime;
int i=0;
int tup_num=10000;
srand((unsigned)time(0));
/////////////////////Create database////////////////////////////
rc=sqlite3_open(":memory:", &db);
if (rc){
fprintf(stderr,"Can't create MMDB: %s/n", sqlite3_errmsg(db));
sqlite3_close(db);
exit(1);
}
///////////////////Create table index_test/////////////////////
sql = "create table index_test(uid int, name varchar(50))";
rc= sqlite3_exec(db, sql, NULL,NULL,&zErr);
if (rc!= SQLITE_OK) {
if ( zErr!=NULL) {
fprintf(stdout, "execute() : Error %i : %s/n", zErr);
sqlite3_free(zErr);
}
}
const char* data="callback function called";
sql = "insert into index_test values(%d,%Q);";
/////////////////////////insert 100000/////////////////////////
starttime=timecacul();
sqlite3_exec(db,"BEGIN TRANSACTION;",NULL,NULL,&zErr);
for (i=0; i<tup_num; i++)
{
sql=sqlite3_mprintf(sql,random()%tup_num,"MOB");
rc=sqlite3_exec(db,sql,callback,(char*) data, &zErr);
}
sqlite3_exec(db,"COMMIT TRANSACTION;",NULL,NULL,NULL);
endtime=timecacul();
resulttime=endtime-starttime;
printf("no_index_insert_%d_time : %dms./n",tup_num,resulttime);
////////////////////////update one///////////////////////
starttime=timecacul();
sql="update index_test set name='AJO' where uid=%d;";
sql=sqlite3_mprintf(sql,random()%tup_num);
rc= sqlite3_exec(db, sql, NULL,NULL,&zErr);
if (rc!= SQLITE_OK) {
if ( zErr!=NULL) {
fprintf(stdout, "execute() : Error %i : %s/n", zErr);
sqlite3_free(zErr);
}
}
endtime=timecacul();
resulttime=endtime-starttime;
printf("no_index_update_one_time : %dms./n",resulttime);
////////////////////////update all fix///////////////////////
starttime=timecacul();
sqlite3_exec(db,"BEGIN TRANSACTION;",NULL,NULL,&zErr);
sql="update index_test set name='AJO' where uid < %d;";
sql=sqlite3_mprintf(sql,tup_num);
rc= sqlite3_exec(db, sql, NULL,NULL,&zErr);
if (rc!= SQLITE_OK) {
if ( zErr!=NULL) {
fprintf(stdout, "execute() : Error %i : %s/n", zErr);
sqlite3_free(zErr);
}
}
sqlite3_exec(db,"COMMIT TRANSACTION;",NULL,NULL,NULL);
endtime=timecacul();
resulttime=endtime-starttime;
printf("no_index_update_all_time(f) : %dms./n",resulttime);
////////////////////////update all v///////////////////////
starttime=timecacul();
sqlite3_exec(db,"BEGIN TRANSACTION;",NULL,NULL,&zErr);
sql="update index_test set name='MOBILELITabcsafgsdfadfdfdfdfdfssdsdfssasfsassdfew' where uid < %d;";
sql=sqlite3_mprintf(sql,tup_num);
rc= sqlite3_exec(db, sql, NULL,NULL,&zErr);
if (rc!= SQLITE_OK) {
if ( zErr!=NULL) {
fprintf(stdout, "execute() : Error %i : %s/n", zErr);
sqlite3_free(zErr);
}
}
sqlite3_exec(db,"COMMIT TRANSACTION;",NULL,NULL,NULL);
endtime=timecacul();
resulttime=endtime-starttime;
printf("no_index_update_all_time(v) : %dms./n",resulttime);
////////////////////////delete///////////////////////
starttime=timecacul();
sql="delete from index_test where uid=%d;";
sql=sqlite3_mprintf(sql,random()%tup_num);
rc= sqlite3_exec(db, sql, NULL,NULL,&zErr);
if (rc!= SQLITE_OK) {
if ( zErr!=NULL) {
fprintf(stdout, "execute() : Error %i : %s/n", zErr);
sqlite3_free(zErr);
}
}
endtime=timecacul();
resulttime=endtime-starttime;
printf("no_index_delete_one_time : %dms./n",resulttime);
////////////////////////select one///////////////////////
starttime=timecacul();
sql="select * from index_test where uid = 2512;";
rc=sqlite3_exec(db,sql,callback,(char*) data, &zErr);
if (rc!= SQLITE_OK) {
if ( zErr!=NULL) {
fprintf(stdout, "execute() : Error %i : %s/n", zErr);
sqlite3_free(zErr);
}
}
endtime=timecacul();
resulttime=endtime-starttime;
printf("no_index_search_one_time : %dms./n",resulttime);
////////////////////////select min///////////////////////
starttime=timecacul();
sql="select min(uid) from index_test;";
rc=sqlite3_exec(db,sql,callback,(char*) data, &zErr);
if (rc!= SQLITE_OK) {
if ( zErr!=NULL) {
fprintf(stdout, "execute() : Error %i : %s/n", zErr);
sqlite3_free(zErr);
}
}
endtime=timecacul();
resulttime=endtime-starttime;
printf("no_index_search_min_time : %dms./n",resulttime);
///////////////////Create table index_test1/////////////////////////
sql = "create table index_test1(uid int, name varchar(50))";
rc= sqlite3_exec(db, sql, NULL,NULL,&zErr);
if (rc!= SQLITE_OK) {
if ( zErr!=NULL) {
fprintf(stdout, "execute() : Error %i : %s/n", zErr);
sqlite3_free(zErr);
}
}
//////////////////////Create index/////////////////////////////////
sql = "create index idx_uid on index_test1(uid)";
rc= sqlite3_exec(db, sql, NULL,NULL,&zErr);
if (rc!= SQLITE_OK) {
if ( zErr!=NULL) {
fprintf(stdout, "execute() : Error %i : %s/n", zErr);
sqlite3_free(zErr);
}
}
/////////////////////////insert 100000/////////////////////////
starttime=timecacul();
sqlite3_exec(db,"BEGIN TRANSACTION;",NULL,NULL,&zErr);
sql = "insert into index_test1 values(%d,%Q);";
for (i=0; i<tup_num; i++)
{
sql=sqlite3_mprintf(sql,random()%tup_num,"MOB");
rc=sqlite3_exec(db,sql,callback,(char*) data, &zErr);
sqlite3_free(sql);
}
sqlite3_exec(db,"COMMIT TRANSACTION;",NULL,NULL,NULL);
endtime=timecacul();
resulttime=endtime-starttime;
printf("index_insert_%d_time : %dms./n",tup_num,resulttime);
////////////////////////update///////////////////////
starttime=timecacul();
sql="update index_test1 set name='AJO' where uid=%d;";
sql=sqlite3_mprintf(sql,random()%tup_num);
rc= sqlite3_exec(db, sql, NULL,NULL,&zErr);
if (rc!= SQLITE_OK) {
if ( zErr!=NULL) {
fprintf(stdout, "execute() : Error %i : %s/n", zErr);
sqlite3_free(zErr);
}
}
endtime=timecacul();
resulttime=endtime-starttime;
printf("index_update_one_time : %dms./n",resulttime);
////////////////////////update all fix///////////////////////
starttime=timecacul();
sqlite3_exec(db,"BEGIN TRANSACTION;",NULL,NULL,&zErr);
sql="update index_test1 set name='AJo' where uid < %d;";
sql=sqlite3_mprintf(sql,tup_num);
rc= sqlite3_exec(db, sql, NULL,NULL,&zErr);
if (rc!= SQLITE_OK) {
if ( zErr!=NULL) {
fprintf(stdout, "execute() : Error %i : %s/n", zErr);
sqlite3_free(zErr);
}
}
sqlite3_exec(db,"COMMIT TRANSACTION;",NULL,NULL,NULL);
endtime=timecacul();
resulttime=endtime-starttime;
printf("index_update_all_time(f) : %dms./n",resulttime);
////////////////////////update all v///////////////////////
starttime=timecacul();
sqlite3_exec(db,"BEGIN TRANSACTION;",NULL,NULL,&zErr);
sql="update index_test1 set name='MOBILELITabcsafgsdfadfdfdfdfdfssdsdfssasfsassdfew' where uid < %d;";
sql=sqlite3_mprintf(sql,tup_num);
rc= sqlite3_exec(db, sql, NULL,NULL,&zErr);
if (rc!= SQLITE_OK) {
if ( zErr!=NULL) {
fprintf(stdout, "execute() : Error %i : %s/n", zErr);
sqlite3_free(zErr);
}
}
sqlite3_exec(db,"COMMIT TRANSACTION;",NULL,NULL,NULL);
endtime=timecacul();
resulttime=endtime-starttime;
printf("index_update_all_time(v) : %dms./n",resulttime);
////////////////////////delete///////////////////////
starttime=timecacul();
sql="delete from index_test1 where uid=%d;";
sql=sqlite3_mprintf(sql,random()%tup_num);
rc= sqlite3_exec(db, sql, NULL,NULL,&zErr);
if (rc!= SQLITE_OK) {
if ( zErr!=NULL) {
fprintf(stdout, "execute() : Error %i : %s/n", zErr);
sqlite3_free(zErr);
}
}
endtime=timecacul();
resulttime=endtime-starttime;
printf("index_delete_one_time : %dms./n",resulttime);
///////////////////////////////select one/////////////////////////
starttime=timecacul();
sql="select * from index_test1 where uid = 2512;";
rc=sqlite3_exec(db,sql,callback,(char*) data, &zErr);
if (rc!= SQLITE_OK) {
if ( zErr!=NULL) {
fprintf(stdout, "execute() : Error %i : %s/n", zErr);
sqlite3_free(zErr);
}
}
endtime=timecacul();
resulttime=endtime-starttime;
printf("index_search_one_time : %dms./n",resulttime);
////////////////////////select min///////////////////////
starttime=timecacul();
sql="select min(uid) from index_test1;";
rc=sqlite3_exec(db,sql,callback,(char*) data, &zErr);
if (rc!= SQLITE_OK) {
if ( zErr!=NULL) {
fprintf(stdout, "execute() : Error %i : %s/n", zErr);
sqlite3_free(zErr);
}
}
endtime=timecacul();
resulttime=endtime-starttime;
printf("index_search_min_time : %dms./n",resulttime);
////////////////////////////////////////////////////////////////////////////////
sqlite3_close(db);
return 0;
}
/////////////////////////////////////////////////////////////////////////////////////////////////////////
/////////////////////////////////////////////////////////////////////////////////////////////////////////
int callback(void* data, int ncols, char** values, char** headers)
{
int i;
fprintf(stderr, "%s:", (const char*) data);
for (i=0; i<ncols; i++) {
fprintf(stderr, "%s=%s.", headers[i], values[i]);
}
fprintf (stderr, "/n");
return 0;
}
/////////////////////////////////////////////////////////////////////////////////////////////////////////
[db@localhost sqlite3]$ gcc DML_sq.c -o DML_sq -L/usr/local/lib/ -lsqlite3
[db@localhost sqlite3]$ ./DML_sq
Used as main memory DB(:memory:):
have transaction:
no_index_insert_100000_time : 1459ms.
no_index_update_one_time : 15ms.
no_index_update_all_time(f) : 364ms.
no_index_update_all_time(v) : 374ms.
no_index_delete_one_time : 15ms.
no_index_search_one_time : 16ms.
no_index_search_min_time : 17ms.
index_insert_100000_time : 1855ms.
index_update_one_time : 0ms.
index_update_all_time(f) : 237ms.
index_update_all_time(v) : 439ms.
index_delete_one_time : 0ms.
index_search_one_time : 0ms.
index_search_min_time : 1ms.
When tuple=10,000
no_index_insert_10000_time : 128ms.
no_index_update_one_time : 1ms.
no_index_update_all_time(f) : 49ms.
no_index_update_all_time(v) : 58ms.
no_index_delete_one_time : 1ms.
no_index_search_one_time : 2ms.
no_index_search_min_time : 2ms.
index_insert_10000_time : 175ms.
index_update_one_time : 0ms.
index_update_all_time(f) : 51ms.
index_update_all_time(v) : 59ms.
index_delete_one_time : 0ms.
index_search_one_time : 0ms.
index_search_min_time : 0ms.
no transaction:
no_index_insert_100000_time：1488ms.
index_insert_100000_time：2019ms.
Used as HDD DB(index_test):
no transaction:
no_index_insert_100000_time：199288ms.
index_insert_100000_time：263062ms.
have transaction:
no_index_insert_100000_time : 1346ms.
no_index_update_one_time : 19ms.
no_index_delete_one_time : 18ms.
no_index_search_one_time : 19ms.
index_insert_100000_time : 1950ms.
index_update_one_time : 1ms.
index_delete_one_time : 0ms.
index_search_one_time : 0ms.
no_index_insert_100000_time : 1335ms.
no_index_update_one_time : 19ms.
no_index_delete_one_time : 18ms.
no_index_search_one_time : 19ms.
index_insert_100000_time : 1943ms.
index_update_one_time : 1ms.
index_delete_one_time : 0ms.
index_search_one_time : 0ms.
When tuple=10,000
no_index_insert_10000_time : 132ms.
no_index_update_one_time : 1ms.
no_index_update_all_time(f) : 63ms.
no_index_update_all_time(v) : 78ms.
no_index_delete_one_time : 2ms.
no_index_search_one_time : 1ms.
no_index_search_min_time : 2ms.
index_insert_10000_time : 194ms.
index_update_one_time : 0ms.
index_update_all_time(f) : 57ms.
index_update_all_time(v) : 74ms.
index_delete_one_time : 0ms.
index_search_one_time : 0ms.
index_search_min_time : 0ms.
- DML with or without Index on SQLite(use Random())
- DML with or without Index on ML(use Random())
- Insert 100000 tuples with or without index on ML
- With or Without You
- With or Without You
- Insert 10000 tuples with "BEGIN TRANSCATION" or no ON SQLite
- Use Local Or Global Index?
- ORA-14452: attempt to create, alter or drop an index on temporary table already in use
- ORA-14452: attempt to create, alter or drop an index on temporary table already in use
- UVa 10493 Cats, with or without Hats
- The good site to buy rs 2007 gold cheap on RSorder with pay by Paypal or Alipay without anyconfirmat
- DML statment cannot operate on trigger.new or trigger.old
- install and use junit on MAC without IDE
- Limit MongoDB memory use on Windows without Virtualization
- FCKeditor use with jsp on eclipse
- use cscope with gvim on windows
- use lib function on linux with C
- SQLite with readline support on Ubuntu
- BANG0BB6C39D9BFCD4140FCDA9EBXIANGUO
- 愿朋友们冬至快乐！
- 07年钢材市场发展态势权威预测
- 说说600308，华泰股份
- 鞍钢最新资料分析
- DML with or without Index on SQLite(use Random())
- 价值投资交流园12.15
- 和大家说
- 000709唐钢股份分析（2）
- 远程图片自动保存到本地并为图片加上水印
- 鞍钢股权融资分析
- 000709唐钢股份分析（1）
- c# TCP通讯
- 鞍钢不愿意上涨的原因分析
