Re: Embedded SQL vs Stored Procedures

From: Gama Franco <gama_franco_at_clix.pt>
Date: Wed, 08 Sep 2004 09:53:16 +0100
Message-ID: <chmgu2$4j9$1@pegasus.fccn.pt>

Tibor Jager wrote:
> Gama Franco wrote:
>

>> I'm using OCCI to develop a new aplication, but I have one doubt about 
>> the most efficient way to make an operation.
>> The operation makes inserts some rows in different tables, and also 
>> creates some new tables. I would like to know if it is faster to 
>> compute this kind of operations if they are hard codded in OCCI, or 
>> using a stored procedure that recieves all the parameters needed and 
>> takes care of the insertions and creations.

>
>
> I'd prefer stored procs for two reasons:
>
> 1.) I like to keep database logic inside the database, application logic
> inside the application. Depending on your design, you might also be more
> flexible regarding the DBMS used.
>
> 2.) Stored procedures are often the more efficient way, because a
> SQL-string passed by an application needs to be compiled new every time,
> while a stored procedure is compiled only once. At least concerning
> PL/SQL procedures. I've never used CREATE TABLEs in a stored procedure...
>
> Best Regards
>
> Tibor
>

Hi,

Ok, thank you.I also see a lot of advantages in using PL/SQL. If in the future the users decide to drop OCCI, or even make an application that uses JDBC or ODBC would spare me a lot of time, if some part of the code were already done. My only doubt was about efficiency, but what you said makes a lot of sense (SQL validation and the optimizer will be used only when the procedure it is created).

Best regards,

   Gama Franco Received on Wed Sep 08 2004 - 03:53:16 CDT