Help speeding up a function 1

[daddypost]

I have a sql query that pulls about 26000 rows in about 3 min. I have added another column that calls a pretty simple function, but it changes the efficiency to about 100 rows/2 minutes. I really need to add three columns using the same function, but at this rate, it would run all day. The function is as follows
create or replace function customers_x(v_master_id number,
p_product_id varchar2,
n number)

return varchar2


is

v_no_of_records number := 0;
v_current_rec number := 0;

cursor x is
select m.product_id
from miccom1.ensembles_prod_rec e, acntv.master_sku m
where m.master_id = e.master_id2
and e.master_id1 = v_master_id
order by e.affinity_score desc,master_id2 asc;
begin

for rec_index in x loop
v_no_of_records := v_no_of_records + 1;
end loop;

if v_no_of_records < n then
return 'NULL';

else
for rec_index in x loop
v_current_rec := v_current_rec + 1;
if v_current_rec = n then
return rec_index.product_id;

end if;
end loop;
end if;
end;

basically, master_id is indexed in the table that is passing that variable, product_type is not used(will be in future function), and n is the number of the column I added. (ie would pass 1 to get first record, 2 to get second record for second additional column ...etc) The select statement itself pulls incredibly fast when providing a master_id. Each master_id could have 0-10 product_ids. Everything works fine, it is just a complete resource hog. Is there anything I can do to help speed this up.
Thank you in advance for your help.

 

[BJCooperIT]

Each time this function is called, it reads all the rows in cursor "x" for the master_id and sorts them. TWICE!

I cannot test this without your tables and data, but this should be closer to what you want:

CREATE OR REPLACE FUNCTION customers_x
(
    v_master_id  NUMBER,
    p_product_id VARCHAR2,
    n            NUMBER
)
 RETURN VARCHAR2 
IS
    v_product_id    acntv.master_sku.product_id%TYPE;
BEGIN
    SELECT  product_id
    INTO    v_product_id
    FROM    (   SELECT m.product_id
                FROM   miccom1.ensembles_prod_rec e,
                       acntv.master_sku           m
                WHERE  m.master_id = e.master_id2
                AND    e.master_id1 = v_master_id
                ORDER  BY e.affinity_score DESC, master_id2 ASC )
    WHERE   rownum = n;

    RETURN v_product_id;

    EXCEPTION WHEN no_data_found THEN RETURN 'NULL';
END;

^{Beware of false knowledge; it is more dangerous than ignorance. ~George Bernard Shaw}
Consultant Developer/Analyst Oracle, Forms, Reports & PL/SQL (Windows)
My website: www.EmuProductsPlus.com

 

[Dagon]

Incredibly fast" may mean that the query takes 1 second but 1 second multiplied by 100 would give you 1 min 40 s.

I would really avoid this sort of approach if you possibly can. It will involve opening, fetching and closing a cursor for each row of your main query. Even for an extremely simple query this will be slow, but your query involves a join of two tables and a sort. And, as BJCooper says, you're doing it twice !

I would ditch the function and try to move it all into SQL. I don't have your table definitions, so I've tried to mimic what you seem to be trying to do with some temporary tables and I've come up with this:

drop table ordered_row;

create table ordered_row (master_id number, data_item varchar2(20), ordering_col number);

insert into ordered_row values (1, 'X1', 1);
insert into ordered_row values (1, 'X2', 2);
insert into ordered_row values (1, 'X3', 3);
insert into ordered_row values (2, 'Y1', 1);

drop table source_data;

create table source_data (master_id number, some_data varchar2(20));

insert into source_data values (1, 'XXXXXXX');
insert into source_data values (2, 'YYYYYYYY');

with order_source as
(select master_id, data_item, rank() over (partition by master_id order by ordering_col) as rn from ordered_row)
select s.master_id, o1.data_item as first_data_item, o2.data_item as second_data_item,
o3.data_item as third_data_item
from source_data s, order_source o1, order_source o2, order_source o3
where o1.master_id (+) = s.master_id
and   o2.master_id (+) = s.master_id
and   o3.master_id (+) = s.master_id
and   o1.rn (+) = 1
and   o2.rn (+) = 2
and   o3.rn (+) = 3
/

 

[daddypost]

This does speed it up a little, thank you. I'm still thinking it shouldn't take this long. Still 100 rows/45 sec.

Using the function you provided above the whole sql query is:
select m.master_id,
customers_x(m.master_id,m.product_id,1),
customers_x(m.master_id,m.product_id,2),
customers_x(m.master_id,m.product_id,3)
from master_sku m


there is an unique index on m.master id. The master_sku table has 26K+ rows. I'm thinking it should only take maybe 10-25 minutes to run as the select without the function runs in a minute.

Again, thank you very much for your help. I have given you a star, as I can use that syntax not only in this case, but in the future as well.

 

[jimirvine]

Are you simply looking to return the nth row for each grouping and a null for those groups that don't have a value at the nth row?
PL/SQL is not the way to go here. This can be solved with pure SQL and the power that is Analytic functions.
Check out the documentation and here is a very quick example that might point you in the right direction.

select d.department_id
      , d.department_name
      , e.last_name
      , e.salary
      , count(e.employee_id) over (partition by e.department_id order by e.salary) orders
from departments d
join employees e
on e.department_id = d.department_id

 

[daddypost]

Dagon,
I'm not sure I follow completely. Would I need to manually create the "insert into ordered_row values (1, 'X1', 1);" the tables I am using will be truncated and repopulated daily based on a number of criteria. So everyday, the master_ids will change, as will the items I am joining with in the function.

 

[BJCooperIT]

Jim and Dagon are steering you in the right direction. I stuck with the function because you said product_type would be added and I thought there might be some additional functionality added to the function.

My code, as it stands, will not work because of the "= rownum" I had in there. It would need to be in a cursor and be "< n+1" or something of that nature.

CREATE TABLE my_master_sku (master_id number, product_id NUMBER);

INSERT INTO my_master_sku VALUES(456,80);
INSERT INTO my_master_sku VALUES(456,60);
INSERT INTO my_master_sku VALUES(456,40);
INSERT INTO my_master_sku VALUES(456,20);
INSERT INTO my_master_sku VALUES(123,90);
INSERT INTO my_master_sku VALUES(123,70);
INSERT INTO my_master_sku VALUES(123,50);
INSERT INTO my_master_sku VALUES(123,30);
INSERT INTO my_master_sku VALUES(123,10);
COMMIT;

SELECT * FROM my_master_sku;

 MASTER_ID PRODUCT_ID
---------- ----------
       456         80
       456         60
       456         40
       456         20
       123         90
       123         70
       123         50
       123         30
       123         10


SELECT   ROWNUM, sq.*
FROM     
            (   SELECT m.*, ROWNUM AS sq_rownum
                FROM   my_master_sku  m         
                WHERE  m.master_id = 456
                ORDER  BY m.product_id ASC ) sq
WHERE   ROWNUM = 2;

    ROWNUM  MASTER_ID PRODUCT_ID  SQ_ROWNUM
---------- ---------- ---------- ----------


SELECT   ROWNUM, sq.*
FROM     
            (   SELECT m.*, ROWNUM AS sq_rownum
                FROM   my_master_sku  m         
                WHERE  m.master_id = 456
                ORDER  BY m.product_id ASC ) sq
WHERE   ROWNUM < 3;

    ROWNUM  MASTER_ID PRODUCT_ID  SQ_ROWNUM
---------- ---------- ---------- ----------
         1        456         20          4
         [COLOR=red]2        456         40          3[/color]

^{Beware of false knowledge; it is more dangerous than ignorance. ~George Bernard Shaw}
Consultant Developer/Analyst Oracle, Forms, Reports & PL/SQL (Windows)
My website: www.EmuProductsPlus.com

 

[daddypost]

Jimirvine,
I hope something like that can work. Let me provide some examples and see if I can make sense of it.
MASTER SKU TABLE
master_id
ABC123
FGH567
XYZ890

Ensembles_prod_rec TABLE
master_id1 master_id2 score
ABC123 acbde1 .33
ABC123 hjfns2 .69
ABC123 lnfmw3 .19
ABC123 qwewe4 .99
FGH567 mmmmm5 .88



The result set I need is
ABC123 | qwewe4 | hjfns2 | abcde1
FGH567 | mmmmm5 | null | null
XYZ890 | null | null | null


I hope this helps explain the table setups and result set expected. Thank you all very much for your time and great advice.

 

[jimirvine]

Here is an Example using the employees table from the HR schema:

SELECT department_id
      ,MAX(DECODE(ORDERS,1, SALARY)) L1
      ,MAX(DECODE(ORDERS,2, SALARY)) L2
      ,MAX(DECODE(ORDERS,3, SALARY)) L3
FROM(
select department_id
     , salary
     , count(*) over (partition by department_id order by salary) orders
from employees
)
GROUP BY DEPARTMENT_ID