The performance improvement of a system is not only the task of performance tuning in the trial operation or maintenance stage, but also not just the development phase, but the whole software life cycle needs attention, effective work can be achieved. So I want to summarize the considerations related to database performance optimization in the different phases of the software lifecycle.
First, the analysis phase
In general, there are often too many areas of concern in the system analysis stage, the system's various functions, availability, reliability, security requirements often attract most of our attention, but we must note that performance is a very important non-functional requirements, must be based on the characteristics of the system to determine its real-time needs, response time requirements , hardware configuration, and so on. It is advisable to have quantified indicators of various needs.
On the other hand, in the analysis phase, the type of the system should be differentiated according to various requirements, the large aspect, the distinction is OLTP (online transaction processing system) and OLAP (Online analytical processing system).
Second, the design phase
The design phase can be said to be the key stage of system performance later, at this stage, there is a process that relates to almost all performance tuning in the future-database design.
After the database design is completed, the preliminary index design can be carried out, the good index design can instruct the coding stage to write high efficiency code, and lay a good foundation for the performance of the whole system.
The following are the performance requirements for the design phase:
1.
Normalization of database logical design
The normalization of database logic design is what we generally call the paradigm, we can simply understand the paradigm:
1th specification: There is no duplicate group or multivalued column, which is the minimum requirement for database design.
The 2nd specification: Each non-critical field must depend on the primary key and cannot rely on some component of a combined primary key. Eliminate part of the dependency, in most cases, the database design should reach the second paradigm.
3rd specification: A non-critical field cannot depend on another non-critical field. Eliminating transitive dependencies, reaching the third paradigm should be a requirement for most tables in the system, except for some special-purpose tables.
The higher paradigm requires no further introduction here, and the individual believes that if all of the second paradigm is reached, most of the third paradigm, the system produces fewer columns and more tables, thus reducing data redundancy and improving performance.
2.
Reasonable redundancy
It is almost impossible to completely standardize a system, unless the system is particularly small, and it is necessary to systematically add redundancy after a standardized design.
Redundancy can be a redundant database, redundant tables, or redundant fields, and different granularity of redundancy can play a different role.
Redundancy can be increased for ease of programming, or it can be increased for performance improvement. From the performance point of view, redundant database can disperse the pressure of database, redundant table can disperse the concurrency pressure of the large scale data, also can speed up the speed of special query, the redundant field can reduce the connection of database table effectively and improve the efficiency.
3.
Design of the primary key
The primary key is necessary, and the primary key for SQL Server is a unique index, and in practice, we tend to choose the smallest key group to work as the primary key, so the primary key is often appropriate as a clustered index for the table. The impact of the clustered index on the query is relatively large, as described in the following index.
In the table with more than one key, the choice of primary key is also more important, generally choose the total length of the small key, small key comparison speed, while the small key can make the primary key of the B-tree structure less hierarchy.
Primary key selection also pay attention to the combination of the primary key field order, for the combined primary key, the different field order of the primary key performance difference may be very large, generally should choose low repetition rate, alone or the combination of large query possibility of the field is placed in front.
4.
Design of foreign keys
Foreign keys as database objects, many people think the trouble without, in fact, foreign keys in most cases is very useful, the reason is:
Foreign key is the most efficient consistency maintenance method, database consistency requirements, can be used in order to use foreign keys, check constraints, rules constraints, triggers, client programs, it is generally believed that the closer to the data of the method is more efficient.
Careful use of cascading deletes and cascading updates, cascading deletions, and cascading updates as new features of SQL SERVER 2000 in the current year, are reserved in 2005 and should have their availability. I'm cautious here because cascading deletions and cascading updates have broken through the traditional definition of foreign keys, which is a bit too powerful to use before you have to make sure that you have mastered the scope of their functionality, otherwise cascading deletions and cascading updates may make your data inexplicably modified or lost. cascading deletions and cascading updates are more efficient than other methods in terms of performance.
5.
Design of the field
A field is the most basic unit of a database, and its design has a significant impact on performance. The following should be noted:
A, the data type as far as possible with the digital type, the digital type comparison is much faster than the character type.
B
The data type is as small as possible, as small as possible to meet the foreseeable future needs of the premise.
C
Try not to allow Nulls, unless necessary, instead of Null+default.
D, less with the text and image, binary field read and write is relatively slow, and, read the method is not much, most of the case is best not.
E
The self-increment field should be used with caution, which is not conducive to data migration.
6.
The design of database physical storage and environment
In the design phase, the physical storage, operating system environment and network environment of the database can be designed so that our system can adapt to more users ' concurrency and larger data volume in the future.
There is a need to pay attention to the role of filegroups, applicable filegroups can effectively spread the I/O operations to different physical hard disks, improve concurrency capability.
7.
System Design
The whole system design, especially the system structure design has a great impact on performance, for the general OLTP system, can choose c/S structure, three-layer C/s structure, the different system structure of its performance key is different.
In the system design phase, some business logic should be put into the database programming, and the database programming includes database stored procedure, trigger and function. The benefit of implementing business logic with database programming is to reduce network traffic and make better use of the pre-compilation and caching capabilities of the database.
8.
Design of the Index
In the design phase, a preliminary index design can be performed based on functional and performance requirements, where the index needs to be designed based on the estimated amount of data and queries, and may differ from what is actually used in the future.
For the selection of indexes, you should change your mind:
A
Depending on the amount of data, it is possible to have only primary keys for which tables need to be indexed, with small amounts of data.
B
Depending on the frequency of usage, which fields need to be indexed, select the fields that are frequently used as join conditions, filter criteria, aggregate queries, and sort as candidate fields for the index.
C
Combine the fields that often appear together, make up the combined index, the field order of the combined index is the same as the primary key, also need to put the most common fields in front, the low repetition rate of the field in front.
D
Do not add too many indexes to a table because the index affects the speed of insertions and updates.
Third, the coding phase
The coding phase is the focus of this article, because in the case of design determination, the quality of the coding almost determines the quality of the whole system.
Coding phase First is the need for all programmers have performance awareness, that is, in the implementation of the function at the same time consider the performance of the idea, the database is able to perform set operation of the tool, we should try to use this tool, so-called set operation is actually a batch operation, is to minimize the large amount of data in the client loop operation, Instead, use SQL statements or stored procedures instead. It is hard to understand the idea and the consciousness, which needs to be realized in the programming process.
Here are some things to note in the programming phase:
1.
Return only the data you need
The return data to the client needs at least the database extracts data, the network transmits the data, the client receives the data as well as the client processing data and so on, if returns the unnecessary data, will increase the server, the network and the client invalid labor, its harm is obvious, avoids this kind of event to need to notice:
A, in the horizontal view, do not write the SELECT * statement, but choose the field you need.
B
Vertically, write the WHERE clause reasonably, and do not write a SQL statement without a where.
C
Note the WHERE clause of the SELECT INTO, because select into inserts data into the staging table, which locks some system tables, and if the WHERE clause returns too much data or is too slow, it can cause the system table to lock for a long time and plug other processes.
D, for aggregate queries, you can further qualify the returned rows with the HAVING clause.
2.
Try to do as little repetitive work as possible
This point is the same as the previous point, is to minimize the invalid work, but this point of emphasis in the client program, it is important to note the following:
A
Controlling multiple executions of the same statement, especially when some basic data is executed many times, is seldom noticed by programmers.
B
Reducing the number of data transformations may require data conversion to be a design problem, but the reduction is the programmer's ability to do so.
C
To eliminate unnecessary subqueries and join tables, subqueries are generally interpreted as outer joins in the execution plan, with additional overhead associated with redundant connection tables.
D
Merge multiple update occurrences of the same condition on the same table, such as
UPDATE EMPLOYEE SET fname= ' haiwer ' WHERE emp_id= ' vpa30890f '
UPDATE EMPLOYEE SET lname= ' YANG ' WHERE emp_id= ' vpa30890f '
These two statements should be merged into one of the following statements
UPDATE EMPLOYEE SET fname= ' haiwer ', lname= ' YANG '
WHERE emp_id= ' vpa30890f '
E
The update operation does not split into the form of the delete operation +insert operation, although the function is the same, but the performance difference is very large.
F
Do not write meaningless queries, such as
SELECT * from EMPLOYEE WHERE 1=2
3.
Note Transactions and Locks
The transaction is the database application and the important tool, it has the atomicity, the consistency, the isolation, the persistence four properties, many operations we all need to use the transaction to guarantee the data correctness. In the use of transactions we need to try to avoid deadlocks and minimize blocking. Special attention needs to be paid to the specific following:
A, the transaction operation process to be as small as possible, can split the transaction to split apart.
B
Transaction operation procedures should not be interactive, because when the interaction waits, the transaction is not over, and many resources may be locked.
C
The transaction operation procedure accesses the object in the same order.
D, improve the efficiency of each statement in the transaction, using indexes and other methods to improve the efficiency of each statement can effectively reduce the execution time of the whole transaction.
E
Instead of specifying the lock type and index, SQL Server allows us to specify the type and index of the lock used by the statement, but in general the SQL Server optimizer chooses the type of lock and index that is optimal under the current data volume and query conditions, which we specify may be more in the present case, But the amount of data and the distribution of data will change in the future.
F
You can select the lowest isolation level (READ UNCOMMITTED) when querying with a lower isolation level, especially when reporting queries.
4.
Note the use of temporary tables and table variables
In complex systems, temporary tables and table variables are difficult to avoid, and for the use of temporal tables and table variables, it is important to note:
A, if the statement is complex and there are too many connections, consider stepping through the temporary table and table variables.
B
If you need to use the same part of the data for a large table multiple times, consider staging this part of the data with temporary tables and table variables.
C
If you need to synthesize data from multiple tables to form a result, consider using temporal tables and table variables to summarize data for these tables in steps.
D, in other cases, you should control the use of temporary tables and table variables.
E
On the choice of temporary tables and table variables, many of the statements are table variables in memory, fast, should be preferred table variables, but in the actual use of the choice is mainly to consider the amount of data to be placed on the temporary table, in the case of large amounts of data, the temporary table faster. F
With regard to temporary table generation using SELECT INTO and CREATE TABLE + INSERT INTO, we have tested, and in general, select INTO is much faster than the CREATE TABLE + INSERT into method, but the select in To locks tempdb's system tables sysobjects, sysindexes, syscolumns, and in a multiuser concurrency environment, it's easy to block other processes, so my advice is to use create TABLE + INSERT into as much as possible in a concurrent system. While a single statement with a large amount of data is used, select INTO is used.
G
Note the collation, which is created with the CREATE table, and if you do not specify a collation for the field, the default collation for tempdb is selected instead of the collation of the current database. If the collation of the current database differs from the collation of tempdb, there will be a collation conflict error when connecting. You can generally avoid these problems by specifying the collation of the field as Database_default when creating a temporary table in create table.
5.
Usage of subqueries
A subquery is a select query that is nested within a SELECT, INSERT, UPDATE, DELETE statement, or other subquery. Subqueries can be used anywhere that an expression is allowed to be used.
Subqueries can make our programming flexible and can be used to implement some special functions. But in performance, often an inappropriate subquery usage can create a performance bottleneck.
If a subquery's condition uses a field of its outer table, this seed query is called a correlated subquery. Correlated subqueries can be introduced with in, not in, EXISTS, not EXISTS.
For related subqueries, it should be noted that:
A, not in, not EXISTS related subquery can use the left join instead of the notation. Like what:
SELECT Pub_namefrom Publishers
WHERE pub_id not in (SELECT pub_id
From TITLES
WHERE TYPE = ' business ')
Can be changed to write:
SELECT A.pub_namefrom Publishers A left JOIN TITLES B
On b.type = "Business" and a.pub_id=b. pub_id
WHERE b.pub_id is NULL
SELECT Titlefrom TITLES
WHERE not EXISTS (SELECT title_id
From SALES
WHERE title_id = TITLES. title_id)
Can be changed to write:
SELECT Titlefrom TITLES left JOIN SALES
On SALES. title_id = TITLES. title_id
WHERE SALES. title_id is NULL
B
If you guarantee that subqueries are not duplicated, in, exists related subqueries can be replaced with inner JOIN. Like what:
SELECT Pub_namefrom Publishers
WHERE pub_id in (SELECT pub_id
From TITLES
WHERE TYPE = ' business ')
Can be changed to write:
SELECT DISTINCT A.pub_namefrom Publishers A INNER JOIN TITLES B
On b.type = "Business" and
A.pub_id=b. pub_id
C
In related subqueries are replaced with exists, such as
SELECT Pub_namefrom Publishers
WHERE pub_id in (SELECT pub_id
From TITLES
WHERE TYPE = ' business ')
You can use the following statement instead:
SELECT Pub_namefrom Publishers
WHERE EXISTS (SELECT 1
From the TITLES WHERE TYPE = ' business ' and
Pub_id= Publishers. pub_id)
D, do not use the subquery of Count (*) to determine whether there is a record, preferably with a left join or exists, such as someone to write such a statement:
SELECT Job_desc from JOBS
Where (SELECT COUNT (*) from EMPLOYEE WHERE job_id=jobs. job_id) =0
Should be changed to:
SELECT JOBS. Job_desc from JOBS left JOIN employee on employee. Job_id=jobs. job_id
WHERE EMPLOYEE. EMP_ID is NULL
SELECT Job_desc from JOBS
Where (SELECT COUNT (*) from EMPLOYEE WHERE job_id=jobs. job_id) <>0
Should be changed to:
SELECT Job_desc from JOBS
where EXISTS (SELECT 1 from EMPLOYEE WHERE job_id=jobs. JOB_ID)
6.
Caution With Cursors
The general operation of a database is a collection operation, that is, a set of result sets determined by the WHERE clause and the selection column, which is a way to provide a non-collection operation. In general, cursors implement functionality that is often equivalent to a loop implementation of the client, so, in most cases, we move the cursor functionality to the client.
Cursors are a very large drain on database resources (especially memory and lock resources) by placing the result set in server memory and by looping through a single processing record, so we should only use cursors if there are no other methods.
In addition, we can use some of the features of SQL Server instead of cursors to achieve the goal of speed improvement.
A, String connection example
This is a common example of a forum where a string field of a table's qualifying record is concatenated into a variable. For example, it is necessary to connect the fname of the job_id=10 employee with a comma, and perhaps the most likely thing to think of is a cursor:
DECLARE @[url=url]name[/url] varchar DECLARE @NAME varchar (1000)
DECLARE name_cursor CURSOR for SELECT FNAME from EMPLOYEE WHERE job_id=10 ORDER by emp_id
OPEN name_cursor FETCH NEXT from Rname_cursor to @NAME
While @ @FETCH_STATUS = 0
BEGIN
SET @NAMES = ISNULL (@NAMES + ', ', ') [email protected] FETCH NEXT from Name_cursor to @NAME
END CLOSE Name_cursor
Deallocate name_cursor
Can be modified as follows, the same function:
DECLARE @NAME VARCHAR () SELECT @NAMES = ISNULL (@NAMES + ', ', ') +fname
From EMPLOYEE WHERE job_id=10 ORDER by emp_id
B
Example of conversion using case
Many of the reasons for using cursors are that some processing needs to be handled differently depending on the circumstances of the record, and in fact, we can use the case-when statement to do the necessary processing of the judgments and cases when they can be nested. Like what:
Table structure:
CREATE Table Material Sheet (Part number VARCHAR (30),
Name varchar (100), Master unit varchar (20),
Unit 1 VARCHAR (20), Unit 1 parameter NUMERIC (18,4),
Unit 2 VARCHAR (20), Unit 2 parameter NUMERIC (18,4)
)
GO
CREATE Table Inbound Table (Time DATETIME,
Material number VARCHAR (30), Unit INT,
Storage quantity NUMERIC (18,4), Damage quantity NUMERIC (18,4)
)
GO
Where the Unit field can be 0,1,2, representing the main unit, Unit 1, Unit 2, many calculations need a unified unit, unified units can be implemented with cursors:
DECLARE @ number VARCHAR (30), @ Unit INT,
@ parameter NUMERIC (18,4),
DECLARE CUR CURSOR for SELECT material number, unit from inbound table WHERE unit <>0
OPEN Curfetch NEXT from CUR to @, @ unit
While @ @FETCH_STATUS <>-1
BEGIN
IF @ Unit =1
BEGIN
SET @ parameter = (SELECT Unit 1 parameter from material table WHERE part number [email protected] number) UPDATE Inbound table SET quantity = Quantity *@ parameter, damage quantity = Damage Quantity *@ parameter, unit =1 WHERE Current of CUR
END IF @ Unit =2
BEGIN SET @ parameter = (SELECT Unit 1 parameter from material table WHERE material number [email protected]
UPDATE Inbound Table SET quantity = Quantity *@ parameter, damage quantity = number of damage *@ parameter, unit =1 WHERE Current of CUR END
FETCH NEXT from CUR to @ material, @ Unit end
CLOSE CUR
Deallocate CUR
Can be changed to write:
UPDATE a SET quantity =case a. Unit when 1 then A. Quantity *b. Unit 1 parameter
When 2 then a. Quantity *b. Unit 2 parameter ELSE A. Quantity
END, number of damage = case A. Unit when 1 then A. Damage quantity *b. Unit 1 parameters
When 2 then A. Damage quantity *b. Unit 2 parameter ELSE A. Number of damage
END, Unit =1
From inbound table A, material table bwhere A. Unit <>1 and
A. Material number =b.
C
Example of a variable participating UPDATE statement
SQL erver statements are flexible, and variable-participating update statements can implement some cursor-like functionality, such as:
In
SELECT A,b,c,cast (NULL as INT) as ordinal into #T
From table
ORDER by A, NEWID ()
After the temporary table is generated, the A field has been sorted, but in the same case a disorderly order, if you need to change the ordinal field in accordance with the number of records grouped by the A fields, only the cursor and the variables participate in the UPDATE statement can be implemented, the variable participates in the UPDATE statement as follows:
DECLARE @A intDECLARE @ ordinal INT
UPDATE #T SET @ ordinal =case when [e-mail protected] Then @ sequence +1 ELSE 1 END,
@a=a,
serial number [email protected] serial number
D, if you must use a cursor, note the type of the cursor, if you just loop through the data, then you should use a forward-only cursor (option Fast_forward), generally only need a static cursor (option static).
E
Note the uncertainty of dynamic cursors, if the recordset data of a dynamic cursor query is modified, the cursor is automatically refreshed, which makes the dynamic cursor nondeterministic, because in a multiuser environment, a cursor's recordset may be flushed if another process or itself changes the record. 7.
use indexes as much as possible
After indexing, not every query uses an index, and the efficiency of indexing can vary greatly when using indexes. As long as we do not enforce the specified index in the query statement, the selection and use of the index is an automatic choice of SQL Server optimizer, and it is selected based on the conditions of the query statement and the statistics of the related table, which requires us to write the SQL statement as far as possible to make the optimizer use the index.
In order for the optimizer to use the index efficiently, it should be noted when writing statements:
A, do not operate on the indexed fields, but want to do a change, such as
SELECT ID from T WHERE num/2=100
should read:
SELECT ID from T WHERE num=100*2
SELECT ID from T WHERE num/2=num1
If Num has an index, it should read:
SELECT ID from T WHERE num=num1*2
If the NUM1 has an index, it should not be changed.
You have found a statement like this:
SELECT year, month, amount from balance table
WHERE 100* year + month =2007*100+10
should read:
SELECT year, month, amount from balance table where year =2007 and
Month =10
B
Do not format conversion of indexed fields
Examples of date fields:
WHERE CONVERT (VARCHAR (10), date field, 120) = ' 2008-08-15 '
should read
Where date field 〉= ' 2008-08-15 '
and Date fields < ' 2008-08-16 '
Examples of IsNull conversions:
where ISNULL (field, ') <> ' should be changed to: where Field <> '
WHERE ISNULL (field, ') = ' should not be modified
where ISNULL (field, ' F ') = ' t ' should be changed to: where field = ' t '
WHERE ISNULL (field, ' F ') <> ' t ' should not be modified
C
Do not use functions on indexed fields
where left (NAME, 3) = ' abc ' or where SUBSTRING (name,1, 3) = ' abc '
should read:
WHERE NAME like ' abc% '
Examples of date queries:
where DATEDIFF (day, date, ' 2005-11-30 ') =0 should read: where date >= ' 2005-11-30 ' and date < ' 2005-12-1 '
where DATEDIFF (day, date, ' 2005-11-30 ') >0 should be changed to: where Date < ' 2005-11-30 '
where DATEDIFF (day, date, ' 2005-11-30 ') >=0 should be changed to: where Date < ' 2005-12-01 '
where DATEDIFF (day, date, ' 2005-11-30 ') <0 should be changed to: where Date >= ' 2005-12-01 '
where DATEDIFF (day, date, ' 2005-11-30 ') <=0 should be changed to: where Date >= ' 2005-11-30 '
D, do not make multi-field connections to indexed fields
Like what:
WHERE fame+ '. ' +lname= ' Haiwei. YANG '
should read:
WHERE fname= ' Haiwei ' and lname= ' YANG '
8.
Note the wording of the join condition
The connection condition of the multi-table connection is of great importance to the selection of the index, so we need special attention when we write the condition of the connection.
A, multi-table connection, the connection conditions must be written in full, rather repeat, do not leave gaps.
B
Join conditions use clustered indexes whenever possible
C
Note the difference between the on condition and the where part condition
9.
Other places to be aware of
Experience has shown that the sooner the problem is discovered, the lower the cost, and many performance problems can be found in the coding phase, in order to identify performance issues early, it is important to note:
A, programmers pay attention to the amount of data in each table.
B
The coding process and the unit test process use the database test with large amount of data as far as possible, preferably with actual data test.
C
Each SQL statement is as simple as possible
D, do not update data for tables that have triggers frequently
E
Note the limitations of database functions and their performance
10.
Learn to distinguish the merits of SQL statements
It is very important for me to distinguish the merits of SQL statements, and to write efficient statements only if you can distinguish them.
A
View the execution plan of the SQL statement, you can analyze its use ctrl+l graphical display execution plan in the query, generally should pay attention to the percentage of the largest number of graphic properties, move the mouse over it will show the properties of this graphic, you need to pay attention to the estimated cost of data, but also pay attention to its title, It is generally CLUSTERED Index seek, index seek, CLUSTERED index Scan, index Scan, TABLE scan, etc., where the scan description statement has the leeway to optimize. You can also use the statement
SET Showplan_all on
The statement to execute
SET Showplan_all OFF
View the text details of the execution plan.
B
Use Profiler to track the operation of the system, to trace the execution of the statement, and the time spent, CPU usage, and I/O data to analyze the efficiency of the statement.
C
You can use the Windows System performance detector to focus on CPU, I/O parameters
Iv. testing, commissioning and maintenance phases
The main task of testing is to identify and modify the system's problems, where performance issues are also an important aspect. The focus should be on identifying performance issues and making the necessary optimizations. Mainly for sentence optimization, index optimization and so on.
Commissioning and maintenance phases are operating in a real-world environment, with a wider range of problems, potentially involving operating systems, networks, and multi-user concurrency, as well as optimization of operating systems, networks, and physical storage of databases.
This phase of the optimal flower method is not expanded here, only to illustrate the following methods of index maintenance:
A
You can use the DBCC DBREINDEX statement or the SQL Server maintenance plan to set a timed index rebuild, which is designed to improve the performance of the index.
B
You can use the statement update statistics or the SQL Server maintenance plan to set a timed update of index statistics, which is intended to make the statistics more reflective of the actual situation, thus allowing the optimizer to select a more appropriate index.
C
You can use DBCC CHECKDB or DBCC CHECKTABLE statements to check database tables and indexes for problems, and these two statements can also fix general problems.
D
V. Personal disagreement on some of the statements in the online materials
1.
"You should try to avoid NULL values for a field in the Where clause, or it will cause the engine to discard full table scans using the index, such as:
SELECT ID from T WHERE NUM is NULL
You can set the default value of 0 on NUM, make sure that the NUM column in the table does not have a null value, and then query:
Select ID from T WHERE num=0 "
Personal opinion: After testing, is null can also be found with index seek, 0 and Null are different concepts, the above statement of two query meaning and the number of records is different.
2.
"You should try to avoid using the! = or <> operator in the WHERE clause, or you will discard the engine for full table scanning using the index. ”
Personal opinion: After testing the,<> can also be found with index seek.
3.
"You should try to avoid using or in the WHERE clause to join the condition, otherwise it will cause the engine to abandon using the index for a full table scan, such as:
SELECT ID from T WHERE num=10 OR num=20
You can query this:
SELECT ID from T WHERE num=10
UNION All
Select ID from T WHERE num=20 "
Personal opinion: The main view of full-scale scanning is unfavorable.
4.
"In and not" should also be used with caution, otherwise it will result in a full table scan, such as:
SELECT ID from T WHERE NUM in
For consecutive values, you can use between instead of in:
Select ID from T WHERE NUM between 1 and 3 "
Personal opinion: The main view of full-scale scanning is unfavorable.
5.
"If you use a parameter in the WHERE clause, it also causes a full table scan. Because SQL resolves local variables only at run time, the optimizer cannot defer the selection of access plans to run time; it must be selected at compile time. However, if an access plan is established at compile time, the value of the variable is still unknown and therefore cannot be selected as an input for the index. The following statement will perform a full table scan:
SELECT ID from T WHERE [email protected]
You can force the query to use the index instead:
Select ID from T with (index name) WHERE [email protected] "
Personal opinion: The interpretation of local variables is rather strange, if the use of parameters will affect performance, the stored procedure in addition to the school, I insist on my view of forced indexing.
6.
"Use Varchar/nvarchar instead of Char/nchar as much as possible, because the first variable-length field has a small storage space, saving storage space, and secondly for queries, the search efficiency in a relatively small field is obviously higher." ”
Personal opinion: "The search efficiency is obviously higher in a relatively small field" is obviously right, but the length of the field does not seem to be determined by the variable length, but by the business itself. In SQLSERVER6.5 or previous versions, long-range string fields are slower to compare faster than fixed-length string fields, so for those versions, we recommend using fixed-length fields to store some key fields. In the 2000 version, modified the variable length of the String field comparison method, and fixed-length field comparison speed is not very different, so for convenience, we use a lot of indefinite long field.
7.
With regard to the order of joining tables or the order of conditions, it has been tested, in SQL SERVER, that these orders do not affect performance, which may be valid for Oracle. Source: http://www.jb51.net/article/16026.htm
Summary of SQL Server performance optimizations
