Home » Infrastructure » Windows » Calling SP with array parameter from .net (Oracle 11.0.2.10, Window Server 2008 R2 Enterprise, Visual Studio 2010, IIS 6)
| Calling SP with array parameter from .net [message #666270] |
Wed, 25 October 2017 01:19  |
 |
OraFerro
Messages: 433
Registered: July 2011
|
Senior Member |
|
|
Dear All,
A few week ago I posted http://www.orafaq.com/forum/m/665861/#msg_665861 to PL/SQL forum. I want to add more .net related questions to the same example.
I traced the code together with the SP using ODTforVS2015_122010 and it showed me that the .net code runs once and then the SP runs as many times as the array size sent.
My questions are:
1- In this example, how many network trips are there between the application and DB? According to my tracing tool, there is only one network trip.
2- How many times is the SP called, according to the tracing tool it is called once and its code is repeated according to array size. Is this right or it is actually called 3 different times (as the sent array length is 3)? In other words, does ODP.net handle the communication between application and DB layers in such a way that there is only one network trip and one SP call while inside the SP the code is repeated according to ArrayBindCount?
3- The reply I got indicated that rewriting the SP to accept array parameter is a better approach, will that reduce network trips or SP calls or enhance performance?
Thanks,
Ferro
[Updated on: Wed, 25 October 2017 01:22] Report message to a moderator |
|
|
|
| Re: Calling SP with array parameter from .net [message #666278 is a reply to message #666270] |
Wed, 25 October 2017 06:40  |
 |
EdStevens
Messages: 1376
Registered: September 2013
|
Senior Member |
|
|
OraFerro wrote on Wed, 25 October 2017 01:19Dear All,
A few week ago I posted http://www.orafaq.com/forum/m/665861/#msg_665861 to PL/SQL forum. I want to add more .net related questions to the same example.
I traced the code together with the SP using ODTforVS2015_122010 and it showed me that the .net code runs once and then the SP runs as many times as the array size sent.
My questions are:
1- In this example, how many network trips are there between the application and DB? According to my tracing tool, there is only one network trip.
2- How many times is the SP called, according to the tracing tool it is called once and its code is repeated according to array size. Is this right or it is actually called 3 different times (as the sent array length is 3)? In other words, does ODP.net handle the communication between application and DB layers in such a way that there is only one network trip and one SP call while inside the SP the code is repeated according to ArrayBindCount?
3- The reply I got indicated that rewriting the SP to accept array parameter is a better approach, will that reduce network trips or SP calls or enhance performance?
Thanks,
Ferro
"will that reduce network trips or SP calls or enhance performance?"
What does it cost to test it for yourself? You've already shown that you can trace it and see what's going on. You'll learn far more from conducting your own test, then asking for a review of the results.
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Calling SP with array parameter from .net [message #666365 is a reply to message #666354] |
Wed, 01 November 2017 00:55 |
|
OraFerro
Messages: 433
Registered: July 2011
|
Senior Member |
|
|
Dear Ed,
I am sure you are trying to help, I honestly do, which is why I am interested to let this case take its logical path regardless of the duration as I believe this will help in future cases.
My logic is: the technical answer regarding the number of network visits and SP calls, is presumably known by you as an expert regardless of my testing. If the answer confirms my testing observation, then there is no need to discuss testing details or go for option 3. In case the answer negates my testing observation, then (also regardless of my testing details) I will work on option 3.
Thanks,
Ferro
[Updated on: Wed, 01 November 2017 00:56] Report message to a moderator |
|
|
|
| Re: Calling SP with array parameter from .net [message #666370 is a reply to message #666365] |
Wed, 01 November 2017 07:12 |
|
EdStevens
Messages: 1376
Registered: September 2013
|
Senior Member |
|
|
Like cookiemonster in your other thread, I'm not a .net guy, but like him, I observe that your procedure does not accept an array as input. Since I'm not a .net guy I'm not sure how this works. In fact, I'm surprised it works at all. I'd expect the PL/SQL procedure to fail with 'wrong number or type of arguments'.
As to your specific questions on THIS thread ..
1) I'm not sure how I'd measure the number of network trips, outside of the PL/SQL
2) If you want to know how many times the proc is called/executed, you could write a record to a file (utl_file) or insert into a logging table. All of our procs to the latter. At the very minimum they insert a row 'Beginning <procname>' and 'Ending <procname>', with a timestamp column.
create table program_log (log_time timstamp,
log_pgm_name varchar2(30),
log_action varchar2(500)
)
;
PROCEDURE JOBS_ADD_ROWS
(
I_JOB_ID IN varchar2,
I_JOB_TITLE IN varchar2,
I_MIN_SALARY IN NUMBER,
I_MAX_SALARY IN Number
)
AS
new_sal number;
v_pgm_name varchar2(30) := 'JOBS_ADD_ROWS';
BEGIN
insert into program_log values (systimestamp,
v_pgm_name,
'Entering procedure'
);
if I_MAX_SALARY = 16000 then
new_sal := I_MAX_SALARY + 500;
else
new_sal := I_MAX_SALARY + 1;
end if;
INSERT INTO JOBS VALUES
(
I_JOB_ID ,
I_JOB_TITLE ,
I_MIN_SALARY,
new_sal
);
insert into program_log values (systimestamp,
v_pgm_name,
'Exiting procedure'
);
END;
Actually, since we do this logging for all of our code, we have more elaborate infrastructure set up, with a common 'program_log' proc that is called from the individual procs, passing their name. The program_log proc runs as an autonomous transaction so that it can commit the writes to the log table irrespective of any commit or rollback done by the calling procedure. I would strongly encourage you to do something similar, as it allows for a great deal of instrumentation of your code.
3) Again, you are asking if some course of action will "reduce network trips or SP calls or enhance performance". And again, the only way to know is to test it.
|
|
|
|
Goto Forum:
Current Time: Sat Feb 01 07:54:56 CST 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
