Hi,
We are developing a Visual Studio.NET 2008 application with Oracle 9i as backend. Is it true that the usage of Stored procedures would be faster than Inline SQL ? This is a debate with my co-programmers.
We are using ODP.NET (Oracle Data Provider for .NET) from Oracle.
Thanks.
It will be faster if you build your stored procs (PL/SQL packages) in such a way that less data will be transferred between the database and the client.
While I am tempted to answer "no" or "I don't think so", the only real way to know the answer to this question is to go and measure it yourself. Use a profiler like JetBrains dotTrace, and TOAD's own profiler, to find out.
All other answers are speculative.
It should be. When you send inline SQL to database, the engine must parse it and execute. Stored procedures are being parsed (and compiled) at creation time. So at least you are gaining parsing time.
I would expect stored procedures to be faster in almost all cases.
But it mainly depends on your code.
If for example you get a resultset from your database and then use this data to perform other queries, you'll end up with a lot of overhead in your network traffic. This is especially true if you forget to use a single connection for the requests.
It could then be more efficient to use a single stored procedure to return the agregated data (if possible of course).
Jon Limjap said: "All other answers are speculative."
There's much truth to this statement. There are so many factors: how's the DB server set up? How's the network speed/reliability? How's the SQL? How's the PL/SQL? I could write really slow SQL and/or PL/SQL if I wanted to (and have, inadvertently, on past projects!). So if you can, try both out.