Why do these 2 oracle functions perform differently?

Question

I wrote a function in Oracle to convert IP addresses to integers. It seemed slow. I wrote a second function to do the same thing only faster. Unfortunately it ended up slower and I do not know why.

Original function;

FUNCTION GET_IP_INTEGER 
(
  IP_IN IN VARCHAR2  
) RETURN NUMBER AS 
DOT_COUNTER INTEGER;
CURRENT_DOT INTEGER;
LAST_DOT INTEGER := 1;
CURRENT_INTEGER INTEGER := 0;
OUTPUT_INTEGER INTEGER := 0;
BEGIN
  FOR DOT_COUNTER IN 1..3
  LOOP
    CURRENT_DOT := INSTR(IP_IN,'.',LAST_DOT);
    CURRENT_INTEGER := TO_NUMBER(SUBSTR(IP_IN,LAST_DOT,CURRENT_DOT - LAST_DOT));
    LAST_DOT := CURRENT_DOT + 1;
    CASE DOT_COUNTER
      WHEN 1 THEN CURRENT_INTEGER := CURRENT_INTEGER * 16777216;
      WHEN 2 THEN CURRENT_INTEGER := CURRENT_INTEGER * 65536;
      WHEN 3 THEN CURRENT_INTEGER := CURRENT_INTEGER * 256;
    END CASE;
    OUTPUT_INTEGER := OUTPUT_INTEGER + CURRENT_INTEGER;
    CURRENT_INTEGER := 0;
  END LOOP;
  CURRENT_INTEGER := TO_NUMBER(SUBSTR(IP_IN,LAST_DOT));
  OUTPUT_INTEGER := OUTPUT_INTEGER + CURRENT_INTEGER;
  RETURN OUTPUT_INTEGER;
END GET_IP_INTEGER;

It picks everything apart and works well. But I thought I could do better so I wrote this;

FUNCTION GET_IP_INTEGER1 
(
  IP_IN IN VARCHAR2  
) RETURN NUMBER AS 
OCTET_COUNTER INTEGER;
CURRENT_INTEGER INTEGER := 0;
OUTPUT_INTEGER INTEGER := 0;
BEGIN
  FOR OCTET_COUNTER IN 1..4
  LOOP
    CURRENT_INTEGER := TO_NUMBER(REGEXP_SUBSTR(IP_IN,'\w+',1,OCTET_COUNTER));
    CURRENT_INTEGER := POWER(2,24 - ((OCTET_COUNTER-1)*8)) * CURRENT_INTEGER;
    OUTPUT_INTEGER := OUTPUT_INTEGER + CURRENT_INTEGER;
  END LOOP;
  RETURN OUTPUT_INTEGER;
END GET_IP_INTEGER1;

This also works but seems to run much (about twice as long) slower. I would assume that either the power function or the regexp_substr is a pig. But I was hoping someone with more knowledge might point out which, and/or why.

score 0 · Answer 1 · answered Feb 19 '13 at 18:04

0

Here is my little piece of knowledge: in oracle 11g, you have the hierarchical PL/SQL profiler. This will show you where your pl/sql is spending its time.

answered Feb 19 '13 at 18:04

Plouf

627
3
7

score 0 · Answer 2 · answered Feb 19 '13 at 18:06

regexp_substr will be slower than a regular substr, and there will be a bit of overhead with the power. The most time would in in the regexp_substr though.

im curious why you say they are "slow" though. i assume you mean over a LOT of calls? as the routines are pretty snappy when i tested them.

Also, this type of function would benefit greatly from native compilation (which is easy to do in 11g, as the dba doesn't have to do anything to get this working).

finally you may find this to be marginally quicker (esp with native compilation).

create or replace function get_ip_integer3(ip_in in varchar2) 
return integer 
as
  result integer := 0;
begin
  result := result + 16777216 * substr(ip_in, 1, instr(ip_in, '.') - 1);
  result := result +
            65536 * substr(ip_in, instr(ip_in, '.') + 1,
                           instr(ip_in, '.', 1, 2) - instr(ip_in, '.') - 1);
  result := result +
            256 * substr(ip_in, instr(ip_in, '.', 1, 2) + 1,
                         instr(ip_in, '.', 1, 3) - instr(ip_in, '.', 1, 2) - 1);
  result := result + substr(ip_in, instr(ip_in, '.', 1, 3) + 1);

  return result;
end get_ip_integer3;

I posted another question (http://stackoverflow.com/questions/14964562/how-can-you-force-an-function-in-a-where-clause-to-execute-once-in-oracle) where I actually use this function, and the time to execute went from 4.33 seconds to 11 seconds. — Paul Stearns, Feb 19 '13 at 18:20

Why do these 2 oracle functions perform differently?

2 Answers2