[SQL]-Report query efficiency optimization

Background

The system serializes the data object JSON into a database field.
The report module needs to get the value of real-time data objects, and it is time-consuming and inefficient to make numerical judgments in SQL.

Analysis

The inefficiency of the current implementation is mainly the unreasonable design of the program structure.

The SQL bottleneck is as follows, where wordstr is the statistic keyword, and 7 is the string length of wordstr.

sum (len(@colName-len(REPLACE(@colName'wordstr  "))/7)

Solve

Idea 1: Use code to re-implement the statistical function again.
Problem: The program structure changes large amount, the workload is big, temporarily does not adopt.

Idea 2: Optimize data structures and cache statistical variables.
2.1 Add the Statistics Results field, which holds the statistical key values for each row of records.
2.2 Update the data table for all the statistic results fields.
2.3 New trigger, automatically update Insert, update line record changes.
2.4 Update the report SQL and get the results directly from the statistics results.

[SQL]-Report query efficiency optimization