Have Geek Brain

Are Oracle Stored Procedures faster than SQL String for Microsoft .NET Application?

We are developing a Visual Studio .NET 2008 application with Oracle 9i as the backend. Is it true that using stored procedures will be faster than Inline SQL? This is a discussion with my co-programmers.

We are using ODP.NET (Oracle Data Provider for .NET) from Oracle.

Thanks.

+1


a source to share


5 answers


While I’m tempted to say “no” or “I don’t think so,” the only real way to find out the answer to this question is to go and measure it yourself . Use a profiler like JetBrains dotTrace and your own TOAD profiler.



All other answers are speculative.

+7


a source


It should be. When you send embedded SQL to the database, the engine must parse it and execute. Stored procedures are processed (and compiled) at creation time. At least you get the parsing time.



+4


a source


I would expect stored procedures to be faster in almost all cases.
But it mostly depends on your code.

If, for example, you get a result set from your database and then use that data to perform other queries, you will have a lot of network traffic overhead. This is especially true if you forgot to use one connection for queries.

It would then be more efficient to use a single stored procedure to return aggregated data (if possible, of course).

+3


a source


It will be faster if you create stored procs (PL / SQL packages) in such a way that less data is transferred between the database and the client.

+2


a source


Jon Limjap said, "All other answers are speculative."

There is a lot of truth in this statement. There are so many factors: How is the DB server configured? How is the speed / reliability of the network? How is SQL? How is PL / SQL? I could write very slow SQL and / or PL / SQL if I wanted to (and unintentionally, from past projects!). So if you can, try both.

0


a source






More articles:


All Articles