stored procedure call connect times

one registerPhone flow goes through 30 stored procedures. Each stored proc connecting database individually and taking 100 milli seconds to connect.

so 30 multipled by 100 total 3000 milli seconds wasted due to this.

can i make all 30 stored procedures into one call so that only one database connection happens for 100 milli seconds

Like to save those 3000 milli seconds when i can do only with 100 milli seconds

Please advise
LVL 7
gudii9Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ste5anSenior DeveloperCommented:
Well, you certainly can agree that a solution to that question solely depends on that "registerPhone" flow..

The obviously first question is: Why not writting a single wrapper procedure to call all those procedures?

btw, check the given flow and those procedures again. Cause your describtion says that those procedures are independent of each other ("Each stored proc connecting database individually [..]"), which sounds a little bit weird.
gudii9Author Commented:
any sample examples with code around this
please advise
slightwv (䄆 Netminder) Commented:
A stored procedure runs inside the database so it doens't need to connect.

Now if your code connects and runs one procedure and disconnects 30 times, then yes, you can save time.

A "wrapper" procedure is just one procedure that calls the rest.  I would also look into packages which is a grouping of procedures and functions.

An example wrapper is nothing more than:

create or replace procedure master_proc
is
   param1 varchar2(10) := 'Hello';
   p1 number :=1;
   p2 number :=2;
   p3 number;
begin
   procedure1(param1);
   procedure2;
   procedure3(p1,p2,p3);
   procedure4(p3);
end;
/

Open in new window


p3 is an output parameter for procedure3 that is used as an input for procedure4.
Geert GOracle dbaCommented:
depends on how you call it too, and what the system does.

from experience ...
we used a TStoredProc component in our development language
  and it implicitely did a check for the stored procedure name and parse on every call
> 10 calls per sec was the limit, somewhat like what you have

when using a TQuery component this didn't happen.
> 200 calls per sec

pass as musch as you can in 1 call
limit going between frontend and backend as much as possible

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Programming

From novice to tech pro — start learning today.