From 763f3300c5e737efe86d53fd356051982123b6d1 Mon Sep 17 00:00:00 2001 From: prabatuty Date: Wed, 28 Jan 2009 15:53:06 +0000 Subject: [PATCH] optimizing single table aggregate queries with no index --- include/Statement.h | 7 ++++- include/TableImpl.h | 2 ++ src/sql/SelStatement.cxx | 73 ++++++++++++++++++++++++++++++++++++++++----- src/sql/Statement.h | 5 ++++ src/storage/TableImpl.cxx | 75 ++++++++++++++++++++++++++++++++++++++++++++++- 5 files changed, 153 insertions(+), 9 deletions(-) diff --git a/include/Statement.h b/include/Statement.h index 6c46adcb..2c733441 100644 --- a/include/Statement.h +++ b/include/Statement.h @@ -134,6 +134,8 @@ class SelStatement : public DmlStatement char **bindFieldValues; int totalFields; + bool isPointReturned; + bool handleAggWithTbl; DbRetVal execute(int &rowsAffected); DbRetVal setParam(int paramNo, void *value); @@ -159,11 +161,14 @@ class SelStatement : public DmlStatement DbRetVal close(); DbRetVal freeScan(); DbRetVal resolve(); + + void* handleSingleTableAggWithoutGroup(); + SelStatement(); ~SelStatement(); DbRetVal openTables(); - //DbRetVal resolveGroupFld(AggTableImpl *impl); +// DbRetVal resolveGroupFld(AggTableImpl *impl); DbRetVal setBindField(int pos, void* value); diff --git a/include/TableImpl.h b/include/TableImpl.h index 1cd33de8..b742a967 100644 --- a/include/TableImpl.h +++ b/include/TableImpl.h @@ -25,6 +25,7 @@ #include #include #include +#include//for AggType enum ScanType { fullTableScan = 0, @@ -203,6 +204,7 @@ class TableImpl:public Table void* fetch(DbRetVal &rv); void* fetchNoBind(); void* fetchNoBind(DbRetVal &rv); + DbRetVal fetchAgg(const char *fldName, AggType aType, void *buf); DbRetVal close(); DbRetVal closeScan(); diff --git a/src/sql/SelStatement.cxx b/src/sql/SelStatement.cxx index 660ad064..7e919631 100644 --- a/src/sql/SelStatement.cxx +++ b/src/sql/SelStatement.cxx @@ -14,6 +14,7 @@ * * ***************************************************************************/ #include "Statement.h" +#include SelStatement::SelStatement() { @@ -26,14 +27,16 @@ SelStatement::SelStatement() bindFields = NULL; bindFieldValues = NULL; totalFields = 0; + isPointReturned = false; + handleAggWithTbl=false; } SelStatement::~SelStatement() { if (table) { table->setCondition(NULL); - //if (dbMgr) dbMgr->closeTable(table); table->close(); + //if (dbMgr) dbMgr->closeTable(table); delete table; } if (totalParams) { @@ -82,6 +85,7 @@ DbRetVal SelStatement::execute(int &rowsAffected) } AllDataType::copyVal(value->value, paramValues[i], value->type, value->length); } + rv = table->execute(); //table->printPlan(0); return rv; @@ -292,6 +296,12 @@ DbRetVal SelStatement::resolve() FieldName *name = NULL; FieldInfo *fInfo = new FieldInfo(); List bindFldList; + bool isSingleTableNoGrp = false; + if(parsedData->getTableNameList().size() == 1 && + parsedData->getGroupFieldNameList().size() == 0) + { + isSingleTableNoGrp = true; + } AggTableImpl *aggTable = NULL; while (iter.hasElement()) { @@ -315,7 +325,8 @@ DbRetVal SelStatement::resolve() delete fInfo; return rv; } - if (parsedData->getGroupFieldNameList().size()!= 0) + if (parsedData->getGroupFieldNameList().size()!= 0 + && !isSingleTableNoGrp) { if (!aggTable) aggTable = new AggTableImpl(); @@ -348,7 +359,8 @@ DbRetVal SelStatement::resolve() while (it.hasElement()) { bFldName = (FieldName*)it.nextElement(); - if(0==strcmp(bFldName->fldName,name->fldName)) + if(0==strcmp(bFldName->fldName,name->fldName) && + name->aType == AGG_UNKNOWN) { newVal->value=table->getBindFldAddr(name->fldName); newVal->isAllocVal=false; @@ -367,13 +379,16 @@ DbRetVal SelStatement::resolve() if (name->aType ==AGG_UNKNOWN && parsedData->getGroupFieldNameList().size()== 0) table->bindFld(name->fldName, newVal->value); - else { + else if (!isSingleTableNoGrp) + { if (!aggTable) { aggTable = new AggTableImpl(); aggTable->setTable(table); } aggTable->bindFld(name->fldName, name->aType, newVal->value); } + if (name->aType !=AGG_UNKNOWN && isSingleTableNoGrp) + handleAggWithTbl= true; parsedData->insertFieldValue(newVal); } if (!isBindFld) bindFldList.append(name); @@ -418,7 +433,9 @@ DbRetVal SelStatement::resolve() } DbRetVal SelStatement::resolveGroupFld(AggTableImpl *aggTable) { - if (!aggTable) return OK; + if (!aggTable) { + return OK; + } ListIterator giter = parsedData->getGroupFieldNameList().getIterator(); FieldName *name = NULL; DbRetVal rv = OK; @@ -601,9 +618,36 @@ DbRetVal SelStatement::resolveForCondition() } return OK; } - +void* SelStatement::handleSingleTableAggWithoutGroup() +{ + if (isPointReturned) return NULL; + TableImpl *tblImpl = (TableImpl*)table; + ListIterator iter = parsedData->getFieldNameList().getIterator(); + int i=0; + DbRetVal rv = OK; + FieldName *name; + FieldValue *fVal = NULL; + while (iter.hasElement()) + { + name = (FieldName*) iter.nextElement(); + fVal = bindFields[i]; + + //rv = tblImpl->fetchAgg(name, (int) name->aType, fVal->value); + rv = tblImpl->fetchAgg(name->fldName, name->aType, fVal->value); + if (OK != rv) return NULL; + i++; + tblImpl->closeScan(); + tblImpl->execute(); + } + isPointReturned = true; + return fVal; +} void* SelStatement::fetch() { + if(handleAggWithTbl) + { + return handleSingleTableAggWithoutGroup(); + } void *tuple = table->fetch(); if (NULL == tuple) return NULL; //copy values to binded buffer @@ -623,6 +667,10 @@ void* SelStatement::fetch() void* SelStatement::fetch(DbRetVal &rv) { + if(handleAggWithTbl) + { + return handleSingleTableAggWithoutGroup(); + } void *tuple = table->fetch(rv); if (NULL == tuple) return NULL; //copy values to binded buffer @@ -642,6 +690,7 @@ void* SelStatement::fetch(DbRetVal &rv) DbRetVal SelStatement::close() { + isPointReturned = false; return table->closeScan(); } @@ -693,7 +742,13 @@ int SelStatement::getFieldLength( int pos ) void* SelStatement::fetchAndPrint(bool SQL) { - void *tuple = table->fetch(); + void *tuple = NULL; + if(handleAggWithTbl) + { + tuple = handleSingleTableAggWithoutGroup(); + }else { + tuple = table->fetch(); + } if (NULL == tuple) return NULL; FieldValue *value; bool nullValueSet; @@ -757,6 +812,10 @@ void* SelStatement::fetchAndPrint(bool SQL) void* SelStatement::next() { + if(handleAggWithTbl) + { + return handleSingleTableAggWithoutGroup(); + } return( table->fetch() ); } diff --git a/src/sql/Statement.h b/src/sql/Statement.h index 1163f3ce..2c733441 100644 --- a/src/sql/Statement.h +++ b/src/sql/Statement.h @@ -134,6 +134,8 @@ class SelStatement : public DmlStatement char **bindFieldValues; int totalFields; + bool isPointReturned; + bool handleAggWithTbl; DbRetVal execute(int &rowsAffected); DbRetVal setParam(int paramNo, void *value); @@ -159,6 +161,9 @@ class SelStatement : public DmlStatement DbRetVal close(); DbRetVal freeScan(); DbRetVal resolve(); + + void* handleSingleTableAggWithoutGroup(); + SelStatement(); ~SelStatement(); diff --git a/src/storage/TableImpl.cxx b/src/storage/TableImpl.cxx index 83e968c2..ab6145ca 100644 --- a/src/storage/TableImpl.cxx +++ b/src/storage/TableImpl.cxx @@ -23,6 +23,7 @@ #include #include #include +#include //for AggType void Table::getFieldNameAlone(char *fname, char *name) { bool dotFound= false; @@ -63,6 +64,7 @@ DbRetVal TableImpl::bindFld(const char *name, void *val) } bool TableImpl::isFldNull(const char *name){ + if (name[0] == '*') return false; char fieldName[IDENTIFIER_LENGTH]; getFieldNameAlone((char*)name, fieldName); int colpos = fldList_.getFieldPosition(fieldName); @@ -417,7 +419,78 @@ void* TableImpl::fetchNoBind(DbRetVal &rv) } return curTuple_; } - +DbRetVal TableImpl::fetchAgg(const char * fldName, AggType aType, void *buf) +{ + FieldInfo *info = new FieldInfo(); + DbRetVal rv = getFieldInfo(fldName, info); + if (OK != rv) return rv; + bool res= false; + + char *tuple = (char*) fetchNoBind(rv); + if ( NULL == tuple) + { + *(int*)buf = 0; //assuming int. could create porting problems(64 |endian) + return OK; + } + int count =1; + AllDataType::copyVal(buf, (void*) (tuple+info->offset), info->type, info->length); + while(1) { + tuple = (char*) fetchNoBind(rv); + if (NULL == tuple) break; + switch(aType) { + case AGG_MIN: + { + res = AllDataType::compareVal(buf, (void*) (tuple+info->offset), + OpGreaterThan, + info->type, info->length); + if (res) AllDataType::copyVal(buf, (void*) (tuple+info->offset), + info->type, info->length); + break; + } + case AGG_MAX: + { + res = AllDataType::compareVal(buf, (void*) (tuple+info->offset), + OpLessThan, + info->type, info->length); + if (res) AllDataType::copyVal(buf, (void*) (tuple+info->offset), + info->type, info->length); + break; + } + case AGG_SUM: + { + AllDataType::addVal(buf, (void*) (tuple+info->offset), + info->type); + break; + } + case AGG_AVG: + { + AllDataType::addVal(buf, (void*) (tuple+info->offset), + info->type); + count++; + break; + } + case AGG_COUNT: + { + count++; + break; + } + } + } + switch(aType) { + case AGG_AVG: + { + AllDataType::divVal(buf, &count,info->type); + break; + } + case AGG_COUNT: + { + (*(int*)buf) = count; + break; + } + } + delete info; + return OK; +} DbRetVal TableImpl::insertTuple() { DbRetVal ret =OK; -- 2.11.4.GIT