UDR improvement suggestions
Vijay Lolabattu asked how to speed up a C UDR that strips non-digits from phone numbers, which made a query six times slower. Paul Watson suggested the regular expression blade. Art Kagel posted a revised loop, but Paul pointed out mi_get_vardata does not return a null-terminated string, so it could crash the engine. Paul posted a tweaked version at about 0.002 ms per call versus 0.003. Vijay's client will try it.
Auto-generated by Claude from the posts below — may be imperfect; read the full thread.
In the following SQL:
SELECT fname, lname, ssn from tab1 where lname like "%vijay%" AND ( telephonenumber IN ((123)456-7890 , ......, ....) OR homephone IN (+123 456-7890, ......, ....... ) OR mobile IN(123-456-7890, ....,.....,....)Custer wrote an UDR to take out all extra non-digit characters from the telephone number string but the UDR call increasing the query execution time by 6 times. Wondering if this UDR can be improved?
Select using the UDR:
SELECT fname, lname, ssn from tab1 where lname like "%vijay%" AND (sanitize_phonenumber( telephonenumber) IN ((123)456-7890 ) OR (sanitize_phonenumber(homephone) IN (+123 456-7890 ) OR (sanitize_phonenumber(mobile) IN(123-456-7890).....UDR code:
CREATE FUNCTION sanitize_phonenumber(lvarchar)
RETURNING varchar(255)
WITH(NOT VARIANT, PARALLELIZABLE, PERCALL_COST=50, SELCONST=0.0001)EXTERNAL NAME "$INFORMIXDIR/extend/ccm/libdbfuncs.so(sanitize_phonenumber)"
LANGUAGE C;
// remove formatting characters from phone number
mi_lvarchar *sanitize_phonenumber(mi_lvarchar *mi_source)
{
int len;
int i, j = 0;
char *pSource = 0, c;
mi_char *s;
mi_lvarchar *ret_string = 0;
s = mi_zalloc(256);
if (s == (mi_char *) NULL)
{
mi_db_error_raise(NULL, MI_EXCEPTION, "mi_alloc failed\\n");
return 0; // unreachable, here to satisfy static analysis
}
len = (int) mi_get_varlen(mi_source);
if (len > 0)
{
pSource = mi_get_vardata(mi_source);
}
if (len > 255)
len = 255;
for ( i = 0; i < len; i++)
{
c = tolower(pSource[i]);
switch(c) {
case '0':
case '1':
case '2':
case '3':
case '4':
case '5':
case '6':
case '7':
case '8':
case '9':
case 'a':
case 'b':
case 'c':
case 'd':
case '+':
case ',':
case '*':
case '#':
s[j++] = c;
break;
default:
c = '\\0'; // To supress warnings about missing default
}
} // end for
s[j] = '\\0'; // terminate
ret_string = mi_new_var(strlen(s));
mi_set_vardata(ret_string, s);
mi_free(s);
return (ret_string);
} // end sanitize_phonenumber
------------------------------
Vijay Lolabattu
------------------------------
#Informix
Use the regular expression blade ? Cheers Paul Paul Watson Oninit LLC +1-913-387-7529 www.oninit.com Oninit® is a registered trademark of Oninit LLC
Vijay: Minor improvement, but maybe that's all that's needed: // remove formatting characters from phone number mi_lvarchar *sanitize_phonenumber(mi_ lvarchar *mi_source) { int len; int i = 0, j = 0; char *pSource = 0, c; mi_char *s; mi_lvarchar *ret_string = 0; s = mi_zalloc(256); if (s == (mi_char *) NULL) { mi_db_error_raise(NULL, MI_EXCEPTION, "mi_alloc failed\\n"); return 0; // unreachable, here to satisfy static analysis } len = (int) mi_get_varlen(mi_source); if (len > 0) { pSource = mi_get_vardata(mi_source); } if (len > 255) len = 255; while (pSource[i] != (char)0) { c = tolower(pSource[i]); switch(c) { case '0': case '1': case '2': case '3': case '4': case '5': case '6': case '7': case '8': case '9': case 'a': case 'b': case 'c': case 'd': case '+': case ',': case '*': case '#': s[j++] = c; break; default: c = '\\0'; // To supress warnings about missing default } i++; } // end for s[j] = '\\0'; // terminate ret_string = mi_new_var(strlen(s)); mi_set_vardata(ret_string, s); mi_free(s); return (ret_string); } // end sanitize_phonenumber ------------------------------ Art S. Kagel, President and Principal Consultant ASK Database Management Corp. www.askdbmgt.com ------------------------------
mi_get_vardata doesn't return a null terminated string so pSource = mi_get_vardata(mi_source); while (pSource[i] != (char)0) will probably crash the engine Cheers Paul
Testing the code I see a run time of 0.003 ms per call. The code below is 0.002ms per call ( not robustly tested) mi_lvarchar *oni_sanitize_phonenumber(mi_lvarchar *src) { mi_integer len; int i, j = 0; char *p = 0; char c; mi_char s[256]; len = mi_get_varlen(src); if (len > 0) p = mi_get_vardata(src); if (len > 255) len = 255; p[len]=0; i=0; while(p[i]!= (char)0) { c = tolower(p[i]); switch(c) { case '0': case '1': case '2': case '3': case '4': case '5': case '6': case '7': case '8': case '9': case 'a': case 'b': case 'c': case 'd': case '+': case ',': case '*': case '#': s[j++] = c; break; default: c = '\\0'; // To supress warnings about missing default } } // end for s[j] = '\\0'; // terminate mi_set_varlen(src, j); mi_set_vardata(src, s); return (src); }
Paul: Duh! You are right of course. Should heed my own advice. I forgot to take off my "C" head and put on my "UDR" head before commenting. Got to go reread the slides for my Best Practices presentation! Art Art S. Kagel, President and Principal Consultant ASK Database Management www.askdbmgt.com Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves.
No faster way to crash an engine than writing C UDRs .... OK maybe OAT Cheers Paul
Thank you Paul, will have my client try this out! Appreciate your help. ------------------------------ Vijay Lolabattu ------------------------------