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 Go to next message
OraFerro
Messages: 315
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 Go to previous messageGo to next message
EdStevens
Messages: 863
Registered: September 2013
Senior Member
OraFerro wrote on Wed, 25 October 2017 01:19
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

"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 #666295 is a reply to message #666278] Thu, 26 October 2017 02:55 Go to previous messageGo to next message
OraFerro
Messages: 315
Registered: July 2011
Senior Member
@EdStevens
Thanks for your reply. From my test results it seems that the current solution has no cons, as the tracing tool showed a single network trip and a single call to the SP. If what I observe is actually what happens then there is no need for further improvement. In fact this technique makes a SP that takes non-array parameter(s) accept array values from the calling app and handle it correctly.
Having doubt that what I see in tracing is exactly what happens (as I failed to find a clear answer on the internet) is what made me post.

So it will be great if can you confirm an answer for questions 2, 3?

Thanks,
Ferro

Re: Calling SP with array parameter from .net [message #666296 is a reply to message #666295] Thu, 26 October 2017 06:32 Go to previous messageGo to next message
EdStevens
Messages: 863
Registered: September 2013
Senior Member
So it seems the real question is "does my tracing method show what I think it does?"
Re: Calling SP with array parameter from .net [message #666303 is a reply to message #666296] Thu, 26 October 2017 07:19 Go to previous messageGo to next message
gazzag
Messages: 906
Registered: November 2010
Location: Bristol, UK
Senior Member
Is Ferro's desire for validation of his methodology a bad thing? Smile
Re: Calling SP with array parameter from .net [message #666306 is a reply to message #666303] Thu, 26 October 2017 14:21 Go to previous messageGo to next message
OraFerro
Messages: 315
Registered: July 2011
Senior Member
@EdStevend
Quote:

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)?
Exactly

Thanks,
Ferro

[Updated on: Thu, 26 October 2017 14:22]

Report message to a moderator

Re: Calling SP with array parameter from .net [message #666313 is a reply to message #666303] Fri, 27 October 2017 06:18 Go to previous messageGo to next message
EdStevens
Messages: 863
Registered: September 2013
Senior Member
gazzag wrote on Thu, 26 October 2017 07:19
Is Ferro's desire for validation of his methodology a bad thing? Smile
Not at all. I was just trying to clarify which question needs to be answered.

1) Will this change to my procedure result in better performance?

or

2) Is my testing method and results analysis valid, and if not, how do I get a valid test?

Upon opening the thread, it appeared the OP was asking (1), but the more important question - the one that will address not only the current issue but future issues as well - is (2).
Re: Calling SP with array parameter from .net [message #666321 is a reply to message #666313] Sun, 29 October 2017 00:34 Go to previous messageGo to next message
OraFerro
Messages: 315
Registered: July 2011
Senior Member
@EdStevens
@gazzaz

This is clearly taking much more of our precious time than it deserves Smile
The questions as I clarified in my original post and two times afterwards is:
Quote:

Is my testing method and results analysis valid, and if not, how do I get a valid test?
Thanks,
Ferro

[Updated on: Sun, 29 October 2017 00:35]

Report message to a moderator

Re: Calling SP with array parameter from .net [message #666329 is a reply to message #666321] Mon, 30 October 2017 06:30 Go to previous messageGo to next message
EdStevens
Messages: 863
Registered: September 2013
Senior Member
OraFerro wrote on Sun, 29 October 2017 00:34
@EdStevens
@gazzaz

This is clearly taking much more of our precious time than it deserves Smile
The questions as I clarified in my original post and two times afterwards is:
Quote:

Is my testing method and results analysis valid, and if not, how do I get a valid test?
Thanks,
Ferro
Then you need to detail your testing method . . . no one here can comment on what they cannot see.
Re: Calling SP with array parameter from .net [message #666347 is a reply to message #666329] Mon, 30 October 2017 10:37 Go to previous messageGo to next message
OraFerro
Messages: 315
Registered: July 2011
Senior Member
Dear Ed,

Logically, details of my testing are irrelevant as the aim of the question is about the network and SP calls. If you know the answer (regardless of my testing procedure) kindly share it.

Thanks for your time in all cases,
Ferro
Re: Calling SP with array parameter from .net [message #666354 is a reply to message #666347] Tue, 31 October 2017 06:27 Go to previous messageGo to next message
EdStevens
Messages: 863
Registered: September 2013
Senior Member
OraFerro wrote on Mon, 30 October 2017 10:37
Dear Ed,

Logically, details of my testing are irrelevant as the aim of the question is about the network and SP calls. If you know the answer (regardless of my testing procedure) kindly share it.

Thanks for your time in all cases,
Ferro

On Sunday you said "Is my testing method and results analysis valid, and if not, how do I get a valid test?"
Now you say "details of my testing are irrelevant".

How can we answer the question "Is my testing method and results analysis valid" without knowing details of your testing method?

Off the top of my head, I do not know how to measure the performance of the operation you described. I'm sure others here do. But I can spot the logical disconnect between your various statements.
Re: Calling SP with array parameter from .net [message #666365 is a reply to message #666354] Wed, 01 November 2017 00:55 Go to previous messageGo to next message
OraFerro
Messages: 315
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 Go to previous message
EdStevens
Messages: 863
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.
Previous Topic: Drop/Delete an instance
Goto Forum:
  


Current Time: Tue Dec 12 12:59:47 CST 2017

Total time taken to generate the page: 0.01659 seconds