Home » SQL & PL/SQL » SQL & PL/SQL » Replacing bulk collect with loop (11.2.0.1.0 )
Replacing bulk collect with loop [message #675028] Wed, 06 March 2019 02:28 Go to next message
OraFerro
Messages: 369
Registered: July 2011
Senior Member
Hi All,

I have the following example of calling a function that accepts a date and an ID and returns a table of objects.
This function is called by another function that fills a variable array with values, this var array is then used to call the first function.

I noticed that this technique is slow, and I was wondering if other techniques can be faster.

My example:
CREATE OR REPLACE TYPE OBJ_A_STATEMENT AS OBJECT
(
  a_serial number(5),
  VALUE_DATE DATE
);

CREATE TYPE TBL_A_STATEMENT AS
    TABLE OF OBJ_A_STATEMENT;

CREATE or replace FUNCTION F_test_DATES
  (
    D_DATE IN DATE DEFAULT SYSDATE, 
    I_SERIAL IN NUMBER
  ) 
  RETURN TBL_A_STATEMENT AS
  A_STATEMENT TBL_A_STATEMENT;
 
  BEGIN

      SELECT OBJ_A_STATEMENT( I_SERIAL,
                            VALUE_DATE
                           )
        BULK COLLECT INTO A_STATEMENT
        FROM
        (  
          SELECT  I_SERIAL, sysdate VALUE_DATE from dual       
        );
    RETURN  (A_STATEMENT);
   END;


CREATE or replace TYPE ARY_A AS
    VARYING ARRAY(2000) OF NUMBER(5, 0);

 create or replace FUNCTION F_test_DATES_ALL ( D_ACQUISITION_DATE IN DATE) 
    RETURN TBL_A_STATEMENT AS
    A_STATEMENT TBL_A_STATEMENT;

    vARY_A ARY_A:=  ARY_A();
   BEGIN

      select rownum n BULK COLLECT INTO vARY_A from dual connect by level <= 1000;  -- <-- just a test statement to generate serial numbers
  

/* HERE is where I feel the slowness and I wonder if a loop or other techniques can be used -->*/
      SELECT T2.OBJECT_VALUE  
                 BULK COLLECT INTO A_STATEMENT  
            FROM TABLE(vARY_A) T1,                                   
                 TABLE(F_test_DATES(sysdate,T1.COLUMN_VALUE)) T2; 
 
/* HERE is where I feel the slowness and I wonder if a loop or other techniques can be used <--*/
   
      RETURN A_STATEMENT;
  
   END;



Thanks,
Ferro
Re: Replacing bulk collect with loop [message #675029 is a reply to message #675028] Wed, 06 March 2019 03:30 Go to previous messageGo to next message
cookiemonster
Messages: 13864
Registered: September 2008
Location: Rainy Manchester
Senior Member
I would never write a select that joins multiple arrays.
I would write a select that populates a single array directly from a query against real tables.

Why have you got two arrays at the point you've highlighted in the first place?

Also - I'd never populate an array from a table to then read that array as a table.

Just scrap all that code and replace it with a single select that gets what you need.
If you think you can't do that then either:
a) your example code isn't anything like your real code.
b) you've got some artificial constraints that you haven't explained.
Re: Replacing bulk collect with loop [message #675031 is a reply to message #675029] Wed, 06 March 2019 03:40 Go to previous messageGo to next message
OraFerro
Messages: 369
Registered: July 2011
Senior Member
Dear Cookiemonster,

Maybe its my limited knowledge, which is why I am checking for expert opinion.

To me, if I am going to call a function (i.e. F_test_DATES) multiple times according to a list of values then I have to have an array of values to pass them to this function.

Quote:
ust scrap all that code and replace it with a single select that gets what you need.
Do you mean by that re-writing F_test_DATES so that it accepts an array of records?
The reason I am keeping it this way is that both single and multiple parameters are needed and I prefer keeping one function in order not to duplicate the code (F_test_DATES_single_param, and F_test_DATES_array).

Please tell me if I got you correctly or I got it all wrong!

Thanks,
Ferro
Re: Replacing bulk collect with loop [message #675032 is a reply to message #675031] Wed, 06 March 2019 04:16 Go to previous messageGo to next message
cookiemonster
Messages: 13864
Registered: September 2008
Location: Rainy Manchester
Senior Member
I think you need to explain requirements at this point.
Re: Replacing bulk collect with loop [message #675033 is a reply to message #675032] Wed, 06 March 2019 04:23 Go to previous messageGo to next message
OraFerro
Messages: 369
Registered: July 2011
Senior Member
@Cookiemonster

Thanks again.

What I mean is that I understood from your first reply that you suggest re-writing the function to accept an array parameter

Which is why I explained that my requirements:
1- I need to run a function (i.e. F_test_DATES) with a single value for its I_SERIAL parameter, and with an array of values.
2- Instead of having two copies from this function (F_test_DATES_single_param, and F_test_DATES_array), I followed the way shown in the example to have both modes without repeating the function code.

Am I getting your suggestion correctly?

Thanks,
Re: Replacing bulk collect with loop [message #675034 is a reply to message #675031] Wed, 06 March 2019 04:28 Go to previous messageGo to next message
cookiemonster
Messages: 13864
Registered: September 2008
Location: Rainy Manchester
Senior Member
See there's always a trade off between encapsulation and performance.

And if you try to get everything in PL/SQL functions you'll hit that trade off hard.

Thing is - SQL is designed to work across large volumes of data very quickly by interfacing directly to where it's stored (the tables) and working there.

No other language can do that, not even PL/SQL.

If you use PL/SQL to the level you are you will tank performance.

OraFerro wrote on Wed, 06 March 2019 09:40


To me, if I am going to call a function (i.e. F_test_DATES) multiple times according to a list of values then I have to have an array of values to pass them to this function.
But why are you going to call a function multiple times?
Why don't you just write some SQL that gets what you need efficiently?



OraFerro wrote on Wed, 06 March 2019 09:40

Do you mean by that re-writing F_test_DATES so that it accepts an array of records?
No - I doubt any arrays are needed, but I'm not clear on what you are actually trying to achieve here so I could be wrong.

OraFerro wrote on Wed, 06 March 2019 09:40

The reason I am keeping it this way is that both single and multiple parameters are needed and I prefer keeping one function in order not to duplicate the code (F_test_DATES_single_param, and F_test_DATES_array).
This is the point where I need you explain the functional requirement.
Re: Replacing bulk collect with loop [message #675035 is a reply to message #675034] Wed, 06 March 2019 04:39 Go to previous messageGo to next message
OraFerro
Messages: 369
Registered: July 2011
Senior Member
@Cookiemonster,

Thanks for your patience.

Quote:

This is the point where I need you explain the functional requirement.
F_test_DATES is a function that should check each I_serial (the PK of a database table) and runs several checks and calculate appropriate transaction dates for that ID. Those checks are relatively complex and are prone to change by the business.
This is why I built a function that accepts a single I_serial parameter and then used it with all I_serial values that are eligible for this process.

I_serial (is a loan ID) and the array includes all Loans that are eligible for the current accounting period.

I hope this can help.
Thanks,
Re: Replacing bulk collect with loop [message #675036 is a reply to message #675035] Wed, 06 March 2019 04:46 Go to previous messageGo to next message
cookiemonster
Messages: 13864
Registered: September 2008
Location: Rainy Manchester
Senior Member
So you've massively oversimplified what you're actually doing in your example.
Don't do that, it's a waste of everyone's time, yours included.

Start again.
Post the real code (obfuscate table/column names if necessary).

Explain the requirements.

We can't make useful suggestions on how to improve the code without having any idea what that code is and what it is trying to do.
Re: Replacing bulk collect with loop [message #675037 is a reply to message #675036] Wed, 06 March 2019 05:17 Go to previous messageGo to next message
OraFerro
Messages: 369
Registered: July 2011
Senior Member
Dear Cookiermonster,

Quote:
So you've massively oversimplified what you're actually doing in your example.
I though this is what an example is meant for.

I really appreciate your help but I dont know why cant I seek your opinion based on a certain set of assumptions. Functions can include complex calculations. There is no need to simulate them in an example, lets assume F_test_DATES has complex calculations that are prone to change and it needs to be called with a single parameter and an array of values.
In this case:
1- would you write two functions?
2- If you keep it as one function, would you use another way to call the single-parameter function from another function and pass the list of values?


Thanks,
Re: Replacing bulk collect with loop [message #675038 is a reply to message #675037] Wed, 06 March 2019 06:11 Go to previous messageGo to next message
cookiemonster
Messages: 13864
Registered: September 2008
Location: Rainy Manchester
Senior Member
OraFerro wrote on Wed, 06 March 2019 11:17
Dear Cookiermonster,

Quote:
So you've massively oversimplified what you're actually doing in your example.
I though this is what an example is meant for.
No. Not at all.
An example is meant to make the problem clear in a simple manner - that means stripping out things that have nothing to do with the problem.
Simplest example is posting create table statements that only include the columns that are relevant to the problem, instead of 100+.

You've posted a complicated structure that doesn't contain any code to justify that structure. I suggest ditched the structure, you say you can't because of the code you've not shown.

OraFerro wrote on Wed, 06 March 2019 11:17

I really appreciate your help but I dont know why cant I seek your opinion based on a certain set of assumptions.
What assumptions? The part of the reason I'm asking for code/requirments is so I can try and work out what assumptions you are operating under.

OraFerro wrote on Wed, 06 March 2019 11:17

Functions can include complex calculations. There is no need to simulate them in an example,
You don't necessarily need to include them in the example, but you at the very least need to be clear that they exist.

You've got code that's running slow because you are calling a complex function a lot.
The general ways you speed that up are:
a) improve the performance of the function itself.
b) call the function less - check you're not calling it more times that necessary to get the job done.
c) move code/logic out of the PL/SQL function into SQL.

That's pretty much it in high level terms.
Your over-simplified example makes it impossible for us to tell if any of those are relevant/possible.

OraFerro wrote on Wed, 06 March 2019 11:17

lets assume F_test_DATES has complex calculations that are prone to change and it needs to be called with a single parameter and an array of values.
In this case:
1- would you write two functions?
I'd look to see if I could put the logic in a view in first instance probably.
But failing that - yes two functions. The real question is, is the one that takes the array just a wrapper than calls the other one or does it process the data differently - and if I can get a massive speed increase by having the array one process the data in a different way to the single id one then I'd probably do that (especially if users are complaining about performance and I've got no other way to speed it up).

OraFerro wrote on Wed, 06 March 2019 11:17

2- If you keep it as one function, would you use another way to call the single-parameter function from another function and pass the list of values?
not sure what you mean.
Re: Replacing bulk collect with loop [message #675130 is a reply to message #675038] Sun, 10 March 2019 23:58 Go to previous messageGo to next message
OraFerro
Messages: 369
Registered: July 2011
Senior Member
@Cookiemonster

Thanks a lot for the time you've taken to clarify the above points.

I was under the assumption that the way I call the single value function is the reason of delay which can be fixed if another technique is used. I stand corrected regarding the importance of showing the function logic (at least) to justify its complexity as fixing the function's performance should be a priority. I will try to build an example or share the pseudo code of the single value function.

Quote:
The real question is, is the one that takes the array just a wrapper than calls the other one or does it process the data differently
Yes, the array function is just a wrapper no further calculations. Its actually a report that either run for a single value or a list of values. but steps to get the single value report are complicated and involve several function calls as well. So the effort of revamping the whole thing into an SQL will be huge and will also affect code readability and change (as I will have a lengthy SQL statement instead of several reusable and modular functions).

Thanks a lot,
Ferro
Re: Replacing bulk collect with loop [message #675132 is a reply to message #675028] Mon, 11 March 2019 08:14 Go to previous messageGo to next message
BlackSwan
Messages: 26669
Registered: January 2009
Location: SoCal
Senior Member
What problem are you REALLY trying to solve?

http://xyproblem.info/
Re: Replacing bulk collect with loop [message #675133 is a reply to message #675031] Mon, 11 March 2019 08:21 Go to previous message
BlackSwan
Messages: 26669
Registered: January 2009
Location: SoCal
Senior Member
What problem are you REALLY trying to solve?

http://xyproblem.info/
Previous Topic: Need suggestion on code refactoring
Next Topic: how can I allow this procedure to attach to email(pdf file in the directory), pdf file that is > 32
Goto Forum:
  


Current Time: Mon Mar 30 18:34:58 CDT 2020