The difficulty to migrate a stored procedure to other databases is always a subject of criticism. This is seldom forgotten every time when the shortcomings of the stored procedure are listed.
The migration of a stored procedure for handling the complex business logic is particularly problematic because its coding depends on the unique features and syntax of different databases and thus it needs to be recoded with the database changed. The cost won’t be very high if only the functions are replaced and the parameter writing rule is changed (like date conversion). But if the database to which a stored procedure is migrated doesn’t support a certain feature (like the window functions), the algorithm needs to be rewritten. The migration could become impossible if the performance is on the list of demands.
Fortunately, the migration of stored procedures isn’t a high user demand.
Years ago when the database market was characterized by fierce competitions, it was common for users to switch between different database products. Then the market of conventional relational databases evolved to enter today’s relatively stable phase when various industries and trades have had their own commonly used databases. For users the changeover to a new database is far more than the migration of stored procedures. It is a huge project involving the transformation in usage habit and maintenance personnel training. Unless a major change occurs, there won’t be a database migration. Though stored procedures are difficult to migrate, this isn’t enough to give them up.
The standard BI software intended for various industries and users, which usually needs to connect to different databases, seldom uses the stored procedure. It requires only the switchover between SQL functions, which is simple and easy.
We can find through a study that the difficulty of stored procedure migration mainly exists in the changeover process from a commercial database to an open source database (including the data warehousing products based on the big data platform that are emerging in recent years). However, the migration isn’t that difficult between commercial databases in the same price range, and it is easy from an open source database to a commercial database (though this is rare).
As stated previously, the real reason behind the difficulty to migrate a stored procedure between different kinds of databases lies in the difference of the functionalities and features these databases provide. Syntax is only a vehicle to express. Commercial databases in the same price range have similar functionalities, and the migration needs mainly the change of the syntax, which is relatively easy. While bearing the same name and supporting basic SQL features, there are huge differences between open source databases and mature commercial databases in functionalities. They are essentially two things according to many critical features, like the world’s number one commercial database and the world’s leading open source database. The former has an excellent support for SQL2003 standards and a relatively complete set of window functions. The latter performs a FULL JOIN by converting it to UNION, and hasn’t any support for window functions. The migration of a stored procedure between them means a recoding. The obstacle isn’t due to the migration itself, because if we chose to develop the application with an open source database in the beginning, we’ll encounter a problem as big as we are facing now.
When we are talking about the migration cost, we are speaking of the migration between two platforms with almost equal functionalities. On either of these platforms, the initial development work is the same when measured by complexity. To write a piece of C++ code under both Windows and Linux, for instance, has the same degree of difficulty. In this situation, the discussion of migration workload is practical. Converting a piece of Java code to the C++ code, however, isn’t migration any more, since the difficulties of initial development are not on the same level. That is the type of problem the migration from a commercial database to an open source database has.
At present, industries are seeing a trend of switchover from commercial databases to open source databases or big data platforms. This is leading to a big drop in the cost of database building. But market is fair. The cost reduction in purchasing a database that isn’t the result of technology advance will surely be offset by the high development cost.
