How to optimize my stored procedure

Question

I would like to know if there are ways I could optimize n improve performance of this stored procedure. I'm fairly new at it & would appreciate your help

To summary , the stored procedure takes an array . Each array an entry of the form a:b:c . After extracting the required data, an entry is made in a table.

create or replace 
PROCEDURE insert_sku_prom_assigments (
   sku_assigment_array         tp_string_array_table
)
IS
first_colon_pos    NUMBER;
second_colon_pos   NUMBER;
sku                VARCHAR2 (40);
assign             VARCHAR2 (20);
promotion          VARCHAR2 (40);
l_count            INTEGER;
 BEGIN
FOR i IN sku_assigment_array.FIRST .. sku_assigment_array.LAST
 LOOP
  first_colon_pos := INSTR (sku_assigment_array (i), ':', 1, 1);
  second_colon_pos := INSTR (sku_assigment_array (i), ':', 1, 2);
  sku := SUBSTR (sku_assigment_array (i), 1, first_colon_pos - 1);
  assign :=
     SUBSTR (sku_assigment_array (i),
             first_colon_pos + 1,
             second_colon_pos - first_colon_pos - 1
            );
  promotion := SUBSTR (sku_assigment_array (i), second_colon_pos + 1);

  IF sku IS NOT NULL AND assign IS NOT NULL AND promotion IS NOT NULL
  THEN
     SELECT COUNT (*)
       INTO l_count
       FROM mtep_sku_promotion_rel_unver
      WHERE sku_id = sku
        AND assignment = assign
        AND promotion_id = promotion
        AND ROWNUM = 1;

     IF l_count < 1
     THEN
        INSERT INTO mtep_sku_promotion_rel_unver
                    (sku_id, assignment, promotion_id
                    )
             VALUES (sku, assign, promotion
                    );
     END IF;
  END IF;

  l_count := 0;
END LOOP;
END insert_sku_prom_assigments;

Answer 1

Replace the row-by-row PL/SQL with a single SQL statement:

create or replace procedure insert_sku_prom_assigments
(
    sku_assigment_array tp_string_array_table
) is
begin
    --#4: Insert new SKU, ASSIGN, and PROMOTION.
    insert into mtep_sku_promotion_rel_unver(sku_id, assignment, promotion_id)
    select sku, assign, promotion
    from
    (
        --#3: Get values.
        select sku_string,
            substr (sku_string, 1, first_colon_pos - 1) sku,
            substr (sku_string, first_colon_pos + 1, second_colon_pos-first_colon_pos - 1) assign,
            substr (sku_string, second_colon_pos + 1) promotion
        from
        (
            --#2: Get positions.
            select
                sku_string,
                instr(sku_string, ':', 1, 1) first_colon_pos,
                instr(sku_string, ':', 1, 2) second_colon_pos
            from
            (
                --#1: Convert collection to table.
                select column_value sku_string
                --Use this for debugging:
                --from table(tp_string_array_table('asdf:qwer:1234'))
                from table(sku_assigment_array)
            ) converted_collection
        ) positions
    ) sku_values
    --Only non-null values:
    where sku is not null and assign is not null and promotion is not null
    --Row does not already exist:
    and not exists
    (
        select 1/0
        from mtep_sku_promotion_rel_unver
        where sku_id = sku
            and assignment = assign
            and promotion_id = promotion
    );
end insert_sku_prom_assigments;
/

Here are sample objects for testing:

create or replace type tp_string_array_table is table of varchar2(4000);
create table mtep_sku_promotion_rel_unver(
    sku_id varchar2(100),
    assignment varchar2(100),
    promotion_id varchar2(100));

begin
    insert_sku_prom_assigments(tp_string_array_table('asdf:qwer:1234'));
end;
/

SQL almost always outperforms PL/SQL. It's also easier to test and debug once you get used to the way the code flows.

Answer 2

@jorge is right there probably isn't much performance improvement to be found. But you could consider these alternatives to selecting a count before inserting:

This:

INSERT INTO mtep_sku_promotion_rel_unver
                (sku_id, assignment, promotion_id
                )
SELECT sku, assign, promotion FROM DUAL
WHERE NOT EXISTS
( SELECT NULL
    FROM mtep_sku_promotion_rel_unver
   WHERE sku_id = sku
     AND assignment = assign
     AND promotion_id = promotion
);

Or, assuming those 3 columns are defined as a key:

BEGIN
  INSERT INTO mtep_sku_promotion_rel_unver
                (sku_id, assignment, promotion_id
                )
         VALUES (sku, assign, promotion
                );
EXCEPTION
  WHEN DUP_VAL_ON_INDEX THEN NULL;
END;

This is not performance-related, but I also like to use the Oracle function apex_util.string_to_table to break up delimited strings easily:

PROCEDURE ...
IS
  v_tab apex_application_global.vc_arr2;
  ...
BEGIN
  v_tab := apex_util.string_to_table (sku_assigment_array (i), ':');
  if v_tab.count >= 3 then
    sku := v_tab(1);
    assign := v_tab(2);
    promotion := v_tab(3);
  end if;