Saturday, June 19, 2010

11g: SIMPLE_INTEGER is faster than PLS_INTEGER

Reading the improvements on 11g, I came across these new data types. They have NOT NULL constraints and should be faster.
  • SIMPLE_INTEGER - based on PLS_INTEGER
  • SIMPLE_FLOAT  - based on BINARY_FLOAT
  • SIMPLE_DOUBLE - based on BINARY_DOUBLE.
With a basic time test, I did find SIMPLE_INTEGER to be faster, and I don't know where I go wrong on the others, but they're are actually running slower for me.  Also, I'm suppose to see a difference if  plsql_code_type is native instead of interpreted.  At least with this test, it was incredible minor.  Anyway, here's some code you can copy, and test with.  If you know why they're not all faster, please let me know.
ALTER SESSION SET plsql_code_type = 'NATIVE'; -- or INTERPRETED

SET SERVEROUTPUT ON
DECLARE
    v_pls_integer    PLS_INTEGER    := 0;
    v_simple_integer SIMPLE_INTEGER := 0;
    v_start          NUMBER;
    v_end            NUMBER;
    v_pls_time       NUMBER;
    v_simple_time    NUMBER;
    v_loops          NUMBER         := 90000000;
BEGIN
    ----------------------------------------------------------
    -- SIMPLE_INTEGER test (New datatype)
    ----------------------------------------------------------
    v_start := DBMS_UTILITY.GET_TIME;
    
    FOR i in 1..v_loops LOOP
        v_simple_integer := v_simple_integer + 1;
    END LOOP;
    
    v_end         := DBMS_UTILITY.GET_TIME;
    v_simple_time := v_end - v_start;
    
    DBMS_OUTPUT.PUT_LINE ( 'The new Simple_Integer took '
                        || (v_end - v_start)
                        || q'[ 100th's of a second.]');
    ----------------------------------------------------------
    -- PLS_INTEGER TEST (Old datatype)
    ----------------------------------------------------------
    v_start := DBMS_UTILITY.GET_TIME;
    
    FOR i in 1..v_loops LOOP
        v_pls_integer := v_pls_integer + 1;
    END LOOP;
    
    v_end      := DBMS_UTILITY.GET_TIME;
    v_pls_time := v_end - v_start;
    
    DBMS_OUTPUT.PUT_LINE ( 'The old Pls_Integer took    '
                        || (v_end - v_start)
                        || q'[ 100th's of a second.]');
    ----------------------------------------------------------
    -- Calculate 
    ----------------------------------------------------------
    DBMS_OUTPUT.PUT_LINE ( 'With'
                        || To_char(v_loops,'99,999,999')
                        || ' iterations, there was a '
                        || Round(100 - v_Simple_Time/v_Pls_Time * 100,1)
                        ||'% improvement.');
END;
/
Tiny URL: http://tinyurl.com/simpleinteger

No comments:

Post a Comment