From d1bee7f9b58a86cc4f39d04e96e6e6846f326799 Mon Sep 17 00:00:00 2001 From: prabatuty Date: Fri, 30 Jan 2009 08:27:27 +0000 Subject: [PATCH] performance fixes for join table based on selectivity If hash or tree is involved, then that table becomes the left handle of the join --- include/AggTableImpl.h | 4 ++++ include/JoinTableImpl.h | 4 ++-- include/Table.h | 13 +++++++++++++ include/TableImpl.h | 7 +++++++ src/odbc/Makefile | 18 +++++++++--------- src/storage/DataType.cxx | 40 ++++++++++++++++++++++++++++++++++++---- src/storage/JoinTableImpl.cxx | 35 +++++++++++++++++++++++++++++++++++ src/storage/TableImpl.cxx | 20 ++++++++++++-------- 8 files changed, 118 insertions(+), 23 deletions(-) diff --git a/include/AggTableImpl.h b/include/AggTableImpl.h index 3f09665a..52d1b068 100644 --- a/include/AggTableImpl.h +++ b/include/AggTableImpl.h @@ -34,6 +34,7 @@ enum AggType AGG_COUNT, AGG_UNKNOWN }; +#include class AggFldDef { public: @@ -146,6 +147,9 @@ class AggTableImpl:public Table bool isTableInvolved(char *tableName) { printf("Wrong call\n"); return false; } void printPlan(int space){printf("AGG-PLAN-TODO\n");} + DbRetVal optimize() + { printf("Wrong call\n"); return OK; } + ScanType getScanType(){ return unknownScan;} }; #endif diff --git a/include/JoinTableImpl.h b/include/JoinTableImpl.h index 15784c89..07bf7331 100644 --- a/include/JoinTableImpl.h +++ b/include/JoinTableImpl.h @@ -149,8 +149,8 @@ class JoinTableImpl:public Table void setPredicate(Predicate *pred); void printPlan(int space); DbRetVal optimize(); - - + void optimizeRestrict(); + ScanType getScanType(); }; diff --git a/include/Table.h b/include/Table.h index d97a1e1b..87538efe 100644 --- a/include/Table.h +++ b/include/Table.h @@ -19,6 +19,16 @@ #include class Predicate; class Condition; +#ifndef SCANTYPE +enum ScanType +{ + fullTableScan = 0, + hashIndexScan, + treeIndexScan, + unknownScan +}; +#define SCANTYPE +#endif /** * @class Table * @@ -197,6 +207,8 @@ class Table virtual void printSQLIndexString()=0; //optimizer + virtual DbRetVal optimize()=0; + virtual ScanType getScanType()=0; virtual bool isTableInvolved(char *tblName)=0; virtual bool pushPredicate(Predicate *pred)=0; virtual void setPredicate(Predicate *pred)=0; @@ -211,4 +223,5 @@ class Table static void getFieldNameAlone(char *fname, char *name); static void getTableNameAlone(char *fname, char *name); }; + #endif diff --git a/include/TableImpl.h b/include/TableImpl.h index b742a967..d5faa9bc 100644 --- a/include/TableImpl.h +++ b/include/TableImpl.h @@ -26,6 +26,7 @@ #include #include #include//for AggType +#ifndef SCANTYPE enum ScanType { fullTableScan = 0, @@ -33,6 +34,8 @@ enum ScanType treeIndexScan, unknownScan }; +#define SCANTYPE +#endif static char ScanTypeNames[][10] = { @@ -222,9 +225,13 @@ class TableImpl:public Table DbRetVal setUndoLogging(bool flag) { undoFlag = flag; } void printSQLIndexString(); + + DbRetVal optimize(); bool isTableInvolved(char *tblName); bool pushPredicate(Predicate *pred); void setPredicate(Predicate *pred); + ScanType getScanType() { return scanType_; } + char* getName() { return tblName_; } void setTableInfo(char *name, int tblid, size_t length, int numFld, int numIdx, void *chunk); diff --git a/src/odbc/Makefile b/src/odbc/Makefile index 0a99f417..87ce8acb 100644 --- a/src/odbc/Makefile +++ b/src/odbc/Makefile @@ -77,14 +77,14 @@ HEADERS = $(noinst_HEADERS) ETAGS = etags CTAGS = ctags DISTFILES = $(DIST_COMMON) $(DIST_SOURCES) $(TEXINFOS) $(EXTRA_DIST) -ACLOCAL = ${SHELL} /home/praba/csql/latest/jan/jan28/csql/missing --run aclocal-1.9 +ACLOCAL = ${SHELL} /home/praba/csql/latest/jan/jan29/csql/missing --run aclocal-1.9 AMDEP_FALSE = # AMDEP_TRUE = -AMTAR = ${SHELL} /home/praba/csql/latest/jan/jan28/csql/missing --run tar +AMTAR = ${SHELL} /home/praba/csql/latest/jan/jan29/csql/missing --run tar AR = ar -AUTOCONF = ${SHELL} /home/praba/csql/latest/jan/jan28/csql/missing --run autoconf -AUTOHEADER = ${SHELL} /home/praba/csql/latest/jan/jan28/csql/missing --run autoheader -AUTOMAKE = ${SHELL} /home/praba/csql/latest/jan/jan28/csql/missing --run automake-1.9 +AUTOCONF = ${SHELL} /home/praba/csql/latest/jan/jan29/csql/missing --run autoconf +AUTOHEADER = ${SHELL} /home/praba/csql/latest/jan/jan29/csql/missing --run autoheader +AUTOMAKE = ${SHELL} /home/praba/csql/latest/jan/jan29/csql/missing --run automake-1.9 AWK = gawk CC = gcc CCDEPMODE = depmode=gcc3 @@ -94,7 +94,7 @@ CPPFLAGS = CXX = g++ CXXCPP = g++ -E CXXDEPMODE = depmode=gcc3 -CXXFLAGS = -O2 -I/home/praba/csql/jdk1.6.0_06/include -I/home/praba/csql/jdk1.6.0_06/include/linux +CXXFLAGS = -g -I/home/praba/csql/jdk1.6.0_06/include -I/home/praba/csql/jdk1.6.0_06/include/linux CYGPATH_W = echo DEFS = -DHAVE_CONFIG_H DEPDIR = .deps @@ -120,7 +120,7 @@ LIBS = LIBTOOL = $(SHELL) $(top_builddir)/libtool LN_S = ln -s LTLIBOBJS = -MAKEINFO = ${SHELL} /home/praba/csql/latest/jan/jan28/csql/missing --run makeinfo +MAKEINFO = ${SHELL} /home/praba/csql/latest/jan/jan29/csql/missing --run makeinfo OBJEXT = o PACKAGE = csql PACKAGE_BUGREPORT = @@ -167,7 +167,7 @@ host_vendor = pc htmldir = ${docdir} includedir = ${prefix}/include infodir = ${datarootdir}/info -install_sh = /home/praba/csql/latest/jan/jan28/csql/install-sh +install_sh = /home/praba/csql/latest/jan/jan29/csql/install-sh libdir = ${exec_prefix}/lib libexecdir = ${exec_prefix}/libexec localedir = ${datarootdir}/locale @@ -176,7 +176,7 @@ mandir = ${datarootdir}/man mkdir_p = mkdir -p -- oldincludedir = /usr/include pdfdir = ${docdir} -prefix = /home/praba/csql/latest/jan/jan28/csql/install +prefix = /home/praba/csql/latest/jan/jan29/csql/install program_transform_name = s,x,x, psdir = ${docdir} sbindir = ${exec_prefix}/sbin diff --git a/src/storage/DataType.cxx b/src/storage/DataType.cxx index fa2f606e..0449f0b4 100644 --- a/src/storage/DataType.cxx +++ b/src/storage/DataType.cxx @@ -498,6 +498,19 @@ DataType AllDataType::convertFromSQLType(SQLSMALLINT type) } void AllDataType::copyVal(void* dest, void *src, DataType type, int length) { + //Performance optimization. putting likely case first + if (typeInt == type ) + { + *(int*)dest = *(int*)src; + return; + }else if (typeString == type) + { + strncpy((char*)dest, (char*)src, length); + char *d =(char*)dest; + d[length-1] = '\0'; + return; + } + switch(type) { case typeInt: @@ -545,9 +558,15 @@ void AllDataType::copyVal(void* dest, void *src, DataType type, int length) default: break; } + return; } void AllDataType::addVal(void* dest, void *src, DataType type) { + if (type == typeInt) + { + *(int*)dest = *(int*)dest + *(int*)src; + return; + } switch(type) { case typeInt: @@ -576,7 +595,7 @@ void AllDataType::addVal(void* dest, void *src, DataType type) case typeDate: case typeTime: case typeTimeStamp: - case typeBinary: + case typeBinary: default: break; } @@ -654,6 +673,11 @@ void AllDataType::mulVal(void* dest, void *src, DataType type) } void AllDataType::mudVal(void* dest, void *src, DataType type) { + if (type == typeInt) + { + *(int*)dest = *(int*)dest % (*(int*)src); + return; + } switch(type) { case typeInt: @@ -689,6 +713,11 @@ void AllDataType::mudVal(void* dest, void *src, DataType type) } void AllDataType::divVal(void* dest, void *src, DataType type) { + if (type == typeInt) + { + *(int*)dest = *(int*)dest / (*(int*)src); + return; + } switch(type) { case typeInt: @@ -724,6 +753,11 @@ void AllDataType::divVal(void* dest, void *src, DataType type) } void AllDataType::divVal(void* dest, int src, DataType type) { + if(type == typeInt) + { + *(int*)dest = *(int*)dest / src; + return; + } switch(type) { case typeInt: @@ -762,15 +796,13 @@ void AllDataType::divVal(void* dest, int src, DataType type) bool AllDataType::compareVal(void *val1, void *val2, ComparisionOp op, DataType type, long length) { - bool result = false; - //Performance optimization. putting likely case first if (typeInt == type && OpEquals == op) { if (*(int*)val1 == *(int*)val2) return true; else return false; } - + bool result = false; switch(type) { case typeInt: diff --git a/src/storage/JoinTableImpl.cxx b/src/storage/JoinTableImpl.cxx index 2333d053..118cf1a2 100644 --- a/src/storage/JoinTableImpl.cxx +++ b/src/storage/JoinTableImpl.cxx @@ -234,8 +234,43 @@ DbRetVal JoinTableImpl::optimize() pred = NULL; } } + DbRetVal rv = leftTableHdl->optimize(); + if (OK != rv) { + printError(ErrUnknown, "Left handle optimize failed"); + return rv; + } + rv = rightTableHdl->optimize(); + if (OK != rv) { + printError(ErrUnknown, "Left handle optimize failed"); + return rv; + } + optimizeRestrict(); return OK; } +void JoinTableImpl::optimizeRestrict() +{ + ScanType lType = leftTableHdl->getScanType(); + ScanType rType = rightTableHdl->getScanType(); + bool interChange = false; + if (rType == hashIndexScan) interChange = true; + if (rType == treeIndexScan && lType != hashIndexScan) interChange=true; + + if (interChange) { + Table *tmp = leftTableHdl; + leftTableHdl=rightTableHdl; + rightTableHdl = tmp; + } + return; +} +ScanType JoinTableImpl::getScanType() +{ + ScanType lType = leftTableHdl->getScanType(); + ScanType rType = rightTableHdl->getScanType(); + if (lType == hashIndexScan || rType == hashIndexScan) return hashIndexScan; + if (lType == treeIndexScan || rType == treeIndexScan) return treeIndexScan; + return fullTableScan; +} + void JoinTableImpl::printPlan(int space) { char spaceBuf[IDENTIFIER_LENGTH]; diff --git a/src/storage/TableImpl.cxx b/src/storage/TableImpl.cxx index d9263995..a3e577e7 100644 --- a/src/storage/TableImpl.cxx +++ b/src/storage/TableImpl.cxx @@ -165,14 +165,8 @@ void TableImpl::clearFldNull(int colpos) return; } - -DbRetVal TableImpl::execute() +DbRetVal TableImpl::optimize() { - if (NULL != iter) - { - printError(ErrAlready,"Scan already open:Close and re execute"); - return ErrAlready; - } //table ptr is set in predicate because it needs to access the //type and length to evaluate if( NULL != pred_) @@ -182,8 +176,18 @@ DbRetVal TableImpl::execute() pred->setProjectionList(NULL); pred->setOffsetAndType(); } + return createPlan(); +} + +DbRetVal TableImpl::execute() +{ + if (NULL != iter) + { + printError(ErrAlready,"Scan already open:Close and re execute"); + return ErrAlready; + } DbRetVal ret = OK; - ret = createPlan(); + ret = optimize(); if (OK != ret) { printError(ErrSysInternal,"Unable to create the plan"); -- 2.11.4.GIT