1 2 Previous Next 23 Replies Latest reply on May 13, 2017 7:36 AM by Mustafa KALAYCI

    Performance issue with sql using user defined function

    MohammedImran

      Hello Experts,

       

      My requirement is simple. I need to convert tables data into equivalent binary value and insert it into corresponding table(The tables whose data needs to be converted are quite high may be in hundreds).

      Function (below) to convert from decimal to binary

       

      FUNCTION dec2bin (InputData IN NUMBER,NumberOfBits In Number) RETURN Varchar2 RESULT_CACHE IS
        binval VARCHAR2(64);
        N2  NUMBER := InputData;
      BEGIN
        IF InputData is not null then
           WHILE ( N2 > 0 ) LOOP
              binval := MOD(N2, 2) || binval;
              N2 := TRUNC( N2 / 2 );
           END LOOP;
          
           IF NumberOfBits IS NOT NULL THEN
            binval := LPad(binval,NumberOfBits,'0');
           END IF;
         END IF;
          
        RETURN binval;
      END dec2bin;
      

       

      now when I call the function above on my table (size : 10k rows) it is taking 1Min 40Secs.

       

      Since there are no build in function available to convert string/number value to binary I have to use the function above.

      creating a functional index on table columns and changing function as deterministic may solve the problem but doing this activity on hundreds of tables look difficult.

       

      SELECT dec2bin( DATA_COUNT, 16 ) DATA_COUNT, dec2bin( LOCDATA, 32 ) LOCDATA, dec2bin( HEIGHTDATA, 32 ) HEIGHTDATA
        FROM MATERIAL_DATA
       WHERE id = 'MTR1';
      

       

      Above is the sample query that converts 3 columns to binary.

       

      Please let me know how to go ahead with fixing this performance issue.

       

      DB Version: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

       

      Thanks,

      Imran.

        • 1. Re: Performance issue with sql using user defined function
          Mustafa KALAYCI

          Hi,

           

          you can check if NumberofBits is bigger than the length of binval string (if you don't want to truncate your data of course):

           

            IF NumberOfBits IS NOT NULL and NumberOfBits > length(binval)THEN

              binval := LPad(binval,NumberOfBits,'0');

            END IF;

           

          also please post your query's execution plan, let's see if rows that id values are 'MTR1' are fetching fast enough.

          • 2. Re: Performance issue with sql using user defined function
            MohammedImran

            Hi,

             

            Thanks for advice on length value, I will be using this going forward.

             

            let's see if rows that id values are 'MTR1' are fetching fast enough.

            I have already verified this by removing dec2bin from select clause and simply querying the table(with where clause as above) fetching the result in millisec. No issues with execution plan of query.

             

            --Imran

            • 3. Re: Performance issue with sql using user defined function
              Srinivas Vanahalli

              Your function looks to be ideal for deterministic  to me.

              • 4. Re: Performance issue with sql using user defined function
                Nimish Garg

                First check how much time it is taking to simply fetch the data from table using

                 

                1. SELECT DATA_COUNT, LOCDATA, HEIGHTDATA 
                2.   FROM MATERIAL_DATA 
                3. WHERE id = 'MTR1';

                 

                 

                • 5. Re: Performance issue with sql using user defined function
                  Andrew Sayer

                  Why do you have to do this conversion? What's the full business requirement?

                   

                  What happens when further rows get added to your table or when rows get updated?

                   

                  As the function is pure plsql you might gain quick and easy benefits from https://www.google.co.uk/amp/s/mwidlake.wordpress.com/2015/11/04/pragma-udf-speeding-up-your-plsql-functions-called-from…

                   

                  A note on the result cache, do you really have the same numbers going through the function? You might be better off without it (the overhead of sticking values in memory might be too much to be worth the hassle). Using scalar subquery caching for low NDV columns might be useful (I believe the limit is 256 values pre 12c and 1000s in 12c but don't quote me on that)

                   

                  -edit

                  Not forgetting, if this is an uncommon operation that is business critical then perhaps enabling parallelism on the function is a decent idea. Not sure how that will fit in with pragma udf though (an excercise for the reader)

                  • 6. Re: Performance issue with sql using user defined function
                    BluShadow

                    You could, of course, just do it in SQL...

                    Ok, it looks a little more work, but it keeps all the work in the SQL engine and removes any need for SQL to PL/SQL context switches...

                     

                    SQL> ed
                    Wrote file afiedt.buf

                      1  with t(n) as (select 1 from dual union all
                      2                select 123 from dual union all
                      3                select 12345 from dual union all
                      4                select 123456 from dual
                      5              )
                      6      ,b as (select n
                      7                  ,sign(bitand(n, power(2,23)))||
                      8                    sign(bitand(n, power(2,22)))||
                      9                    sign(bitand(n, power(2,21)))||
                    10                    sign(bitand(n, power(2,20)))||
                    11                    sign(bitand(n, power(2,19)))||
                    12                    sign(bitand(n, power(2,18)))||
                    13                    sign(bitand(n, power(2,17)))||
                    14                    sign(bitand(n, power(2,16)))||
                    15                    sign(bitand(n, power(2,15)))||
                    16                    sign(bitand(n, power(2,14)))||
                    17                    sign(bitand(n, power(2,13)))||
                    18                    sign(bitand(n, power(2,12)))||
                    19                    sign(bitand(n, power(2,11)))||
                    20                    sign(bitand(n, power(2,10)))||
                    21                    sign(bitand(n, power(2,9)))||
                    22                    sign(bitand(n, power(2,8)))||
                    23                    sign(bitand(n, power(2,7)))||
                    24                    sign(bitand(n, power(2,6)))||
                    25                    sign(bitand(n, power(2,5)))||
                    26                    sign(bitand(n, power(2,4)))||
                    27                    sign(bitand(n, power(2,3)))||
                    28                    sign(bitand(n, power(2,2)))||
                    29                    sign(bitand(n, power(2,1)))||
                    30                    sign(bitand(n, power(2,0))) as bin
                    31              from  t
                    32            )
                    33  --
                    34  select n, bin
                    35* from  b
                    SQL> /

                            N BIN
                    ---------- ------------------------------
                             1 000000000000000000000001
                           123 000000000000000001111011
                         12345 000000000011000000111001
                        123456 000000011110001001000000

                     

                     

                    You just need to cater for the number of bits you want to deal with.

                    1 person found this helpful
                    • 7. Re: Performance issue with sql using user defined function
                      MohammedImran

                      Hi,

                       

                      It takes 10secs for simple fetch but 1Min 40secs for query with user defined function.

                       

                      --Imran.

                      • 8. Re: Performance issue with sql using user defined function
                        MohammedImran

                        I can only say you that conversion from decimal to binary is part of business requirement.

                         

                        Though your link gave good information on "Pragma UDF" but is of no use to me as I am working on 11g version.

                         

                        Yes, including result cache in function definition could be risky and should be taken care while using it!

                         

                         

                        --Imran.

                        • 9. Re: Performance issue with sql using user defined function
                          BluShadow

                          You could also consider using virtual columns as you're on 11g

                           

                          SQL> create table t(n number
                            2                ,b varchar2(960)
                            3                  generated always as (
                            4                    sign(bitand(n, power(2,23)))||
                            5                    sign(bitand(n, power(2,22)))||
                            6                    sign(bitand(n, power(2,21)))||
                            7                    sign(bitand(n, power(2,20)))||
                            8                    sign(bitand(n, power(2,19)))||
                            9                    sign(bitand(n, power(2,18)))||
                          10                    sign(bitand(n, power(2,17)))||
                          11                    sign(bitand(n, power(2,16)))||
                          12                    sign(bitand(n, power(2,15)))||
                          13                    sign(bitand(n, power(2,14)))||
                          14                    sign(bitand(n, power(2,13)))||
                          15                    sign(bitand(n, power(2,12)))||
                          16                    sign(bitand(n, power(2,11)))||
                          17                    sign(bitand(n, power(2,10)))||
                          18                    sign(bitand(n, power(2,9)))||
                          19                    sign(bitand(n, power(2,8)))||
                          20                    sign(bitand(n, power(2,7)))||
                          21                    sign(bitand(n, power(2,6)))||
                          22                    sign(bitand(n, power(2,5)))||
                          23                    sign(bitand(n, power(2,4)))||
                          24                    sign(bitand(n, power(2,3)))||
                          25                    sign(bitand(n, power(2,2)))||
                          26                    sign(bitand(n, power(2,1)))||
                          27                    sign(bitand(n, power(2,0)))
                          28                  )
                          29                  )
                          30  /

                           

                          Table created.

                           

                          SQL> insert into t (n) values (1);

                           

                          1 row created.

                           

                          SQL> insert into t (n) values (123);

                           

                          1 row created.

                           

                          SQL> insert into t (n) values (12345);

                           

                          1 row created.

                           

                          SQL> insert into t (n) values (123456);

                           

                          1 row created.

                           

                          SQL> select * from t;

                                  N B
                          ---------- --------------------------------
                                   1 000000000000000000000001
                                 123 000000000000000001111011
                               12345 000000000011000000111001
                              123456 000000011110001001000000

                          1 person found this helpful
                          • 10. Re: Performance issue with sql using user defined function
                            Paulzip

                            It would be more efficient to convert to hex (using Oracle's to_char(..., 'xxxx') which is written in C, so fast) and then process byte by byte using a lookup.  In my tests for the first million values, your approach = 24 secs, my approach = 16 secs.  Also, consider native compilation, which should give you further improvements.

                             

                            create or replace function dec2bin(inputdata in number, numberofbits in number default null) return varchar2 result_cache is

                              vhex varchar2(20);

                              binval varchar2(64);

                            begin

                              if inputdata is not null then

                                vhex := to_char(inputdata, 'fmxxxxxxxxxx');

                                for n in 1..length(vhex)

                                loop

                                  binval := binval ||

                                   case substr(vhex, n, 1)

                                     when '0' then '0000'

                                     when '1' then '0001'

                                     when '2' then '0010'

                                     when '3' then '0011'

                                     when '4' then '0100'

                                     when '5' then '0101'

                                     when '6' then '0110'

                                     when '7' then '0111'

                                     when '8' then '1000'

                                     when '9' then '1001'

                                     when 'a' then '1010'

                                     when 'b' then '1011'

                                     when 'c' then '1100'

                                     when 'd' then '1101'

                                     when 'e' then '1110'

                                     when 'f' then '1111'

                                   end;

                                end loop;

                                if numberofbits is not null and numberofbits > length(binval) then

                                  binval := lpad(binval,numberofbits,'0');

                                end if;    

                              end if;

                              return binval;

                            end;

                            • 11. Re: Performance issue with sql using user defined function
                              Mustafa KALAYCI

                              in my tests, I removed result cache so I can test more than one time with real calculation time, OP's code runs in 0.9 seconds for 100.000 iteration and yours run 0.4. very good solution Paulzip.

                               

                              just realized something, CASE is much much worse then if else structure. while testing myself, I replaced your case statement with if then else and it works around 0.2 now

                               

                              please remember I removed result cache for both codes.

                               

                              create or replace function dec2bin_hex(inputdata in number, numberofbits in number default null) return varchar2 is
                              
                                vhex varchar2(20);
                              
                                binval varchar2(64);
                                x_part char(1);
                              begin
                              
                                if inputdata is not null then
                              
                                  vhex := to_char(inputdata, 'fmxxxxxxxxxx');
                              
                                  for n in 1..length(vhex)
                              
                                  loop
                              
                                    
                                     x_part := substr(vhex, n, 1);   
                                     if x_part = '0' then 
                                       binval := binval || '0000'     ;  
                                     elsif x_part = '1' then 
                                       binval := binval || '0001'  ;     
                                     elsif x_part = '2' then 
                                       binval := binval || '0010'  ;     
                                     elsif x_part = '3' then 
                                       binval := binval || '0011'  ;                                                                       
                                     elsif x_part = '4' then 
                                       binval := binval || '0100'  ;                                                                       
                                     elsif x_part = '5' then 
                                       binval := binval || '0101'  ;                                                                       
                                     elsif x_part = '6' then 
                                       binval := binval || '0110'  ;                                                                       
                                     elsif x_part = '7' then 
                                       binval := binval || '0111'  ;                                                                       
                                     elsif x_part = '8' then 
                                       binval := binval || '1000'  ;                                                                       
                                     elsif x_part = '9' then 
                                       binval := binval || '1001'  ;                                                                       
                                     elsif x_part = 'a' then 
                                       binval := binval || '1010'  ;                                                                       
                                     elsif x_part = 'b' then 
                                       binval := binval || '1011'  ;                                                                       
                                     elsif x_part = 'c' then 
                                       binval := binval || '1100'  ;                                                                       
                                     elsif x_part = 'd' then 
                                       binval := binval || '1101'  ;                                                                       
                                     elsif x_part = 'e' then 
                                       binval := binval || '1110'  ;                                                                       
                                     elsif x_part = 'f' then 
                                       binval := binval || '1111'  ;
                                     end if;
                              
                                  end loop;
                              
                                  if numberofbits is not null and numberofbits > length(binval) then
                                    binval := lpad(binval,numberofbits,'0');
                                  end if;    
                              
                                end if;
                              
                                return binval;
                              
                              end;
                              

                               

                              here is my test case:

                              declare
                                xx varchar2(64);
                              begin
                                for i in 200001..300000 loop
                                  xx := dec2bin_hex(i,32);
                                end loop;
                              end;
                              
                              • 12. Re: Performance issue with sql using user defined function
                                ascheffer

                                Another SQL version, using the model clause

                                with t(n) as ( select 1 from dual union all
                                              select 123 from dual union all
                                              select 12345 from dual union all
                                              select 123456 from dual
                                            )
                                select n
                                    , ( select lpad( bs, 32, '0' )
                                        from dual
                                          model
                                            dimension by (0 as i )
                                            measures(n as bn, cast( null as varchar2(128) ) as bs)  
                                            rules iterate (256 ) until bn[0] = 0
                                              ( bs[0] = bitand( bn[0], 1 ) || bs[0]
                                              , bn[0] = trunc( bn[0] / 2 )
                                              )
                                      )
                                from t
                                
                                
                                • 13. Re: Performance issue with sql using user defined function
                                  BluShadow

                                  ascheffer wrote:

                                   

                                  Another SQL version, using the model clause

                                  1. witht(n)as(select1fromdualunionall
                                  2. select123fromdualunionall
                                  3. select12345fromdualunionall
                                  4. select123456fromdual
                                  5. )
                                  6. selectn
                                  7. ,(selectlpad(bs,32,'0')
                                  8. fromdual
                                  9. model
                                  10. dimensionby(0asi)
                                  11. measures(nasbn,cast(nullasvarchar2(128))asbs)
                                  12. rulesiterate(256)untilbn[0]=0
                                  13. (bs[0]=bitand(bn[0],1)||bs[0]
                                  14. ,bn[0]=trunc(bn[0]/2)
                                  15. )
                                  16. )
                                  17. fromt

                                   

                                   

                                  not so easy to include as a virtual column though. 

                                  • 14. Re: Performance issue with sql using user defined function
                                    Paulzip

                                    Mustafa KALAYCI wrote:

                                     

                                    in my tests, I removed result cache so I can test more than one time with real calculation time, OP's code runs in 0.9 seconds for 100.000 iteration and yours run 0.4. very good solution Paulzip.

                                     

                                    just realized something, CASE is much much worse then if else structure. while testing myself, I replaced your case statement with if then else and it works around 0.2 now

                                     

                                    please remember I removed result cache for both codes.

                                    In my tests, this is even quicker (with or without result_cache) :

                                     

                                    create or replace function dec2bin_hex(inputdata in number, numberofbits in number) return varchar2 result_cache is

                                      vBins varchar2(100) := '0000000100100011010001010110011110001001101010111100110111101111';

                                      vhex varchar2(20);

                                      binval varchar2(64);

                                    begin

                                      if inputdata is not null then

                                        vhex := to_char(inputdata, 'fmxxxxxxxxxx');

                                        for n in 1..length(vhex)

                                        loop

                                          binval := binval || substr(vBins, (instr('0123456789abcdef', substr(vhex, n, 1)) * 4) - 3, 4);

                                        end loop;

                                        if numberofbits is not null and numberofbits > length(binval) then

                                          binval := lpad(binval,numberofbits,'0');

                                        end if;    

                                      end if;

                                      return binval;

                                    end;

                                     

                                    I also tried varray lookup, to_number(...., 'x') to get the index position, but these were slower.

                                    1 2 Previous Next