DML with or without Index on ML(use Random())
来源：互联网 发布：md5.js用法 编辑：程序博客网 时间：2024/05/17 23:14
//File: DML_ml.c Date: 2009/08/25 by JIN RIZE && Minchul
#include <stdio.h>
#include <string.h>
#include <stdlib.h>
#include <time.h>
#include <sys/time.h>
#include "include/isql.h"
///////////////////////////////////////time caculation/////////////////////////////////////////////
long timecacul () {
struct timeval tv;
struct timezone tz;
gettimeofday(&tv,&tz);
return (tv.tv_sec * 1000 + tv.tv_usec / 1000);
}
//////////////////////////////////////Main Fun.////////////////////////////////////////////////////
int main(int argc, char **argv)
{
iSQL isql;
long starttime, endtime, resulttime;
int i=0;
int num=10000;
char query[2048];
srand((unsigned)time(0));
if(!iSQL_connect(&isql,"127.0.0.1","index_test","sys","sys")){
printf("fail %s", iSQL_error(&isql));
return -1;
}
memset (query,0,2048);
//autocommit stop
sprintf(query,"SET AUTOCOMMIT OFF;");
if (iSQL_query(&isql,query)<0) {
iSQL_rollback(&isql);
fprintf(stderr, "error query: %s/n", query);
printf("error [%d] [%s]/n", iSQL_errno(&isql),iSQL_error(&isql));
printf("/n/nerror query 1: %s/n/n", query);
exit(1);
}
printf(query);
printf("/n");
//////////////////////////////////////create table////////////////
sprintf(query,"drop table index_test;");
if (iSQL_query(&isql,query)<0) {
iSQL_rollback(&isql);
fprintf(stderr, "error query: %s/n", query);
printf("error [%d] [%s]/n", iSQL_errno(&isql),iSQL_error(&isql));
printf("/n/nerror query 1: %s/n/n", query);
exit(1);
}
sprintf(query,"create table index_test(uid int, name varchar(50), address varchar(20));");
if (iSQL_query(&isql,query)<0) {
iSQL_rollback(&isql);
fprintf(stderr, "error query: %s/n", query);
printf("error [%d] [%s]/n", iSQL_errno(&isql),iSQL_error(&isql));
printf("/n/nerror query 1: %s/n/n", query);
exit(1);
}
/////////////////////////Insert 100000//////////////////////////
starttime=timecacul();
iSQL_begin_transaction(&isql);
for (i=0; i<num; i++)
{
sprintf(query,"insert into index_test values(%d,'MOB','Korea no.%d');",random()%num, random()%num);
if (iSQL_query(&isql,query)<0) {
iSQL_rollback(&isql);
fprintf(stderr, "error query: %s/n", query);
exit(1);
}
}
iSQL_commit(&isql);
endtime=timecacul();
resulttime=endtime-starttime;
printf("no_index_insert_%d_time : %dms./n",num,resulttime);
//////////////////////update one tuple///////////////////////
starttime=timecacul();
sprintf(query,"update index_test set name='ajo' where uid=%d;",random()%num);
if (iSQL_query(&isql,query)<0) {
iSQL_rollback(&isql);
fprintf(stderr, "error query: %s/n", query);
exit(1);
};
endtime=timecacul();
resulttime=endtime-starttime;
printf("no_index_update_one_time : %dms./n",resulttime);
//////////////////////update all fix///////////////////////
starttime=timecacul();
iSQL_begin_transaction(&isql);
sprintf(query,"update index_test set name='ajo' where uid < %d;",num);
if (iSQL_query(&isql,query)<0) {
iSQL_rollback(&isql);
fprintf(stderr, "error query: %s/n", query);
exit(1);
}
iSQL_commit(&isql);
endtime=timecacul();
resulttime=endtime-starttime;
printf("no_index_update_all_time(f) : %dms./n",resulttime);
//////////////////////update all v///////////////////////
starttime=timecacul();
iSQL_begin_transaction(&isql);
sprintf(query,"update index_test set name='MOBILELITabcsafgsdfadfdfdfdfdfssdsdfssasfsassdfew' where uid < %d;",num);
if (iSQL_query(&isql,query)<0) {
iSQL_rollback(&isql);
fprintf(stderr, "error query: %s/n", query);
exit(1);
}
iSQL_commit(&isql);
endtime=timecacul();
resulttime=endtime-starttime;
printf("no_index_update_all_time(v) : %dms./n",resulttime);
//////////////////////delete one tuple///////////////////////
starttime=timecacul();
sprintf(query,"delete from index_test where uid=%d;",random()%num);
if (iSQL_query(&isql,query)<0) {
iSQL_rollback(&isql);
fprintf(stderr, "error query: %s/n", query);
exit(1);
}
endtime=timecacul();
resulttime=endtime-starttime;
printf("no_index_delete_one_time : %dms./n",resulttime);
/////////////////////Select One//////////////////////////////
starttime=timecacul();
sprintf(query,"select * from index_test where uid = 2512;");
if (iSQL_query(&isql,query)<0) {
iSQL_rollback(&isql);
fprintf(stderr, "error query: %s/n", query);
printf("error [%d] [%s]/n", iSQL_errno(&isql),iSQL_error(&isql));
printf("/n/nerror query 1: %s/n/n", query);
exit(1);
}
endtime=timecacul();
resulttime=endtime-starttime;
printf("no_index_select_one_time : %dms./n",resulttime);
iSQL_commit(&isql);
/////////////////////Select like//////////////////////////////
starttime=timecacul();
sprintf(query,"select address from index_test where name like '%2512%';");
if (iSQL_query(&isql,query)<0) {
iSQL_rollback(&isql);
fprintf(stderr, "error query: %s/n", query);
printf("error [%d] [%s]/n", iSQL_errno(&isql),iSQL_error(&isql));
printf("/n/nerror query 1: %s/n/n", query);
exit(1);
}
endtime=timecacul();
resulttime=endtime-starttime;
printf("no_index_select_like_time : %dms./n",resulttime);
iSQL_commit(&isql);
/////////////////////Select min//////////////////////////////
starttime=timecacul();
sprintf(query,"select min(uid) from index_test;");
if (iSQL_query(&isql,query)<0) {
iSQL_rollback(&isql);
fprintf(stderr, "error query: %s/n", query);
printf("error [%d] [%s]/n", iSQL_errno(&isql),iSQL_error(&isql));
printf("/n/nerror query 1: %s/n/n", query);
exit(1);
}
endtime=timecacul();
resulttime=endtime-starttime;
printf("no_index_select_min_time : %dms./n",resulttime);
iSQL_commit(&isql);
/////////////////////Select Sub//////////////////////////////
starttime=timecacul();
sprintf(query,"select address, count_uid from(select count(uid), address from index_test group by address) as DML(count_Uid, address);");
if (iSQL_query(&isql,query)<0) {
iSQL_rollback(&isql);
fprintf(stderr, "error query: %s/n", query);
printf("error [%d] [%s]/n", iSQL_errno(&isql),iSQL_error(&isql));
printf("/n/nerror query 1: %s/n/n", query);
exit(1);
}
endtime=timecacul();
resulttime=endtime-starttime;
printf("no_index_select_sub_time : %dms./n",resulttime);
iSQL_commit(&isql);
////////////////////create table_test1///////////////////////
sprintf(query,"drop table index_test1;");
if (iSQL_query(&isql,query)<0) {
iSQL_rollback(&isql);
fprintf(stderr, "error query: %s/n", query);
printf("error [%d] [%s]/n", iSQL_errno(&isql),iSQL_error(&isql));
printf("/n/nerror query 1: %s/n/n", query);
exit(1);
}
sprintf(query,"create table index_test1(uid int, name varchar(50),address varchar(20));");
if (iSQL_query(&isql,query)<0) {
iSQL_rollback(&isql);
fprintf(stderr, "error query: %s/n", query);
printf("error [%d] [%s]/n", iSQL_errno(&isql),iSQL_error(&isql));
printf("/n/nerror query 1: %s/n/n", query);
exit(1);
}
//////////////create index idx_name on index_test1//////////////
sprintf(query,"create index idx_uid on index_test1(uid)");
if (iSQL_query(&isql,query)<0) {
iSQL_rollback(&isql);
printf("fail %s", iSQL_error(&isql));
exit(1);
}
starttime=timecacul();
iSQL_begin_transaction(&isql);
for (i=0; i<num; i++)
{
sprintf(query,"insert into index_test1 values(%d,'mod','Korea no.%d');",random()%num, random()%num);
if (iSQL_query(&isql,query)<0) {
iSQL_rollback(&isql);
fprintf(stderr, "error query: %s/n", query);
exit(1);
}
}
iSQL_commit(&isql);
endtime=timecacul();
resulttime=endtime-starttime;
printf("index_insert_%d_time : %dms./n",num,resulttime);
////////////////////////////////update one tuple ///////////////////////////////////////
starttime=timecacul();
sprintf(query,"update index_test1 set name='ajo' where uid=%d;",random()%num);
if (iSQL_query(&isql,query)<0) {
iSQL_rollback(&isql);
fprintf(stderr, "error query: %s/n", query);
exit(1);
}
endtime=timecacul();
resulttime=endtime-starttime;
printf("index_update_one_time : %dms./n",resulttime);
//////////////////////update all fix///////////////////////
starttime=timecacul();
iSQL_begin_transaction(&isql);
sprintf(query,"update index_test1 set name='abc' where uid < %d;",num);
if (iSQL_query(&isql,query)<0) {
iSQL_rollback(&isql);
fprintf(stderr, "error query: %s/n", query);
exit(1);
}
iSQL_commit(&isql);
endtime=timecacul();
resulttime=endtime-starttime;
printf("index_update_all_time(f) : %dms./n",resulttime);
//////////////////////update all v///////////////////////
starttime=timecacul();
iSQL_begin_transaction(&isql);
sprintf(query,"update index_test1 set name='MOBILELITabcsafgsdfadfdfdfdfdfssdsdfssasfsassdfew' where uid < %d;",num);
if (iSQL_query(&isql,query)<0) {
iSQL_rollback(&isql);
fprintf(stderr, "error query: %s/n", query);
exit(1);
}
iSQL_commit(&isql);
endtime=timecacul();
resulttime=endtime-starttime;
printf("index_update_all_time(v) : %dms./n",resulttime);
//////////////////////delete one tuple///////////////////////
starttime=timecacul();
sprintf(query,"delete from index_test1 where uid=%d;",random()%num);
if (iSQL_query(&isql,query)<0) {
iSQL_rollback(&isql);
fprintf(stderr, "error query: %s/n", query);
exit(1);
};
endtime=timecacul();
resulttime=endtime-starttime;
printf("index_delete_one_time : %dms./n",resulttime);
//////////////////////////Select One///////////////////////////////////
starttime=timecacul();
sprintf(query,"select * from index_test1 where uid = 2512;");
if (iSQL_query(&isql,query)<0) {
iSQL_rollback(&isql);
fprintf(stderr, "error query: %s/n", query);
printf("error [%d] [%s]/n", iSQL_errno(&isql),iSQL_error(&isql));
printf("/n/nerror query 1: %s/n/n", query);
exit(1);
}
endtime=timecacul();
resulttime=endtime-starttime;
printf("index_select_one_time : %dms./n",resulttime);
iSQL_commit(&isql);
/////////////////////Select like//////////////////////////////
starttime=timecacul();
sprintf(query,"select address from index_test1 where name like '%2512%';");
if (iSQL_query(&isql,query)<0) {
iSQL_rollback(&isql);
fprintf(stderr, "error query: %s/n", query);
printf("error [%d] [%s]/n", iSQL_errno(&isql),iSQL_error(&isql));
printf("/n/nerror query 1: %s/n/n", query);
exit(1);
}
endtime=timecacul();
resulttime=endtime-starttime;
printf("index_select_like_time : %dms./n",resulttime);
iSQL_commit(&isql);
/////////////////////Select min//////////////////////////////
starttime=timecacul();
sprintf(query,"select min(uid) from index_test1;");
if (iSQL_query(&isql,query)<0) {
iSQL_rollback(&isql);
fprintf(stderr, "error query: %s/n", query);
printf("error [%d] [%s]/n", iSQL_errno(&isql),iSQL_error(&isql));
printf("/n/nerror query 1: %s/n/n", query);
exit(1);
}
endtime=timecacul();
resulttime=endtime-starttime;
printf("index_select_min_time : %dms./n",resulttime);
iSQL_commit(&isql);
/////////////////////Select Sub//////////////////////////////
starttime=timecacul();
sprintf(query,"select address, count_uid from(select count(uid), address from index_test1 group by address) as DML(count_Uid, address);");
if (iSQL_query(&isql,query)<0) {
iSQL_rollback(&isql);
fprintf(stderr, "error query: %s/n", query);
printf("error [%d] [%s]/n", iSQL_errno(&isql),iSQL_error(&isql));
printf("/n/nerror query 1: %s/n/n", query);
exit(1);
}
endtime=timecacul();
resulttime=endtime-starttime;
printf("index_select_sub_time : %dms./n",resulttime);
iSQL_commit(&isql);
//////////////////////////////////////////////////////////////////////
iSQL_disconnect(&isql);
return 0;
}
/////////////////////////////////////////////END Main/////////////////////////////////////////////////////
When num=10, 000:
[db@localhost sqlite3]$ gcc DML_ml.c -o DML_ml -L/home/db/mobilelite/lib -lmobiledbms -lpthread -lnsl -lm
[db@localhost sqlite3]$ ./DML_ml
SET AUTOCOMMIT OFF;
no_index_insert_10000_time : 200ms.
no_index_update_one_time : 1ms.
no_index_update_all_time(f) : 13ms.
no_index_update_all_time(v) : 28ms.
no_index_delete_one_time : 1ms.
no_index_select_one_time : 0ms.
no_index_select_like_time : 0ms.
no_index_select_min_time : 0ms.
no_index_select_sub_time : 0ms.
index_insert_10000_time : 236ms.
index_update_one_time : 0ms.
index_update_all_time(f) : 2873ms.
index_update_all_time(v) : 2885ms.
index_delete_one_time : 0ms.
index_select_one_time : 0ms.
index_select_like_time : 0ms.
index_select_min_time : 0ms.
index_select_sub_time : 1ms.
When using index buffering:
SET AUTOCOMMIT OFF;
no_index_insert_10000_time : 198ms.
no_index_update_one_time : 1ms.
no_index_update_all_time(f) : 13ms.
no_index_update_all_time(v) : 24ms.
no_index_delete_one_time : 1ms.
no_index_select_one_time : 0ms.
no_index_select_like_time : 0ms.
no_index_select_min_time : 0ms.
no_index_select_sub_time : 0ms.
index_insert_10000_time : 231ms.
index_update_one_time : 0ms.
index_update_all_time(f) : 3043ms.
index_update_all_time(v) : 3000ms.
index_delete_one_time : 0ms.
index_select_one_time : 0ms.
index_select_like_time : 0ms.
index_select_min_time : 0ms.
index_select_sub_time : 0ms.
When num=100, 000:
no_index_insert_100000_time : 2030ms.
no_index_update_one_time : 13ms.
no_index_delete_one_time : 12ms.
no_index_select_one_time : 0ms.
no_index_select_like_time : 0ms.
no_index_select_min_time : 0ms.
no_index_select_sub_time : 0ms.
index_insert_100000_time : 9391ms.
index_update_one_time : 0ms.
index_delete_one_time : 0ms.
index_select_one_time : 9ms.
index_select_like_time : 0ms.
index_select_min_time : 0ms.
index_select_sub_time : 0ms.
When using index buffering:
no_index_insert_100000_time : 1998ms.
no_index_update_one_time : 13ms.
no_index_delete_one_time : 13ms.
no_index_select_one_time : 0ms.
no_index_select_like_time : 1ms.
no_index_select_min_time : 0ms.
no_index_select_sub_time : 0ms.
index_insert_100000_time : 9352ms.
index_update_one_time : 1ms.
index_delete_one_time : 0ms.
index_select_one_time : 8ms.
index_select_like_time : 0ms.
index_select_min_time : 0ms.
index_select_sub_time : 0ms.
- DML with or without Index on ML(use Random())
- DML with or without Index on SQLite(use Random())
- Insert 100000 tuples with or without index on ML
- With or Without You
- With or Without You
- Use Local Or Global Index?
- Insert 10000 tuples with "SET AUTOCOMMIT OFF" or no ON ML
- KSFetch Annoyance on Mac OS X 10.8 ML with ‘Hands Off’ or ‘Little Snitch’ Firewall.
- 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
- C Reference Manual Reading Notes: 010 Definition and Replacement
- UDP简介
- 强连通分量 + 缩点 kosaraju
- js检测浏览器版本
- 一个法学专业学员的程序员之路
- DML with or without Index on ML(use Random())
- 线段树学习入门
- SQL Server 2005 Service Broker 简介
- 双向SSL握手过程
- August, 24(R)
- 用stream直接下载文件
- Matlab符号处理工具箱简介
- ie6支持，ie7不支持的js图片切换解决办法
- ubuntu中gedit,vim中文以及"^M"显示问题
