Shulou(Shulou.com)06/02 Report--

This article mainly explains "what are the disadvantages of writing report data sources with stored procedures and JAVA". Interested friends may wish to take a look. The method introduced in this paper is simple, fast and practical. Let Xiaobian take you to learn "what are the disadvantages of writing report data sources with stored procedures and JAVA"!

We often use stored procedures to prepare data in report development. Stored procedures support step-by-step calculation and can implement very complex calculation logic, which brings convenience to report development. Therefore, it is not uncommon to see such stored procedures in report development:

3008 lines, 141KB stored procedure, what bad impact will it bring to report development?

1. Editorial Debugging

Stored procedures are difficult to edit and debug, so the development cycle of thousands of lines of stored procedures is often counted in weeks or months, which will seriously affect the development efficiency of reports, and the report requirements proposed by the business seem to be "urgent."

2. maintainability

Maintenance may be done more often than development is done once. Reports are often modified in actual business, which is called poor stability of report business. The logic of preparing data for a report changes, and the stored process of modifying thousands of rows is a nightmare for most report developers.

Sometimes such reports are split into two groups, with DBAs or professional programmers writing stored procedures for the front-end report developers to report to, thus avoiding report developers writing stored procedures. However, the process of modifying such reports will be longer. Modifying a report involves communication between multiple personnel (including business personnel). If the two groups of people responsible for the front and back ends of the report belong to different teams, it will be even more troublesome.

3. knowledge inheritance

Maintainability leads directly to another problem of "knowledge transmission." Or take the above report as an example, if a new person wants to change the above report, how long do you think it will take him to understand the stored process and change the report?

Of course, this problem also involves a lot of management means. From the technical point of view alone, it is very difficult for such a report to pass on knowledge well.

4. safety

The modification of the storage process requires high database permissions, and the report is often changed to operate the database frequently, which is also a hidden danger to database security, and it also requires a strong management mechanism to ensure one or two.

5. portability

Most of the reasons why stored procedures are now prohibited are that they are not portable. If future database changes need to be migrated, whether it is to change the database type in the future or to expand the system (separate tables and databases), a large number of stored procedures that cannot be migrated are definitely the most troublesome problem.

Of course, the "library change" thing still doesn't happen frequently even today, but it's enough to happen once (pay attention to localization or system expansion expectations).

6. coupling

Stored procedures result in tight coupling between the report application (front-end) and the database (back-end) in terms of maintainability, security, and portability. In addition to the three problems mentioned above, tight coupling can also make the database bloated and affect database performance.

Important things have been said many times, the business of the report is unstable, the report is often added and modified, and sometimes deleted (not used), and the stored procedure prepared for this report is still in the database. At this time, it is difficult to delete this stored procedure.

Why not?

Because you don't know if other programs are sharing this stored procedure, deleting it will not affect other programs. The result is that the database stored procedures accumulate more and more leading to database bloat, and some stored procedures will involve automatic operation, although stored procedures may no longer be used, but still consume database resources, in the long run database performance degradation will become inevitable.

7. Multi-source support

Stored procedures run within closed databases and cannot be mixed across multiple data sources. Regarding the multi-source problem, a few years ago, it was not significant in report development, when everyone used relational libraries; but now it is different, the data of the same report may come from multiple different types of data sources (RDB/NoSQL/TxT/Excel/Hadoop/ES, etc.), and stored procedures are powerless.

How do you fix these problems?

Is there a way to solve these problems caused by storage procedures?

Of course there is!

There is nothing hard coded can't solve! Use JAVA instead of stored procedures to solve the above problem, running independently of the database (self-searching SOA and microservices concepts). Stored procedures a display of the benefits is that you can step by step to achieve the report data preparation logic, this advantage JAVA also has, even more thorough than stored procedures, to put it bluntly: JAVA discreteness is better.

JAVA is just more trouble to write, too difficult for report developers, if you want to add a modifier that is too XX difficult. The SQL language used in stored procedures is very suitable for set operations, and a group summary is written by a group. In contrast, JAVA does not have this advantage, and grouping summary may have to write dozens of hundreds of lines (the lack of class libraries will make the development complexity rise sharply. Think about why you don't use JAVA instead of writing programs?).

Java has other problems that cannot be ignored.

Hot switching is not supported

Java also has a fatal flaw, that is, it does not support hot switching. Reports often need to be modified (again). After modifying the report data source, they need to be recompiled and restarted before they can take effect. This is unacceptable to most business systems. The report stresses not only that the query is immediately available, but also that the modification should take effect in real time.

Reports are tightly coupled to applications

Similar to the use of stored procedures that lead to tight coupling between reports and databases, preparing report data sources with JAVA leads to tight coupling between report modules and other business modules of the application that are not suitable for maintenance.

We know that most reports are integrated into the application system as a module to provide report query services. The integration method can be API (jar package) tight integration; reports can also be published as services separately and loosely integrated through service invocation. In this way, any pressure or problem generated by the report server will not affect the application system (high availability).

* After API tight integration, since the report data source is written in JAVA, it must be packaged with the main application code, which cannot be maintained as an independent module, and it is basically impossible to split it in the future;

* Services loose integration is completely impossible.

Therefore, although it is feasible to write report data sources with JAVA, it is not particularly ideal.

Is there any other way?

We compare the advantages and disadvantages of stored procedures and JAVA can be found, solve the problem should be along the inheritance of the advantages of both, improve the direction of shortcomings. For clarity, summarize the points needed.

The main points are listed, and our goal is to find the technical means to support them (the row with the question mark).

Easy to develop and maintain

This presupposes that these capabilities should be built into the reporting tool, so that the report developer can use the tool to do the report development himself, without relying on others or teams;

hot switching

Hotswitching requires that the technique be interpretive so that changes can take effect in real time;

Support for multiple sources

Ability to interface multiple different types of data sources for hybrid computing, such as text and database table join;

Low coupling, portable

Data preparation capability and report presentation capability are built into the report, nature is decoupled from applications and data sources, and there is no pressure for future system expansion.

In this way, we can easily think of adding a calculation module to the report end to replace the stored procedure or JAVA to prepare data for the report. This module can be implemented by a script embedded in the report tool, and the structure can be as follows.

Scripts should have complete computing power (anything can be calculated), support for multiple sources, and interpretation execution allows hot switching of these capabilities.

At this point, I believe that we have a deeper understanding of "what are the disadvantages of writing report data sources with stored procedures and JAVA", so we may wish to actually operate it! Here is the website, more related content can enter the relevant channels for inquiry, pay attention to us, continue to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.