Informix

 View Only
  • 1.  UDR improvement suggestions

    Posted Sun November 07, 2021 09:19 PM
    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


  • 2.  RE: UDR improvement suggestions

    IBM Champion
    Posted Sun November 07, 2021 09:36 PM
    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





  • 3.  RE: UDR improvement suggestions

    IBM Champion
    Posted Mon November 08, 2021 06:19 AM
    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
    ------------------------------



  • 4.  RE: UDR improvement suggestions

    IBM Champion
    Posted Mon November 08, 2021 08:41 AM

    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

     

     






  • 5.  RE: UDR improvement suggestions

    IBM Champion
    Posted Mon November 08, 2021 08:55 AM

    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);

    }

     

     






  • 6.  RE: UDR improvement suggestions

    IBM Champion
    Posted Mon November 08, 2021 09:24 AM
    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


    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.








  • 7.  RE: UDR improvement suggestions

    IBM Champion
    Posted Mon November 08, 2021 09:29 AM

    No faster way to  crash an engine than writing C UDRs .... OK maybe OAT

     

    Cheers

    Paul

     






  • 8.  RE: UDR improvement suggestions

    Posted Mon November 08, 2021 01:13 PM
    Thank you Paul, will have my client try this out!  Appreciate your help.

    ------------------------------
    Vijay Lolabattu
    ------------------------------