Home » Developer & Programmer » Precompilers, OCI & OCCI » sql statments and proceduare is taking to much time
sql statments and proceduare is taking to much time [message #325271] Thu, 05 June 2008 08:40 Go to next message
elii@advantech.co.il
Messages: 7
Registered: June 2008
Location: israel
Junior Member
hello all .
let me draw you scenario:
when i run the next block:

declare
v_name varchar2(15);
begin
for i in 1..1000 loop
select cust_name
into v_name
from customers
where cust_id = :x;
end loop;
end;

with sql developer it takes 0.02 s

but where i run this block from an application ic c++ with occi it takes 2 entire second.

i think oracle preform multi parse every time i call the select from the application using occi.

does any one face this probleb off sql statment or procedure that is calling from the application and taking to much time using the occi.

please help me

Re: sql statments and proceduare is taking to much time [message #325278 is a reply to message #325271] Thu, 05 June 2008 09:08 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Put the code you use.
Don't forget to read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code.
Use the "Preview Message" button to verify.

Regards
Michel
Re: sql statments and proceduare is taking to much time [message #325284 is a reply to message #325278] Thu, 05 June 2008 09:33 Go to previous messageGo to next message
elii@advantech.co.il
Messages: 7
Registered: June 2008
Location: israel
Junior Member
which code?
the ooci one?
Re: sql statments and proceduare is taking to much time [message #325299 is a reply to message #325284] Thu, 05 June 2008 10:28 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
but where i run this block from an application ic c++ with occi it takes 2 entire second.

This one.

Regards
Michel
Re: sql statments and proceduare is taking to much time [message #325313 is a reply to message #325299] Thu, 05 June 2008 12:26 Go to previous messageGo to next message
elii@advantech.co.il
Messages: 7
Registered: June 2008
Location: israel
Junior Member
The block in sql developer:

declare
v_name varchar2(15);
begin
for i in 1..1000 loop
select cust_name
into v_name
from customers
where cust_id = 37384;
end loop;
end;

From C++ Application Using OCCI:


for( int i = 0; i<numLoop ; i++ )
{
Statement * stmt = NULL;
stmt = conn->createStatement();
stmt->setSQL(" select user_name from users where user_id
= 37384 ");
stmt->executeQuery();
conn->terminateStatement(stmt);

}

conn->commit();

Re: sql statments and proceduare is taking to much time [message #325317 is a reply to message #325313] Thu, 05 June 2008 13:22 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/ You didn't read the rules and format your post
2/ You didn't execute the PL/SQL block in your C program, you execute a program loop
3/ PL/SQL executes only on server and so does only 1 round trip
4/ Your program does a round trip for each loop
5/ PL/SQL parses only once the statement, your program parses it 1000 times
...

Regards
Michel


[Updated on: Thu, 05 June 2008 13:23]

Report message to a moderator

Re: sql statments and proceduare is taking to much time [message #325318 is a reply to message #325317] Thu, 05 June 2008 14:01 Go to previous messageGo to next message
elii@advantech.co.il
Messages: 7
Registered: June 2008
Location: israel
Junior Member
1. i did read the rules and format your post but when i submit
my replay everything changed.
2. i need to run the sql 1000 times, do you have any idea how?
4. what is round trip for every loop ? , what is the diffrent
between running 1000 select in loop in sql developer and
1000 select in c++ loop;
5. i know that my program parse in hard parse 1000 times
because i trace that session but why?
how can i rewrite my loop at the c++ application.


this example is basic , what i really need to do is calling from my c++ 1000 times a procedure in the DB with different variables.
Re: sql statments and proceduare is taking to much time [message #325319 is a reply to message #325318] Thu, 05 June 2008 14:19 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1. This is the purpose of Preview button and code tags and this is explained in forum guide

2. prepare statement, loop bind, execute, end loop

4. a round trip is an exchange of message on network
The difference between the 2 programs is in SQL Developer, you submit a PL/SQL block that fully executes in the server (the whole 1000 SQL executions) and then return. In your program, you create a statement, send it to server, get the result, and go back for another round and this 1000 times. In first case 2 messages, in second one 2000 messages.
To do the same thing, you have to send the same PL/SQL block to server.

5. Parse only one and execute it 1000. Parse (prepare) must be out of the loop. Go back to documentation.

Regards
Michel

[Updated on: Thu, 05 June 2008 14:20]

Report message to a moderator

Re: sql statments and proceduare is taking to much time [message #325320 is a reply to message #325318] Thu, 05 June 2008 14:22 Go to previous messageGo to next message
elii@advantech.co.il
Messages: 7
Registered: June 2008
Location: israel
Junior Member
i think thay i understand what you mean,
correct me if iwrong:
what you suggest is that i insert the loop inside the
stmt->setSQL,
sommething like:
stmt->setSQL(" begin 
               for( int i = 0; i<numLoop ; i++ )
                {
                  select user_name from users where user_id 
                    = 37384;
                 }
              end; ");

stmt->executeQuery();
conn->terminateStatement(stmt);

if this is what you mean thrn my problem is bigger because what i really want to do is calling a procedure in the DB with different values for every interation, smething like
begin
 for i in 1..1000 loop
   my_proc(:x,:y,:z);
  end loop;
end;

[Updated on: Thu, 05 June 2008 14:28] by Moderator

Report message to a moderator

Re: sql statments and proceduare is taking to much time [message #325323 is a reply to message #325320] Thu, 05 June 2008 14:31 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes this is what I meant to reproduce the same thing.
Now what you want is different but the solution depends if you want a result for each value or just execute the procedure for all values without any return (but of course exception).

Bind variables are made for executing the same thing with different values without reparsing the statement.

Regards
Michel
Re: sql statments and proceduare is taking to much time [message #325324 is a reply to message #325323] Thu, 05 June 2008 14:35 Go to previous messageGo to next message
elii@advantech.co.il
Messages: 7
Registered: June 2008
Location: israel
Junior Member
my procedure gets 4 in variable and 4 out variable.
i need to get replay for every call of the procedure ,
this is the reason why i call it in loop because ,after returning from procedure i am doing some other things.
Re: sql statments and proceduare is taking to much time [message #325376 is a reply to message #325324] Fri, 06 June 2008 00:57 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I don't know if you can use array for a procedure call, investigate on this.
Otherwise, you can loop as I said:
prepare
start loop
  bind
  execute
end loop
close

Regards
Michel
Re: sql statments and proceduare is taking to much time [message #325407 is a reply to message #325376] Fri, 06 June 2008 02:36 Go to previous messageGo to next message
elii@advantech.co.il
Messages: 7
Registered: June 2008
Location: israel
Junior Member
the best way is to send bulk array and you can do such thing.
but
 prepare
start loop
  bind
  execute
end loop
close



instead of the prepare which i dont understand , this is exactly what i do and its the same as calling the select 1000 times and parse the procedure 1000 times because inside the loop you execute the procedure and still running 1000 times.

but for now i dond want to use bulk because its a big change fi\or my application which drag a lot off QA testing ,so i dont want to do it now.

i look for other way to make the ORACLE DB running faster over 1000 times.

[Updated on: Fri, 06 June 2008 02:41]

Report message to a moderator

Re: sql statments and proceduare is taking to much time [message #325416 is a reply to message #325407] Fri, 06 June 2008 02:49 Go to previous message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can't do it without making change in your program.

Regards
Michel
Previous Topic: Install Oracle ProCobol pre-compiler
Next Topic: PRO*C compiler error
Goto Forum:
  


Current Time: Thu Mar 28 12:59:03 CDT 2024