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

How to detect HTML5 support for a browser?

HTML5 has introduced lots of new cool  tags . Not all the browsers support all tags and also the implementation of these tags may be different for each browser. HTML5 specification defines the functional aspects of these tags and not the implementation. Also the general concensus is that by 2022 all browsers will support all new features of HTML5. Of all the modern browsers, Chrome seems to have implemented most, if not all, featutes of HTML5. IE9 supports few. Firefox sits in between. So as a developer how do you make use of the cool HTML5 features without causing any compatibility issues with existing browsers? Traditionally developers have used User Agent to detect browser type and use the features accordingly. However these days, you can easily change a User Agent by using addons in your browser. So you need a more robust way to detect the features supported by the browser as the same engine of two different versions of a browser might support different features. To detect t
Post a Comment
Read more

How to deploy Lambda function in AWS from developers machine?

  In previous post , we saw how to setup local development environment and test a Lambda function locally. As a developer, you may want to test the function in AWS environment quickly to make sure your code is working. In this post we will look at how to deploy the function in your AWS account. We will look at CI/CD pipeline to automatically build and deploy Lambda function in next post.  If you followed the instruction in previous post , you will notice there are three files in your solution LambdaEntrypoint.cs - This is the entrypoint for AWS Lambda.  aws-lambda-tools-defaults.json - This is the configuration file used by Lambda tooling. Update this file to add AWS profile you created while configuring AWS CLI and region you want to deploy your code. Note make sure you have this setting in your template as for some reason, it is missing from default template.  "function-runtime" : "netcoreapp3.1" , serverless.template - This is CloudFormation Serverless applicatio
Post a Comment
Read more

When to implement IDisposable?

In .Net, garbage collector is very good at cleaning up managed resources. Managed resources are object implemented using .Net framework and something that the Garbage collector is aware of. Unmanaged resource is anything that is implemented outside .Net framework and Garbage collector has no information about it. The combination of Destructor and Dispose can be used to release unmanaged resources for e.g open files or database connection. If you are not using any unmanaged resource, there is no need to implement IDisposable. Garbage collector will take care of release the unused objects and freeing up the memory.
1 comment
Read more