SQL Server database query optimization 50 tips (Part 2)

26. Appropriate indexes can be used for Min () and max.
27. There is a principle in the database that the code is closer to the data, the better, so default is the preferred one, which is rules, triggers,

Constraint (constraints such as external key checkunique ......, The maximum length of the data type is a constraint), procedure.

Low maintenance work, high programming quality, and fast execution speed.

28. 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.

No ). In this way, the application first converts the binary value to a string (twice the size of the string), and then converts it after the server receives the character.

The stored procedure does not have these actions: method:

Create procedure p_insert as insert into table (fimage) values (@ image)

Call this stored procedure at the front end to pass in binary parameters, which significantly improves the processing speed.

29. Between is faster in some cases than in, and between can locate the range based on the index faster. Visible with the Query Optimizer

Difference.

Select * From chineseresume where title in ('male', 'female ') Select * From chineseresume where

Between

The male and female are the same. Because in may be more than once, it may be slower sometimes.

30. If it is necessary to create an index for a global or local temporary table, it may increase the speed, but not necessarily because the index

It consumes a lot of resources. Its creation is the same as that of the actual table.

31. Do not create useless things, such as wasting resources when generating reports. Use it only when necessary.

32. The or clause can be divided into multiple queries and connected to multiple queries through Union. Their speed is only the same as whether to use the index

If Union indexes are required for queries, Union all is more efficient. If multiple or statements are not used, rewrite them to union.

Then try to match the index. Whether or not indexes are used in a key issue.

33. Use a view as little as possible, which is less efficient. Operations on a view are slower than operations on a table. You can replace it with stored procedure.

In particular, do not use nested views. nested views increase the difficulty of searching for original data. Let's look at the nature of the View: it is stored on the server.

The optimized SQL query plan has been generated. When retrieving data from a single table, do not use a view pointing to multiple tables.

You can retrieve or read the view that only contains the table. Otherwise, unnecessary overhead is added and the query is disturbed. To speed up the View query,

MSSQL adds the View index function.

34. Do not use distinct or order by unless necessary. These actions can be executed on the client. They added additional open

Sales. This is the same as Union and Union all.

Select top 20 Ad. companyName, comid, position, AD. referenceid, worklocation,
Convert (varchar (10), Ad. postdate, 120) as postdate1, workyear, degreedescription from
Jobcn_query.dbo.companyad_query ad where referenceid in ('jcnad00329667 ', 'jcnad132168'

, 'Jcnad00337748', 'jcnad00338345 ',
'Jcnad00333138 ', 'jcnad00303570', 'jcnad00303569 ',
'Jcnad00303568 ', 'jcnad00306698', 'jcnad00231935 ', 'jcnad00231933 ',
'Jcnad00254567', 'jcnad00254585 ', 'jcnad00254608 ',
'Jcnad00254607 ', 'jcnad00258524', 'jcnad00332379', 'jcnad00268618 ',
'Jcnad00279196 ', 'jcnad00268613') order by postdate DESC

35. In the post-in nominal value list, place the most frequent values at the beginning and the least at the end to reduce the number of judgments.

Number.

36. When select into is used, it locks the system table (sysobjects, sysindexes, etc.) and blocks access from other connections. Create

When creating a temporary table, use the show statement instead

Select into. Drop table t_lxh begin Tran select * into t_lxh from chineseresume
Where name = 'xyz' -- commit

In another connection, select * From sysobjects shows that select into locks the system table and Create Table locks the system.

Table (whether it is a temporary table or a system table ). So never use it in things! In this case, use the actual

Table or temporary table variable.

37. Generally, redundant rows can be removed before group by having clauses, so try not to use them for row removal.

. Their execution sequence should be optimal as follows: Select WHERE clause Selects all appropriate rows, and group by is used to group statistics rows,

The having clause is used to remove unnecessary groups. In this way, the consumption of group by having is small and the query speed is fast. Grouping and

Having consumes a lot of resources. If the purpose of group by is not to include computing, but to group, it is faster to use distinct.

38. Updating multiple records at a time is faster than updating multiple records at a time, that is, batch processing is good: 3

39. Use less temporary tables and replace them with result sets and table variables. Table variables are better than temporary tables.

40. In SQL2000, calculated fields can be indexed. The following conditions must be met:

A. The expression of calculated fields is definite.

B. Data Types of text, ntext, and image cannot be used.

C. The following options must be prepared: ansi_nulls = on, ansi_paddings = on ,.......

41. Try to put data processing on the server to reduce network overhead, such as using stored procedures. The stored procedure is compiled,

SQL statements that have been optimized and organized into an execution plan and stored in the database are a collection of control flow languages. The speed is certainly fast.

. You can use a temporary stored procedure to execute dynamic SQL statements repeatedly. This process (temporary table) is stored in tempdb. Previously

Server does not support complex mathematical computing, so it has to put this job on another layer to increase network overhead. SQL2000 support

With udfs, complex mathematical computation is now supported. The return value of a function is not too large, which is costly. User-Defined Functions are like cursors

Execution consumes a large amount of resources. If a large result is returned, the stored procedure is used.

42. Do not use the same function repeatedly in one sentence, waste resources, and put the result in a variable before calling it faster.

43. The efficiency of select count (*) is low. Try to change the method while exists is fast. Note the difference: Select count

(Field of null) from table and select count (field of not null) from Table return values are different !!!

44. When the server has enough memory, the number of preparation threads = maximum number of connections + 5 to maximize the efficiency; otherwise, the configuration will be used.

Number of threads <maximum number of connections enable the SQL Server thread pool to solve, if the number is still = maximum number of connections + 5, serious damage Server

Server performance.

45. Access your table in a certain order. If you lock table A and Table B

Lock them in this order. If you first lock table B in a stored procedure and then lock Table A, this may cause

Deadlocks. If the lock sequence is not designed in detail in advance, it is difficult to find deadlocks.

46. Monitor the load of the corresponding hardware through SQL server performance monitor memory: Page faults/sec counters, as shown in

If this value increases occasionally, it indicates that there were threads competing for memory. If it continues high, memory may be the bottleneck.

Process:

1.% DPC time indicates the percentage of the processor used to receive and provide services during the sample interval in the deferred program call (DPC. (DPC positive

The running interval is lower than the standard interval priority ). Because DPC is executed in privileged mode, the percentage of DPC time is of privileged time.

A part of the ratio. These time values are calculated separately and are not part of the total number of interval values. The total number shows the percentage of instance time

Average busy hours.

2. If the value of % processor time counter exceeds 95%, the bottleneck is the CPU. You can consider adding a processing item.

Or a faster processor.

3.% privileged time indicates the percentage of idle processor time used in privileged mode. (The privileged mode is for operating system components and operations.

A processing mode designed for hardware drivers. It allows direct access to hardware and all memory. Another mode is the user mode, which is

A limited processing mode designed for applications, Environment subsystems, and integer subsystems. The operating system converts application threads to privileges.

Mode to access operating system services ). The privileged time % includes the time when the service is interrupted and the DPC is provided. High privileged time ratio may be caused

A large number of failed devices are generated at intervals. This counter displays the average busy hours as part of the sample time.

4.% USER time indicates CPU-consuming database operations, such as sorting and executing Aggregate functions. If the value is high,

You can consider increasing the index, try to use simple table join, horizontally split large tables, and other methods to reduce this value. Physical Disk: curretn Disk

The value of the queue length counter must not exceed 1.5 of the number of disks ~ 2 times. To improve performance, you can add disks. Sqlserver: cache hit

Ratio counter the higher the value, the better. If the duration is lower than 80%, consider increasing the memory. Note that the value of this parameter is after being started from SQL Server,

So after running for a period of time, this value cannot reflect the current value of the system.

47. Analyze select emp_name form employee where salary> 3000 in this statement, if salary is of the float type,

The optimizer optimizes it to convert (float, 3000). Because 3000 is an integer, we should use 3000.0 during programming instead of waiting for it.

Allows the DBMS to convert data at runtime. Conversion of the same character and integer data.

48. query Association and write order

Select a. personmemberid, * From chineseresume A, personmember B where personmemberid
= B. referenceid and A. personmemberid = 'jcnprh1_1' (A = B, B = 'number ')
Select a. personmemberid, * From chineseresume A, personmember B where a. personmemberid
= B. referenceid and A. personmemberid = 'jcnprh1_1 'and B. referenceid = 'jcnprh1_1 '(

A = B, B = 'number', A = 'number ')
Select a. personmemberid, * From chineseresume A, personmember B where B. referenceid
= 'Jcnprh00001' and A. personmemberid = 'jcnprh00001' (B = 'number', A = 'number ')

49,

(1) If no owner code is entered, then code1 = 0 code2 = 9999 else code1 = code2 = owner code end if execution

The row SQL statement is: Select owner name from p2000 where owner code >=: code1 and owner code <=: code2

(2) If no owner code is entered, then select owner name from p2000 else code = owner code select owner

Code from p2000 where owner code =: code end if the first method only uses one SQL statement, and the second method uses two

SQL statement. When no owner code is entered, the second method is obviously more efficient than the first method because it has no restrictions.

When the owner code is entered, the second method is still more efficient than the first method, not only because it lacks a restriction, but also because the equality operation is the fastest

Query operation. Do not worry about writing programs.

50. About the new method of querying pages in jobcn (as shown below), use the performance optimizer to analyze performance bottlenecks.

In terms of speed, the following methods are effectively optimized. If it is on the CPU or memory, it is better to use the current method. Distinguish the following methods:

The smaller the index, the better.

Begin
Declare @ local_variable table (FID int identity (1, 1), referenceid varchar (20 ))
Insert into @ local_variable (referenceid)
Select top 100000 referenceid from chineseresume order by referenceid
Select * From @ local_variable where FID> 40 and FID <= 60
End

And

Begin
Declare @ local_variable table (FID int identity (1, 1), referenceid varchar (20 ))
Insert into @ local_variable (referenceid)
Select top 100000 referenceid from chineseresume order by updatedate
Select * From @ local_variable where FID> 40 and FID <= 60
End

Different

Begin
Create Table # temp (FID int identity (1, 1), referenceid varchar (20 ))
Insert into # temp (referenceid)
Select top 100000 referenceid from chineseresume order by updatedate
Select * from # temp where FID> 40 and FID <= 60 drop table # temp
End