| sql statments and proceduare is taking to much time [message #325271] |
Thu, 05 June 2008 08:40  |
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 #325313 is a reply to message #325299] |
Thu, 05 June 2008 12:26  |
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 |
 |
Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...
|
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 |
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 |
|
Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...
|
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 |
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 #325407 is a reply to message #325376] |
Fri, 06 June 2008 02:36 |
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 |
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
