Re: .NET stored procedures

On Sat, 13 Jan 2007 16:40:16 +0000, Ana C. Dent wrote:

> "Victor Rosenberg" <Victor.Rosenberg_at_gmail.com> wrote in
> news:1168690464.083355.9700_at_38g2000cwa.googlegroups.com:
>

>> Hey guys
>> Just a couple of questions
>> 1) How bad are the .net stored procedures in terms of performance,
>> compared to pl sql ones

From ORacle ...

"
A .NET stored procedure, on the other hand, runs in an external process and the .NET code is compiled into a ".NET assembly," which is a dynamic link library (DLL) file stored in the file system (usually on the same machine as the database).
"

IOW, it's an extproc.

Victor:

Extprocs need to make some form of database call to get the data from the database. This implies an OCI-based context switch.

I suspect the answer to your question is pretty simple: if the .Net proc is primarily procedural, it'll pace or even outshine PL/SQL, but if it's used mainly to do SQL flow control (letting the database do what is does best and just deciding what the next SQL is), PL/SQL wil be better.

Unfortunately, the majority of .Net, C#, Java (and even many PL/SQL) developers seem to prefer doing the data manipulation in the app code instead of the database. As a result, the majority of apps I've seen tend to run worse than they should.

If it's performance you want, make sure you understand the tools, and especially the Oracle database mechanics, before coding. If you haven't done so, please Read Tom Kyte's books.

-- 
Hans Forbrich   (mailto: Fuzzy.GreyBeard_at_gmail.com)   
*** Feel free to correct me when I'm wrong!
*** Top posting [replies] guarantees I won't respond.