// 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,&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;",&zErr);

for (i=0; i<tup_num; i++)
{
sql=sqlite3_mprintf(sql,random()%tup_num,"MOB");
rc=sqlite3_exec(db,callback,(char*) data,&zErr);

}

sqlite3_exec(db,"COMMIT TRANSACTION;",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,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,&zErr);
sql="update index_test set name='AJO' where uid < %d;";
sql=sqlite3_mprintf(sql,tup_num);
rc= sqlite3_exec(db,zErr);
sqlite3_free(zErr);
}
}
sqlite3_exec(db,NULL);
endtime=timecacul();
resulttime=endtime-starttime;
printf("no_index_update_all_time(f) : %dms./n",resulttime);

////////////////////////update all v///////////////////////

starttime=timecacul();
sqlite3_exec(db,&zErr);
sql="update index_test set name='MOBILELITabcsafgsdfadfdfdfdfdfssdsdfssasfsassdfew' where uid < %d;";
sql=sqlite3_mprintf(sql,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,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,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,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,zErr);
sqlite3_free(zErr);
}
}
//////////////////////Create index/////////////////////////////////
sql = "create index idx_uid on index_test1(uid)";
rc= sqlite3_exec(db,zErr);
sqlite3_free(zErr);
}
}
/////////////////////////insert 100000/////////////////////////
starttime=timecacul();
sqlite3_exec(db,&zErr);

sql = "insert into index_test1 values(%d,%Q);";
for (i=0; i<tup_num; i++)
{
sql=sqlite3_mprintf(sql,&zErr);
sqlite3_free(sql);

}

sqlite3_exec(db,NULL);
endtime=timecacul();
resulttime=endtime-starttime;

printf("index_insert_%d_time : %dms./n",resulttime);

////////////////////////update///////////////////////

starttime=timecacul();
sql="update index_test1 set name='AJO' where uid=%d;";
sql=sqlite3_mprintf(sql,zErr);
sqlite3_free(zErr);
}
}
endtime=timecacul();
resulttime=endtime-starttime;
printf("index_update_one_time : %dms./n",&zErr);
sql="update index_test1 set name='AJo' where uid < %d;";
sql=sqlite3_mprintf(sql,NULL);
endtime=timecacul();
resulttime=endtime-starttime;
printf("index_update_all_time(f) : %dms./n",&zErr);
sql="update index_test1 set name='MOBILELITabcsafgsdfadfdfdfdfdfssdsdfssasfsassdfew' where uid < %d;";
sql=sqlite3_mprintf(sql,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,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,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,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,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.