How to write an efficient SQL script "I"-ASP. NET

How to write an efficient SQL script "I"
Note: test records: 3040000 records per table.
How to Write efficient SQL scripts:
1. Design SQL scripts in the form of Sarg
Definition of Sarg: An operation used to restrict search, because it usually refers to a specific match, a matching within a worthy range or an and connection between two or more conditions. The [index scan] format is as follows:
Column name Operator
Or
Operator column name
The column name can appear on one side of the operator, while the constant or variable appears on the other side of the operator. For example:
Name = 'ata'
Quantity> 5000
5000 name = 'ata 'and quantity> 5000
If an expression cannot meet the form of Sarg, it cannot limit the search range, that is, SQL Server must determine whether each row meets all the conditions in the WHERE clause. Therefore, an index is useless for expressions that do not meet the Sarg format.
2. Like
Whether the like statement belongs to the Sarg depends on the type of the wildcard used.
For example, name like 'ata % ', which belongs to Sarg
Name like '% ata' does not belong to Sarg.
The reason is that the wildcard % at the beginning of the string makes the index unusable.
3. Or and in
Or will cause full table Scan
Name = 'ata 'and quantity> 5000 symbol Sarg, but: Name = 'ata' or quantity> 5000 does not match Sarg. Using or and in causes full table scan.
4. statements not in the Sarg format caused by non-operators and functions
The most typical case for statements that do not meet the requirements of the Sarg format is to include non-operator statements, such as: Not ,! = ,,! , Not exists, not in, not like, is null, not null, and other functions. Below are several examples that do not meet the Sarg format:
ABS (Quantity) name like '% ata'
Some expressions, such:
Where quantity * 2> 5000
SQL Server will also regard it as Sarg, and SQL server will convert this formula:
Where quantity> 2500/2
This is not recommended because sometimes SQL Server cannot guarantee that this conversion is completely equivalent to the original expression.
5. The charindex () function, the like with the wildcard % added before, and the Efficiency Comparison with % added after
If the wildcard % is added before the like, it will cause a full table scan, so the execution efficiency is low. It is incorrect to use the charindex () function to replace the like function to greatly improve the speed. The test is as follows:
1. Select fcandidateid, fcandidatename from tcandidate where fcandidatename like 'Tim %'
Time: 36 seconds, number of records: 2 million
2. Select fcandidateid, fcandidatename from tcandidate
Where charindex ('Tim', fcandidatename)> 0
Time: 47 seconds, number of records: 2 million
3. Select fcandidateid, fcandidatename from tcandidate
Where fcandidatename like '% Tim %'
Time: 45 seconds, number of records: 2 million
We can see from the above three examples that when we use like, the "Tim %" is followed by the Sarg rule, and the time is obviously less than the last two, and the performance of the last two is almost the same.
If it is not fuzzy, such as substring (fcandidatename,) = 'A', you can consider this: fcandidatename like 'a % 'instead (because index scanning is used, not a table scan)

How to write an efficient SQL script II
6. Field extraction should follow the principle of "How much is required and how much is requested" to avoid "select *"
Here is a test:
A) Select fcandidateid, fcandidatename from tcandidate where fcandidatename like 'Tim %'
Time used: 35 seconds record results: 2 million
B) Select fcandidateid, fcandidatename, fcredentialsid, fbirthday from tcandidate where fcandidatename like 'Tim %'
Time in use: the number of records in 51 seconds: 2 million
C) Select fcandidateid from tcandidate where fcandidatename like 'Tim %'
Time used: 23 seconds record result count: 2 million
From this point of view, each time we extract one field less, the data extraction speed will be improved accordingly. Of course, the speed of improvement depends on the size of the field you discard.
7. The use of order by, using those fields as sorting efficiency is relatively high
1. Sort fields with a family index or primary key.
Select fcandidateid, fcredentialsid, fcandidatename, fbirthday, fregionid from tcandidate where fcandidatename like 'Tim % 'order by fcandidateid DESC
Time: 45 seconds, record results: 2 million
2. Use numeric fields (neither primary key nor family index or foreign key)
Select fcandidateid, fcredentialsid, fcandidatename, fbirthday, fregionid from tcandidate where fcandidatename like 'Tim % 'order by fregionid DESC
Time used: 1 minute 15 seconds number of records: 2 million
3. Use the string field [numeric string] (neither primary key nor family index or foreign key)
Select fcandidateid, fcredentialsid, fcandidatename, fbirthday, fregionid from tcandidate where fcandidatename like 'Tim % 'order by fcredentialsid DESC
Time used: 1 minute 22 seconds number of records: 2 million
4. Use a string [A string consisting of letters]
Select fcandidateid, fcredentialsid, fcandidatename, fbirthday, fregionid from tcandidate where fcandidatename like 'Tim % 'order by fcandidatename DESC
Time used: 1 minute 34 seconds number of records: 2 million
5. Use the Date Field
Select fcandidateid, fcredentialsid, fcandidatename, fbirthday, fregionid from tcandidate where fcandidatename like 'Tim % 'order by fbirthday DESC
Time used: 1 minute 9 seconds number of records: 2 million
From the above, we can see that it is faster to sort the fields of family index and primary key. In addition, if the table has a foreign key field, you can give priority to these fields.
The numbers, numeric strings, letter strings, and dates are sorted. For more information about the performance, see the test results. The date fields in the test results are sorted higher than the numbers, you can test the similar data volume and compare the speed of the numeric and date fields.
You can see the other situations at a glance.
At the same time, when sorting by a field, whether in positive or reverse order, the speed is basically equivalent.
8. Note the need to insert a large binary value to the image Column
If you want to insert a large binary value to the image column, use the stored procedure. Do not insert it using an embedded insert statement. In this way, the application first converts the binary value to a string (twice the size), and then converts the string to a binary value after receiving the character.
Stored procedures do not have these actions: the front-end calls this stored procedure to pass in binary parameters, which significantly improves the processing speed.
9. Efficient top
In fact, when you query and extract data sets with a large capacity, extract the required data once as needed. If you allow the top statement as much as possible. For example:

Select top 50000 * from (
Select top 2000000 fcandidateid, fcandidatename, fbirthday from tcandidate
Where fcandidatename like 'Tim %'
Order by fcandidateid DESC) as

Time: 1179 milliseconds

You can take a closer look at the preceding query statements and compare them with the preceding statements to see how high the top efficiency is.
Theoretically, the execution time of the entire statement is longer than that of the clause, but the opposite is true. Because 2000000 records are returned after the sub-statement is executed, and only 50000 statements are returned for the entire statement, the biggest factor affecting the database response time is physical I/O operations. One of the most effective ways to restrict physical I/O operations is to use top keywords. The top keyword is a system-optimized term in SQL Server used to extract the first few or the first few percentage data entries. Therefore, the top efficiency of optimization algorithms is very high.
10. Notes on how to use SQL Functions
1. SQL functions consume different costs in different locations in SQL scripts
Select fcandidateid, fcredentialsid, fcandidatename, fbirthday, fregionid
From tcandidate where dateadd (year, 5, fbirthday) = '2017/08'
CPU: 2079 time: 25317 milliseconds record: 1000000
2. Select fcandidateid, fcredentialsid, fcandidatename, fbirthday, fregionid from tcandidate where fbirthday = dateadd (year,-5, '2017/08 ')
CPU: 1219 time: 21666 millisecond record result: 1000000
Therefore, we can see from the above that different SQL functions have different locations and different performance and consumption costs. The general principle is to put SQL functions on the right of the condition, and the cost of performance consumption is relatively low.
3. Do not use distinct unless necessary. Similar to Union, distinct slows down the query. Do not use distict easily if no duplicate records exist in the Validation result set.

11. Table and temporary table usage
1. In general, try to use table variables instead of temporary tables. For more information about recommended table variables, see the following explanation:
L table variables (such as local variables) have clearly defined ranges. These table variables are automatically cleared when the range ends.
L compared with temporary tables, table variables cause less re-Compilation of stored procedures.
L transactions involving table variables only maintain the update duration on the table variables. Therefore, when using Table variables, You need to lock and record resources in less cases. Because table variables have a limited range and are not part of a persistent database, transaction rollback does not affect them.
2. When to use table variables instead of temporary tables
• Number of rows inserted into the table.
• The number of times the query is recompiled.
• Query types and their dependence on performance indexes and statistical information.
3. for table variable defects, you can search for table variable on msdn)