MSSQL Performance Optimization

The following articles are reproduced in: http://www.cnblogs.com/luluping/archive/2009/08/06/1540763.html#undefined

Recently due to work needs, hope that a more comprehensive summary underSQL SERVERDatabase performance Optimization related considerations, search on the internet, found a lot of articles, and some have listed hundreds, but look closely to find that there are many specious or outdated (may be the SQL SERVER6.5 previous version or Oracle is applicable) information, Had to be based on previous experience and test results to summarize.

I always believe that the performance of a system is not only a test run or maintenance phase of performance tuning tasks, is not only the development phase of things, but in the entire software life cycle need to pay attention to the effective work to achieve. 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, the standardization of Database logic 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, the 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, the foreign key design

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, the 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, where the minimum is to meet the foreseeable future needs of the premise.

C, try not to allow NULL, unless necessary, you can use not null+default instead.

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, self-increment field to use with caution, not conducive to data migration.



6. Database physical storage and environment design

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, the 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, according to the amount of data to determine which tables need to increase the index, the small amount of data can only the primary key.

B, depending on the frequency of use to determine which fields need to be indexed, select the fields that are frequently used as join conditions, filter criteria, aggregate queries, and sort as candidates 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, a table does not add too many indexes, 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, and do not write a SQL statement without where.

C, note the WHERE clause of the SELECT INTO, because the select into inserts data into the temporary table, this process locks some system tables, if the WHERE clause returns too much data or too slow, it will cause the system table long-term lock, plug other processes.

D, for aggregate queries, you can further qualify the returned rows with the HAVING clause.

2, try to do less repetitive work

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, control the execution of the same statement multiple times, especially the number of basic data execution is rarely noticed by many programmers.

B, reduce the number of data conversion, may require data conversion is a design problem, but the reduction is the programmer can do.

C, eliminate unnecessary sub-queries and join tables, sub-query in the execution plan is generally interpreted as an external connection, redundant connection table brings additional overhead.

D. Combine multiple update 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, update operation do not split into the form of delete operation +insert operation, although the function is the same, but the performance difference is very large.

F, do not write some meaningless queries, such as

SELECT * from EMPLOYEE WHERE 1=2

3. Attention to 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, the transaction operation process should not have interaction, because when the interaction waits, the transaction does not end, possibly locking a lot of resources.

C, the transaction operation procedure to access 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, try not to specify the lock type and index, SQL Server allows us to specify the type of lock and index used by the statement, but in general, the SQL Server optimizer chooses the lock type and index is optimal under the current data volume and query conditions, we have specified may be only in the current situation more, But the amount of data and the distribution of data will change in the future.

F, the query can use a lower isolation level, especially when the report query, you can choose the lowest isolation level (READ UNCOMMITTED).

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 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, about 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 speed is faster instead.

F, about the temporary table produces the choice of using SELECT INTO and create TABLE + INSERT into, we have tested, in general, select into will be much faster than the CREATE TABLE + INSERT into method, but select Into locks tempdb's system tables sysobjects, sysindexes, syscolumns, and is prone to blocking other processes in a multiuser concurrency environment, so my advice is to use CREATE TABLE + INSERT into in a concurrent system as much as possible. , while a single statement with a large amount of data is used, select INTO is used.

G, note the collation, the temporary table 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, the use of sub-query

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_name
From publishers
WHERE pub_id not in
(SELECT pub_id
From TITLES
WHERE TYPE = ' business ')
Can be changed to write:

SELECT A.pub_name
From publishers A left JOIN TITLES B
On b.type = "Business" and
A.pub_id=b. pub_id
WHERE b.pub_id is NULL


SELECT TITLE
From TITLES
WHERE not EXISTS
(SELECT title_id
From SALES
WHERE title_id = TITLES. title_id)
Can be changed to write:

SELECT TITLE
From the TITLES left JOIN SALES
On SALES. title_id = TITLES. title_id
WHERE SALES. title_id is NULL
B, if guaranteed subqueries are not duplicated, in, exists related subqueries can be replaced with inner JOIN. Like what:

SELECT pub_name
From publishers
WHERE pub_id in
(SELECT pub_id
From TITLES
WHERE TYPE = ' business ')
Can be changed to write:

SELECT DISTINCT A.pub_name
From publishers A INNER JOIN TITLES B
On b.type = "Business" and
A.pub_id=b. pub_id

The related subqueries of C, in are replaced by exists, for example

SELECT pub_name
From publishers
WHERE pub_id in
(SELECT pub_id
From TITLES
WHERE TYPE = ' business ')
You can use the following statement instead:

SELECT pub_name
From publishers
WHERE EXISTS
(SELECT 1
From 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 @NAME VARCHAR (20)
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 (1000)
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 (
Number VARCHAR (30),
Name VARCHAR (100),
Main 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,
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 @ No. VARCHAR (30),
@ Unit INT,
@ parameter NUMERIC (18,4),

DECLARE CUR CURSOR for
SELECT material number, unit from inbound table WHERE unit <>0
OPEN CUR
FETCH NEXT from CUR to @, @ unit
While @ @FETCH_STATUS <>-1
BEGIN
IF @ Unit =1
BEGIN
SET @ parameter = (SELECT Unit 1 parameter from material table WHERE material number [email protected]
UPDATE Inbound Table SET quantity = Quantity *@ parameter, number of damage = number of Damage *@ 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, number of damage = number of Damage *@ parameter, unit =1 WHERE Current of CUR
END
FETCH NEXT from CUR to @, @ 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 parameters
When 2 then A. Quantity *b. Unit 2 parameters
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 storage table A, material table B
WHERE 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 INT
DECLARE @ ordinal INT
UPDATE #T SET
@ ordinal =case When [email protected] Then @ sequence number +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, dynamic cursor query recordset data if modified, the cursor is automatically refreshed, which makes the dynamic cursor uncertain, because in a multiuser environment, if another process or itself changes the record, it is possible to refresh the cursor's recordset.

7. Use the index 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 the 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 field < ' 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 for 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, pay attention to the wording of the connection conditions

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, connection conditions use clustered index as far as possible

C, note the difference between the on-part condition and the where part condition



9. Other areas needing attention

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 as far as possible with a large amount of data database testing, it is best to use actual data testing.

C, each SQL statement 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 and demerits 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, to 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, Generally CLUSTERED index seek, index seek, CLUSTERED index Scan, index Scan, TABLE scan, and so on, which appear in the scan statement there is room for oil painting. 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, using Profiler to track the operation of the system, suspicious tracking to execute statements, as well as the time spent, CPU usage and I/O data, so as to analyze the efficiency of the statement.

C, you can use the Windows System performance detector, 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 SQL Server maintenance plan to set a timed index rebuild, index reconstruction is to improve the performance of the index.

B, you can use the statement update STATISTICS or SQL Server maintenance plan to set the timing of index statistics updates, the purpose is to make statistics more reflective of the actual situation, so that the optimizer to choose 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 understanding of some of the statements in the online materials

1, "should try to avoid the NULL value of the field in the Where clause to judge, otherwise it will cause the engine to abandon the use of the index for a full table scan, 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, "should try to avoid using the! = or <> operator in the WHERE clause, otherwise the engine discards the use of the index for a full table scan. ”

Personal opinion: After testing the,<> can also be found with index seek.

3, "should try to avoid using OR in the WHERE clause to join the condition, otherwise it will cause the engine to abandon the use of 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, "as far as possible to use Varchar/nvarchar instead of Char/nchar, because the first variable long field storage space is small, you can save storage space, and secondly for the query, in a relatively small field in the search efficiency 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, about the order of joining the table or the order of conditions, after testing, in SQL SERVER, these orders are not affect performance, these claims may be valid for Oracle.

MSSQL Performance Optimization