Delay in the Results of a TSQLStoredProc.

Delay in the Results of a TSQLStoredProc.

Postby ftaveras » Sat 26 Apr 2008 20:39

Hi.

I am experiencing a delay in time to obtain the results of a Stored Procedure (TSQLStoredProc) with respect to time to carry the same "SELECT" using a TSQLQuery. "

Using a Test program PL / SQL directly:

a. Stored Procedure
begin
declare
cv_1 sys_refcursor;
begin
sp_test_result(cv_1=>cv_1);
end;
--print :cv_1;
end;

b. Query
select 1
from dual;

Result : The result is presented in 0.15 seconds in both cases.

2. Test Using dbExpress (Rad Studio 2007).
a. Assign the procedure to a Component TSQLStoredProc: Open.
b. Assign the "SELECT" to a component TQuery: Open;
Result:The response time of StoredProcedure is 10-12 times bigger than the Query.
ftaveras
 
Posts: 2
Joined: Sat 26 Apr 2008 19:27

Postby ftaveras » Sat 03 May 2008 16:08

The procedure:

create procedure sp_test_result(
cv_1 in out sys_refcursor
)as
begin
open cv_1 for
select 1 query_result
from dual;
end;
/
ftaveras
 
Posts: 2
Joined: Sat 26 Apr 2008 19:27

Postby Plash » Mon 05 May 2008 07:31

When you use TSQLStoredProc component, additional queries are performed to describe parameters of the stored procedure. To avoid these additional queries, use TSQLQuery component with a PL/SQL block.
Plash
Devart Team
 
Posts: 2844
Joined: Wed 10 May 2006 07:09


Return to dbExpress driver for Oracle