Hi

I have done some fun with Monetdb. i have created my own user define xmin function. objective of that function is find minimum value from specific column.

I have writen some code as follow :

/****************************************************************************************/ /* Function Name : xmin */ /****************************************************************************************/ #define UI sht #define UU unsigned short #define UO sht #include "impl_xmin.h" #undef UI #undef UU #undef UO

#define UI int #define UU unsigned short #define UO int #include "impl_xmin.h" #undef UI #undef UU #undef UO

#define UI lng #define UU unsigned short #define UO lng #include "impl_xmin.h" #undef UI #undef UU #undef UO

#define UI dbl #define UU unsigned short #define UO dbl #include "impl_xmin.h" #undef UI #undef UU #undef UO

static str UDFBATxmin_(BAT **ret, BAT *bone) { BAT *bres = NULL; bit two_tail_sorted_unsigned = FALSE; bit two_tail_revsorted_unsigned = FALSE; BUN n; str msg = NULL;

/* assert calling sanity */ assert(ret != NULL);

/* handle NULL pointer */ if (bone == NULL) throw(MAL, "batudf.xmin", RUNTIME_OBJECT_MISSING);

n = BATcount(bone);

/* allocate result BAT */ switch (bone->ttype) { case TYPE_sht: bres = BATnew(TYPE_void, TYPE_sht, TOTALTHREAD); break; case TYPE_int: bres = BATnew(TYPE_void, TYPE_int, TOTALTHREAD); break; case TYPE_lng: bres = BATnew(TYPE_void, TYPE_lng, TOTALTHREAD); break; case TYPE_dbl: bres = BATnew(TYPE_void, TYPE_dbl, TOTALTHREAD); break; default: throw(MAL, "batudf.xmin","tails of input BATs must be one of {sht, int, lng, dbl}"); }

if (bres == NULL) throw(MAL, "batudf.xmin", MAL_MALLOC_FAIL);

/* advice on sequential scan */ BATaccessBegin(bone, USE_TAIL, MMAP_SEQUENTIAL);

/* call type-specific core algorithm */ switch (bone->ttype) { case TYPE_sht: msg = UDFBATxmin_sht_sht ( bres, bone, n,&two_tail_sorted_unsigned, &two_tail_revsorted_unsigned ); break; case TYPE_int: msg = UDFBATxmin_int_int ( bres, bone, n,&two_tail_sorted_unsigned, &two_tail_revsorted_unsigned ); break; case TYPE_lng: msg = UDFBATxmin_lng_lng ( bres, bone, n,&two_tail_sorted_unsigned, &two_tail_revsorted_unsigned ); break; case TYPE_dbl: msg = UDFBATxmin_dbl_dbl ( bres, bone, n,&two_tail_sorted_unsigned, &two_tail_revsorted_unsigned ); break; default: BBPreleaseref(bres->batCacheid); throw(MAL, "batudf.xmin","tails of input BATs must be one of {sht, int, lng, dbl}"); }

BATaccessEnd(bone, USE_TAIL, MMAP_SEQUENTIAL);

if (msg != MAL_SUCCEED) { BBPreleaseref(bres->batCacheid); } else { /* set number of tuples in result BAT */ BATsetcount(bres, 1);

/* set result properties */ bres->hdense = TRUE; /* result head is dense */ BATseqbase(bres, bone->hseqbase); /* result head has same seqbase as input */ bres->hsorted = 1; /* result head is sorted */ bres->hrevsorted = (BATcount(bres) <= 1); BATkey(bres, TRUE); /* result head is key (unique) */

/* Result tail is sorted, if the left/first input tail is * sorted and key (unique), or if the left/first input tail is * sorted and the second/right input tail is sorted and the * second/right tail values are either all >= 0 or all < 0; * otherwise, we cannot tell. */ if (BATtordered(bone) && (BATtkey(bone) || two_tail_sorted_unsigned)) bres->tsorted = 1; else bres->tsorted = (BATcount(bres) <= 1); if (BATtrevordered(bone) && (BATtkey(bone) || two_tail_revsorted_unsigned)) bres->trevsorted = 1; else bres->trevsorted = (BATcount(bres) <= 1); /* result tail is key (unique), iff both input tails are */ /*BATkey(BATmirror(bres), BATtkey(bone) || BATtkey(btwo));*/

*ret = bres; }

return msg; }

/* MAL wrapper */ str UDFBATxmin(bat *ires, bat *ione) { // startTimer(); BAT *bres = NULL, *bone = NULL; str msg = NULL; /* assert calling sanity */ assert(ires != NULL && ione != NULL);

/* bat-id -> BAT-descriptor */ if ((bone = BATdescriptor(*ione)) == NULL) throw(MAL, "batudf.xmin", RUNTIME_OBJECT_MISSING);

/* do the work */ msg = UDFBATxmin_ ( &bres, bone);

/* release input BAT-descriptors */ BBPreleaseref(bone->batCacheid);

if (msg == MAL_SUCCEED) { /* register result BAT in buffer pool */ BBPkeepref((*ires = bres->batCacheid)); } // printf("Total time Required to UDFBATmin = %lf",endTimer()); return msg; }

/****************************************************************************************/ /* Function Name : impl_xmin.h */ /****************************************************************************************/ * impl_xmin.h * * Created on: 23-Nov-2012 * Author: swapnil */

/* concatenate two or five tokens */ #define U_CONCAT_2(a,b) a##b #define U_CONCAT_5(a,b,c,d,e) a##b##c##d##e

/* function names, *_nil & TYPE_* macros */ #define UF(p,i,o,s) U_CONCAT_5(p,i,_,o,s) #define UN(t) U_CONCAT_2(t,_nil) #define UT(t) U_CONCAT_2(TYPE_,t)

static str UF(UDFBATxmin_,UI,UO,) ( BAT *bres, BAT *bone, BUN n, bit *two_tail_sorted_unsigned, bit *two_tail_revsorted_unsigned ) { UI *one = NULL; UO *res = NULL; BUN i;

/* assert calling sanity */ assert(bres != NULL && bone != NULL); assert(BATcapacity(bres) >= 1); assert(BATcount(bone) >= n); assert(bone->ttype == UT(UI)); assert(bres->ttype == UT(UO));

/* get direct access to the tail arrays */ one = (UI*) Tloc(bone, BUNfirst(bone)); res = (UO*) Tloc(bres, BUNfirst(bres));

int loop; startTimer(); res[0]=one[0]; for(loop=1;loop<n;loop++) { if(res[0]>one[loop]) res[0]=one[loop]; } printf("Time for clReleaseMemObject = %lf\n",endTimer());

return MAL_SUCCEED; }

/* undo local defines */ #undef UT #undef UN #undef UF #undef U_CONCAT_5 #undef U_CONCAT_2

//*************************************************************************************************/

when i am executing following SQL statement then it will give result but it takes 10 to 13 seconds I know where i am executing on single core of CPU but when i executing that above statement then, I think it will taking data from disk because disk access indicator led blow when i executing that above SQL statement. means, It was not using system main memory.

I will be wrong.

Can each time it is accessing BAT from disk or from main memory? Can you test me Program ? and Can you give me guidance for accessing BAT?

I hope you will give me proper guidance.

My SQL statement results are as follow sql>select xmin(v1) from demo; +---------+ | xmin_v1 | +=========+ | 1 | +---------+ 1 tuple (3.2s) sql>select xmin(v1) from demo; +---------+ | xmin_v1 | +=========+ | 1 | +---------+ 1 tuple (9.3s) sql>select xmin(v1) from demo; +---------+ | xmin_v1 | +=========+ | 1 | +---------+ 1 tuple (14.3s) sql>select xmin(v1) from demo; +---------+ | xmin_v1 | +=========+ | 1 | +---------+ 1 tuple (3.4s)