A typical example of SQL Server from an algorithm

Algorithm is an important research direction in computer science, and it is the key to solve complex problems. In the computer world, algorithms are everywhere. The database is the place that stores the data and executes the large quantity computation, in the database uses some simple SQL commands, carries on the storage, the inquiry, the statistics, solves the real world problem already is not uncommon. With the increase of data volume and the increasing complexity of business rules, a special method is needed to meet the requirements of efficiency and accuracy. How to transform the complex algorithm of solving the problem into the command that the database can execute is also an aspect of the research of database application technology. This article describes the example in the order of MSSQL.

The database can store the entity's data set, in the operation, the database uses the batch computation method to process the data, the batch reads the data from the storage device, processing then the batch of write back storage device. Some databases provide cursors that can be read out of each field in a row of data in a table, perform complex business rule calculations on those fields, and then write back to the database. Compared with the method of using batch, the method of batch calculation consumes relatively little resources, while the use of cursors consumes too much resources, the speed is slow, the efficiency is low and there are many restrictions, such as locking conditions.

For example, for the database stored in the student performance Student_score (Sno,cno,score,level), grades ranging from 0 to 100 points, if you need to store a field after the score of the word level to explain the merits of the score, 90 points above the a,80-90 divided into B , 60-80 points for the following c,60 is D, the following several algorithms can achieve the same goal:

1. Define a cursor, select all the record records in the Student_score table, define a variable @cur_score that stores the score, store the score of the current record, and define a variable @cur_level that stores the score level of the current score to store the mark for good or bad results. The algorithm is as follows: If the record in the cursor is not empty, take the result of the current record from the cursor, judge the fractional segment of the score, store the result in the variable @cur_level, and update the Level field in the current record with the value in @cur_level. The whole process needs to read the database at least two times, once to get a record, to write to the database, each record needs to go through this process, the efficiency is relatively low.

2. Update the database in batches, set the value of all level fields to D, update the database again, update the Level field of the record that is greater than or equal to 60 to C, and then update B and A. A disadvantage of this is that some records of the level field has been updated several times, such as a record the last level field value is a, then it is first updated to D, in turn, is updated to C, B, A. These repetitive updates can be eliminated, and an improved algorithm eliminates the cost of repetitive updates. The updated algorithm is such that the level field of records between 0 and 60 points is updated to D and the scores of each score section are updated sequentially. The implementation of this algorithm is not difficult to write the SQL statement, the use of Between...and ... An expression can be used to express a selection condition such as a record between 80 and 90.

3. In view of the final analysis of the second method, the use of Between...and ... When an expression references a table to update records, it is convenient to express fractions and corresponding level information, store the information in a table level_about, and refer to the table in the process of updating the Student_score table. In the calculation process, the contents of the Level_about table need to be read out and then calculated. For the whole calculation process, sacrificing space and part of efficiency to facilitate the operation, because the speed of the computer is very fast, level_about table occupies a small space, this loss can be ignored. The information in the Level_about table contains at least 3 fields: Start_score, recording the starting score, End_score record termination score, and the score between the start and end points should be achieved. The data in the table should resemble this:

Start_score End_score level
0 59 D
60 79 C
80 89 B
90 100 A

Updating records in the Student_score table requires that the level of achievement in the current record be judged by Start_score and End_score, and the SQL statement implemented in MSSQL:

Update student_score set
student_score.level=level_about.level from
level_about where student.score
between level_about.start_score and level_about.end_score