#include <iostream>
#include <cstdlib>
#include <time.h>
#define MAXN 10000
#define LIMIT 500
using namespace std;
//计时器
class Timer {
public :
//构造函数
Timer ();
//析构函数
~Timer ();
//开始计时
void begin();
//计时结束
void end();
//获取时间
double get_time();
private :
clock_t start, finish;
double time;
};
Timer::Timer () {
start = 0;
finish = 0;
}
Timer::~Timer () {
start = 0;
finish = 0;
}
void Timer::begin () {
start = clock();
}
void Timer::end () {
finish = clock();
}
double Timer::get_time() {
time = (double)(finish-start)/CLOCKS_PER_SEC;
return time;
}
int a[MAXN];
int b[MAXN];
int c[MAXN];
int d[MAXN];
int p[4][2];
//初始化测试数据
void init () {
srand(time(0));
//参与关键查询的数据
//cout << "a\tb\tc\td" << endl;
for(int i=0; i<MAXN; ++i) {
a[i] = i;
b[i] = rand()%MAXN;
c[i] = rand()%MAXN;
d[i] = rand()%MAXN;
//cout << a[i] << "\t"<< b[i] << "\t" << c[i] << "\t" << d[i] << endl;
}
//查询的限制条件
for (int i=0; i<4; ++i) {
cout << i << ": ";
for (int j=0; j<2; ++j) {
//p[i][0]随机一个小于MAXN的1/2的数,p[i][1]随机一个大于MAXN的1/2的数
p[i][j] = rand()%(int)(MAXN/2) + (int)(MAXN/2)*j;
}
cout << p[i][0] << ", " << p[i][1] << endl;
}
return ;
}
//格式化打印
void print(int &cnt,int x, int y, int z) {
cnt++;
if (cnt%LIMIT == 0) {
cout << "Row " << cnt << ": ";
if (z!=-1) {
cout << x << ',' << y << ',' << z << endl;
}
else {
if (y!=-1) {
cout << x << ',' << y << ',' << "N" << endl;
}
else {
cout << x << ',' << "N" << ',' << "N" << endl;
}
}
}
return ;
}
//内联查询测试
//SELECT *
//FROM a INNER JOIN b ON a.id=b.id
//INNER JOIN c ON b.id=c.id
//WHERE a.id BETWEEN p00 AND p01
//AND b.id BETWEEN p10 AND p11
//AND c.id BETWEEN p20 AND p11
void innerJoin () {
int count1, count2;
Timer timer;
double time1, time2;
cout << "====Inner Join Test====" << endl;
//直接JOIN再判断筛选条件
cout << "Test1:" << endl;
count1 = 0;
timer.begin ();
for(int i=0; i<MAXN; ++i) {
for(int j=0; j<MAXN; ++j) {
if (a[i]==b[j]) {
for(int k=0; k<MAXN; ++k) {
if(b[j]==c[k]) {
if (a[i]>p[0][0] && a[i]<p[0][1] \
&& b[j]>p[1][0] && b[j]<p[1][1] \
&& c[k]>p[2][0] && c[k]<p[2][1]) {
print(count1, a[i], b[j], c[k]);
}
}
}
}
}
}
timer.end ();
time1 = timer.get_time();
//先判断筛选条件再JOIN
cout << "Test2:" << endl;
count2 = 0;
timer.begin ();
for(int i=0; i<MAXN; ++i) {
if (a[i]>p[0][0] && a[i]<p[0][1]) {
for(int j=0; j<MAXN; ++j) {
if (a[i]==b[j] && b[j]>p[1][0] && b[j]<p[1][1]) {
for(int k=0; k<MAXN; ++k) {
if(b[j]==c[k] && c[k]>p[2][0] && c[k]<p[2][1]) {
print(count2, a[i], b[j], c[k]);
}
}
}
}
}
}
timer.end ();
time2 = timer.get_time();
//校验数据的正确性并输出
if(count1 == count2) {
cout << endl;
cout << count1 << " Rows: ";
cout << time1 << " VS " << time2 << endl;
}
return ;
}
//外联查询测试
//SELECT *
//FROM a LEFT JOIN
//(b LEFT JOIN c ON b.id=c.id)
//ON a.id=b.id
//WHERE a.id BETWEEN p00 AND p01
//AND b.id BETWEEN p10 AND p11
//AND c.id BETWEEN p20 AND p11
void leftJoin () {
bool flag1, flag2;
int count1, count2, count3;
Timer timer;
double time1, time2, time3;
cout << "====Left Join Test====" << endl;
//先JOIN再判断条件
cout << "Test1:" << endl;
count1 = 0;
timer.begin ();
for(int i=0; i<MAXN; ++i) {
flag1 = false; //标记a,b表是否有匹配行
for(int j=0; j<MAXN; ++j) {
if (a[i]==b[j]) {
flag2 = false; //标记b,c表是否有匹配行
for(int k=0; k<MAXN; ++k) {
if(b[j]==c[k]) {
if (a[i]>p[0][0] && a[i]<p[0][1] \
&& b[j]>p[1][0] && b[j]<p[1][1] \
&& c[k]>p[2][0] && c[k]<p[2][1]) {
print(count1, a[i], b[j], c[k]);
flag2 = true;
flag1 = true;
}
}
}
if(!flag2) {
if(a[i]>p[0][0] && a[i]<p[0][1] \
&& b[j]>p[1][0] && b[j]<p[1][1]) {
print(count1, a[i], b[j], -1);
flag1 = true;
}
}
}
}
if(!flag1) {
if(a[i]>p[0][0] && a[i]<p[0][1]) {
print(count1, a[i], -1, -1);
}
}
}
timer.end ();
time1 = timer.get_time();
//先判断条件再JOIN
cout << "Test2:" << endl;
count2 = 0;
timer.begin ();
for(int i=0; i<MAXN; ++i) {
flag1 = false;
if (a[i]>p[0][0] && a[i]<p[0][1]) {
for(int j=0; j<MAXN; ++j) {
if (a[i]==b[j] && b[j]>p[1][0] && b[j]<p[1][1]) {
flag2 = false;
for(int k=0; k<MAXN; ++k) {
if(b[j]==c[k] && c[k]>p[2][0] && c[k]<p[2][1]) {
print(count2, a[i], b[j], c[k]);
flag1 = true;
flag2 = true;
}
}
if(!flag2) {
print(count2, a[i], b[j], -1);
flag1 = true;
}
}
}
if(!flag1) {
print(count2, a[i], -1, -1);
}
}
}
timer.end ();
time2 = timer.get_time();
//先判断选择条件再判断关联条件再JOIN,其实跟上面基本是一样的
cout << "Test3:" << endl;
count3 = 0;
timer.begin ();
for(int i=0; i<MAXN; ++i) {
if (a[i]>p[0][0] && a[i]<p[0][1]) {
flag1 = false;
for(int j=0; j<MAXN; ++j) {
if (b[j]>p[1][0] && b[j]<p[1][1]) {
if(a[i]==b[j]) {
flag2 = false;
for(int k=0; k<MAXN; ++k) {
if(c[k]>p[2][0] && c[k]<p[2][1]) {
if(b[j]==c[k]) {
print(count3, a[i], b[j], c[k]);
flag1 = true;
flag2 = true;
}
}
}
if(!flag2) {
print(count3, a[i], b[j], -1);
flag1 = true;
}
}
}
}
if(!flag1) {
print(count3, a[i], -1, -1);
}
}
}
timer.end ();
time3 = timer.get_time();
//校验数据的正确性并输出
if(count1==count2 && count2==count3) {
cout << endl;
cout << "Fetch Rows: " << count1 << endl;
cout << time1 << " VS " << time2 << " VS " << time3 << endl;
}
else {
cout << "Error: " << count1 << " <> " << count2 << " <> " << count3 << endl;
}
return ;
}
int main() {
init();
innerJoin();
leftJoin();
return 0;
} |