UPDATE View the edits if you care to see the long original question. This is the clearer short version of the question...
I need to see if GroupA
(not always GroupA
, this changes each loop iteration) exists in a [list,varray,temp table, whatever] of 200 or so groups. How I store those 200 groups is totally in my control. But I want to store them in a construct that lends itself to the FASTEST "existence" checking because I will have to check this list MANY times within a loop against different values (not always GroupA
). So whats fastest in PL/SQL, checking a list...
IF 'GroupA' IN ('GroupA','GroupB') THEN...
or checking a VARRAY using MEMBER OF...
IF 'GroupA' MEMBER OF myGroups THEN
or checking a VARRAY this way...
FOR i IN myGroups.FIRST .. myGroups.LAST
LOOP
IF myGroups(i) = 'GroupA' THEN
v_found := TRUE;
EXIT;
END IF;
END LOOP;
or checking associative arrays...
will test this tomorrow
UPDATE: FINAL RESULTS OF TESTING FROM EVERYONE'S SUGGESTIONS
Thanks all.
I ran these tests, looped 10 million times and the commas separated string using a LIKE
seemed to be the fastest so I guess the points have to go to @Brian McGinity (the times are in the comments below). But since the times were all so close it probably doesn't matter which method I go with. I think I'll go with the VARRAY MEMBER OF
method since I can load the array with a single line of code (bulk collect) instead of having to loop a cursor to build a string (thanks @Wernfried for bringing MEMBER OF
to my attention)...
comma separated list, example: ,GroupA,GroupB,GroupC,...around 200 groups... (list made by looping a cursor)
FOR i IN 1 .. 10000000 loop
if myGroups like '%,NONE,%' then
z:=z+1;
end if;
end loop;
--690msec
same commas separated list (list made by looping a cursor)...
FOR i IN 1 .. 10000000 loop
if instr(myGroups, ',NONE,') > 0 then
z:=z+1;
end if;
end loop;
--818msec
varray, same 200 groups (varray made by bulk collect)...
FOR i IN 1 .. 10000000 loop
IF 'NONE' MEMBER of myGroups THEN
z:=z+1;
end if;
end loop;
--780msec
associative array method suggested by @Yaroslav Shabalin (assoc. array made by looping a cursor)...
FOR i IN 1 .. 10000000 loop
if (a_values('NONE') = 1) then
z:=z+1;
end if;
end loop;
--851msec
Is myGroup a varray? If it is a string try something like:
select 1
from dual
where 'abc,NONE,def' like '%,NONE,%'
It is hard to follow the constraints you're working under... If at all possible, do everything inside of sql and it will be faster.
So if you're already in a plsql unit and wanted to stay in a plsql unit then the logic above would go something like this:
declare
gp varchar2(200) := 'abc,def,NONE,higlmn,op';
begin
if ','||gp||',' like '%,NONE,%' then
dbms_output.put_line('y');
else
dbms_output.put_line('n');
end if;
end;
if this itself is in a loop then, make the list once as:
declare
gp varchar2(200) := 'abc,def,NONE,higlmn,op';
gp2 varchar2(200) := ',' || gp || ',';
begin
if g2 like '%,NONE,%' then
dbms_output.put_line('y');
else
dbms_output.put_line('n');
end if;
end;
Also try instr which is probably faster than like:
declare
gp varchar2(200) := ',abc,def,NONE,hig,';
begin
if instr(gp, ',NONE,') > 0 then
dbms_output.put_line('y');
else
dbms_output.put_line('n');
end if;
end;
I have no idea if this faster than the other solutions mentioned (it stands a good chance), it is something else to try.
I did not get your full question but perhaps this function helps you: MEMBER Condition
WHERE 'groupA' MEMBER of myGroups
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With