There has been a long-standing debate about the stored procedure’s weaknesses. Now let’s again look at its disadvantages.

Non-migratable

Being migratable refers to the stored procedure’s ability to move from its native database to another database of different type. Lack of this ability makes stored procedure syntax narrowly applicable and costly un-migratable.

Hard to edit and debug

A stored procedure is implemented within the database without an invalid IDE environment. This makes the hard to edit and debug problem a long-lasting one.

Closed system

The stored procedure that runs within the database can only compute data in the database. It cannot handle data in the other data sources directly. The external data should first be ETLed into the database for computation. That is time-consuming, low real-time and space-resource-consuming.

Tight coupling

Usually, stored procedures serve front-end applications, but they are stored in the database and physically separated from the latter. The maintenance should take care of both, which results in tight coupling between the application and the database. In the meantime, the database’s linear structure makes it possible that a stored procedure serves multiple applications, which leads to high coupling between applications.

Difficult to administer

Databases have flat structures instead of the tree structures file systems have. There will be no big problems when there are only a few stored procedures in the database, but when their number gets increased, the situation will get into a terrible mess.

Highly insecure

It is the programmers who are in charge of creation and modification of a stored procedure used by the current application. This requires granting a high database privilege to them, which poses potential security threats and causes other problems, such as mistaken deletions.

But stored procedures also have their noticeable merits. SQL provides great support for set-oriented operations. Stored procedures inherit this and add process control capabilities, offering programmers conveniences of SQL computing techniques as well as the abilities to handle complicated scenarios. Besides, stored procedures run within the database and data can be directly computed in the database (database JDBC is usually inefficient), reducing IO cost as much as possible.

If only there was a technology that has advantages of stored procedures and, at the same time, gets rid of their disadvantages.

esProc SPL is the one we’d like to have. It can replace the stored procedure to achieve “outside database stored procedure”.

As a special open-source data computing engine, esProc SPL has computational capabilities independent of databases. There is no need to modify an SPL script when databases switch between each other, getting rid of stored procedures’ migratability issues. SPL’s simple and easy-to-use IDE environment offers all-around edit and debug features to make implementation of algorithms more convenient. esProc SPL boasts an open system that enables the direct computation of data in diverse sources. The “outside database stored procedure” runs separately from the database and is stored together with the related application, which eliminates coupling issue. The computing engine adopts the file system’s tree structure to make data management easy and convenient. esProc SPL runs independent of databases, avoiding security problems.

 

SPL implements stored procedures outside of databases independently, getting rid of all problems brought about by databases.

Outside database computations and agile syntax

SPL possesses computing abilities independent of databases. Let’s look at a sample program:

【Computing task】To find the orders information of the first n big customers whose sales amount takes up at least half of the total:

	A
1	=file(“/opt/ods/orders.csv”).import@tc()
2	=A1.groups(customer;sum(amount):amount).sort(amount:-1)
3	=A2.sum(amount)/2
4	=0
5	=A2.pselect((A4=A4+amount,A4>=A3))
6	=A2.(customer).to(,A5)
7	=A1.select(A6.pos(A1.customer))

SPL performs the task step by step. It finds the eligible big customers and gets their detailed orders information. These computations are performed outside of the database, and SPL can even compute data in a file source directly.

According to the above computing process, SPL’s procedure computation is more convenient than the stored procedure, and has more concise syntax.

SPL provides a wealth of class libraries to be able to handle various complicated computing tasks conveniently.

 

Easy to develop & debug IDE environment

SPL has simple and easy-to-use development environment that offers a series of features, such step and set breakpoint, and WYSIWYG viewing panel, making it convenient for developers to write and debug code.

 

The result of each step can be view in real-time, which is more convenient and efficient than the hard to debug SQL and stored procedures.

Diverse data source mixed computing

Compared with the closed databases, SPL is open. It can handle mixed computations between diverse data sources. At present, SPL supports dozens of data sources, including RDB, NoSQL, CSV, Excel, HDFS, Restful/Webservice and Kafka…

SPL can quickly connect to any of the above data sources to perform data retrieval. In addition, it supplies rich class libraries to implement mixed computations between sources of different types in order to achieve complex computations like multi-source joins.

 

Loose coupling, manageable and secure

The stand-alone SPL decouples application from the database. The SPL script is stored in a file system along with the application (module) and applications (modules) do not share one script file, decreasing coupling between applications.

SPL scripts are managed in a tree structure directory in file system, which makes it convenient to administer the original large number of stored procedures. Thanks to SPL’s independent operation, there is no need to grant high database privileges to applications programmers, increasing database security level.

SPL’s implementation of “outside database stored procedure” employs a series of the library’s features, including independent computational capabilities, procedure computation, agile and easy to code syntax, and open and diverse data source support, making it the ideal alternative to the database stored procedure and the great supplement to databases that originally do not support stored procedures.