Replacing bulk collect with loop [message #675028] |
Wed, 06 March 2019 02:28 |
|
OraFerro
Messages: 433 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 |
cookiemonster
Messages: 13925 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 #675034 is a reply to message #675031] |
Wed, 06 March 2019 04:28 |
cookiemonster
Messages: 13925 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 #675036 is a reply to message #675035] |
Wed, 06 March 2019 04:46 |
cookiemonster
Messages: 13925 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 #675038 is a reply to message #675037] |
Wed, 06 March 2019 06:11 |
cookiemonster
Messages: 13925 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
OraFerro wrote on Wed, 06 March 2019 11:17Dear 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.
|
|
|
|
|
|