Skip to main content

Use of Oracle Stored Procedures


Introduction:

This paper discusses the pros and cons of using Oracle stored procedure for encapsulating SQL. We will also look at the option of storing SQL within the application code. We will use following benchmarks to compare the two
1.       Ease of enterprise level development
2.       Separation of code
3.       Maintenance/support
4.       Testing
5.       Debugging
6.       Performance
7.       Security
8.       Developers skill set
9.       Transaction (Complex business Logic)

For sake of simplicity, the term ‘Stored Procedure’ or ‘Stored Function’ are interchangeable. ‘Package’ is a grouping of related elements. Application code can be a VB, C# or PHP code for a web or desktop application.

Benefits of using stored procedure vs embedding SQL in application code

·         Using stored procedure can speed up enterprise level application development as one developer can focus on developing the database logic while other can focus on application logic.
·         Stored procedure helps to separate the code out from the application code. This makes any futures changes easy as you have to recompile only the stored procedure provided there is no change in signature of the functions. For embedded SQL it will require recompiling the application code (data layer or entire application depending on the architecture). Also a stored procedure can be reused by many applications thus reducing the duplication of SQL.
·         Stored procedure make maintenance/support of application easy as you can debug application and database logic in isolation.
·         Stored procedure makes testing of application easier as you can test application and database layer in isolation. Also DBA’s can carry review the PL/SQL code and carry out performance check without worrying about the syntax of the language used for application e.g. C# or PHP. This will also save developers time as they don’t have to spend time with DBA’s in explaining them where the SQL are hidden in the code. DBA’s don’t need to understand the application structure or various file formats to review the SQL code.
·         As the table names and the logic of data access are encapsulated within stored procedure, it makes the application more secure in case where a web server is compromised. The database can be in lower DMZ.
·         Not all the developers need to know how to write complex or optimized SQL. Also each database server has different syntax for SQL. As long as we have at least one database developer or DBA who can code up database specific code, all other developers can focus on the application syntax to consume the stored procedure.
·         Complex data manipulation can happen on server side with a single database call. This reduces the amount of data transfer between client and server.
·         As stored procedures are tightly coupled with the database and are in precompiled form, this means data manipulation commands are processed much more efficiently than would be the case if an external program was used.
·         Oracle servers are more powerful than client machines; hence the SQL within the stored procedure will run more efficiently.
·         Grouping related stored procedures in a package helps to load all related elements in the memory at same time. This reduces the overhead of loading individual elements when they are referenced.

Pitfalls of using stored procedures

·         Stored procedure gives poor performance for simple sql’s. Stored procedures are good for complex processing.
·         The other pitfall of using Oracle PL/SQL is that involves a context switch from SQL which may add a considerable overhead. This is most likely to be a problem when embedding PL/SQL function calls in SQL statements. Also the code inside PL/SQL functions called from SQL statements will be ignored by EXPLAIN PLAN. This means that a judgment will have to be made on whether the advantages of using stored procedures will outweigh the disadvantages.

Other options

·         Build a data access layer with all the SQL code embedded in the layer. This  solves the issues of separate layer but has other drawbacks in terms of testing, code reviews, security
·         Use Entity Framework to link business objects to database objects. This makes development fast as there is no need to write any SQL code. However this makes performance testing and analyzing SQL code almost impossible as the SQL statements are dynamically generated. 

Conclusion

Based on the advantages a stored procedure provides, it makes a strong case to use stored procedures to handle complex processing logic. However for simple operation like CRUD, SQL’s can be embedded in the data access layer of the application.

References

http://www.smart-soft.co.uk/Oracle/oracle-performance-tuning-part6.htm

Comments

Post a Comment

Popular posts from this blog

Searching Unicode characters in Oracle table

Oracle implementation of Regular expression has no support for using hexadecimal code to search for Unicode characters. The only way to search for Unicode character is it use the character itself. Normally with Regular expression, you can use \x or \u followed by hexadecimal code to search for any character. E.g. \x20 will match space. But REGEXP_LIKE in Oracle does not support \x. You need to use unistr function to convert the code to equivalent character and then use it with REGEXP_LIKE. E.g. REGEXP_LIKE(source,'[' ||unistr('\0020')|| ']');
Post a Comment
Read more

C# Performance Improvement - The Power of StringBuilder

 Often when we are wring code we don't think about performance and go with the default options available to achieve a task. String concatenation is one such scenario. If you are doing simple and few string catenations, then you can use the following result = string1 + string2; string1+= string2; result = String.Concat(string1,string2); String.Format and string interpolation are few other options.  However when you are performing large and repetitive  operation, string catenation can be expensive. Here is an example to prove the point.  As you can see it took 41 seconds to perform 100k string catenation. Now lets replace this with StringBuilder and see.  8 ms!!!!!! That is a massive performance difference. Hope you get the point. More info on StringBuilder can be found here https://learn.microsoft.com/en-us/dotnet/api/system.text.stringbuilder?view=net-7.0
Post a Comment
Read more

How to get started with .Net Core and Lambda functions?

 Recently I started experimenting with .Net Core and AWS Lambda functions, I thought I will share the steps I took to get started Tools to install .Net Core 3.1 -  https://dotnet.microsoft.com/download/dotnet-core/3.1 AWS CLI -  https://aws.amazon.com/cli/   AWS Account - 12 months Free Tier -  https://aws.amazon.com/free Terminal - on iOS I use iTerm2 -  https://www.iterm2.com   Once you installed the above tools, head to AWS console and login with your root account to create an account which will be used by AWS CLI. Please see steps here   Now you can configure AWS CLI as detailed here . Verify that you able to connect to AWS service using CLI. To make life little easy with dotnet core and Lambda functions, you need to install AWS Extensions nuget pack for .Net CLI dotnet tool install -g Amazon.Lambda.Tools You can also install templates to get started quickly  dotnet new -i Amazon.Lambda.Templates That's it. Now the fun part!!!  Creat...
Post a Comment
Read more