Reasons for slow database query speed in the database

There are many reasons for slow query speed. The following are common causes:

1. No index or no index is used (this is the most common problem of slow query and is a defect in programming)

2. Low I/O throughput, resulting in a bottleneck effect.

3. the query is not optimized because no computing column is created.

4. Insufficient memory

5. slow network speed

6. The queried data volume is too large (you can use multiple queries to reduce the data volume in other ways)

7. Lock or deadlock (this is also the most common problem of slow query and is a defect in programming)

8. sp_lock and sp_who are active users. The reason is that they read and write competing resources.

9. Unnecessary rows and columns are returned.

10. The query statement is not good and is not optimized.

You can optimize the query by using the following methods:

1. Place data, logs, and indexes on different I/O devices to increase the reading speed. In the past, tempdb can be placed on raid0, which is not supported by SQL2000. The larger the data size (size), the more important it is to increase I/O.

2. vertically and horizontally split the table to reduce the table size (sp_spaceuse)

3. upgrade hardware

4. Create an index based on the query conditions, optimize the index, optimize the access mode, and limit the data volume of the result set. Note that the fill factor should be appropriate (preferably the default value 0 ). The index should be as small as possible. Use a column with a small number of bytes to create an index (refer to the index creation). Do not create a single index for fields with a limited number of values, such as gender fields.

5. Improve network speed;

6. Expand the server memory, Windows 2000 and SQL Server
2000 supports 4-8 GB memory. Configure virtual memory: the virtual memory size should be configured based on services running concurrently on the computer. Run Microsoft SQL server?
2000, you can consider setting the virtual memory size to 1.5 times the physical memory installed on your computer. If you have installed the full-text retrieval feature and intend to run Microsoft
Search Service for full-text indexing and query. Consider setting the virtual memory size to at least three times the physical memory installed on the computer. Set SQL Server Max
The server memory server configuration option is 1.5 times the physical memory (half of the virtual memory size ).

7. Increase the number of server CPUs. However, you must understand that resources such as memory are more required for concurrent processing of serial processing. Whether to use parallelism or serial travel is automatically evaluated and selected by MSSQL. A single task is divided into multiple
To run on the processor. For example, delayed query sorting, connection, scanning, and group by statement execution, SQL
The server determines the optimal parallel level based on the system load. complex queries that consume a large amount of CPU are most suitable for parallel processing. But update operations
Update, insert, and delete cannot be processed in parallel.

8. If you use like for query, you cannot simply use index, but the full-text index consumes space. Like 'a % 'use index like
'% A' like' % A % 'is not used for Indexing'
The query time is proportional to the total length of the field value. Therefore, the char type cannot be used, but varchar is not used. Create a full-text index for a long field value.

9. Separate DB server and application server; Separate OLTP and OLAP

10. Distributed partition view can be used to implement Database Server consortium. A consortium is a group of separately managed servers, but they collaborate to share the processing load of the system. This number is formed by partition data.
The mechanism of Database Server consortium can expand a group of servers to support large multi-layer Web
Site processing needs. For more information, see designing a database federation server. (Refer to the SQL Help File 'partition view ')

A. before implementing the partition view, a horizontal partition table must be created.

B. After creating a member table, define a distributed partition view on each Member Server, and each view has the same name. In this way, queries that reference the view name of a distributed partition can be found in any
Run on the member server. System operations are the same as if each member server has a copy of the original table, but in fact each server has only one member table and a distributed partition view. Data Location for application
The order is transparent.

11. Rebuild the index DBCC reindex, DBCC indexdefrag, shrink data and log DBCC shrinkdb, DBCC
Shrinkfile. Set automatic log shrinking. Do not set Automatic database growth for large databases, which will reduce server performance.
The writing of T-SQL is very important. The following lists common points: first, the process of DBMS processing the query plan is as follows:

1. query statement lexical and syntax check

2. submit the statement to the query optimizer of the DBMS.

3. optimizer performs algebra optimization and access path optimization

4. A query plan is generated by the Pre-compilation module.

5. Then, submit it to the system for processing and execution at the appropriate time.

6. Finally, return the execution result to the user. Next, let's take a look at the data storage structure of SQL SERVER: the size of a page is 8 K (8060) bytes, eight pages are a disk area and are stored in the B-tree format.

12. Difference Between commit and rollback: Roll back all things. Commit: Submit the current transaction.
There is no need to write things in dynamic SQL. If you want to write things, write them out, for example, begin Tran exec (@ s) Commit trans or
It is written as a function or stored procedure.

13. Use the WHERE clause in the SELECT statement to limit the number of returned rows to avoid table scanning. If unnecessary data is returned, the server's I/O resources are wasted, this increases the burden on the network and reduces performance. If the table is large, the table is locked during the table scan and other connections are prohibited from accessing the table. The consequence is serious.

14. SQL statement comments have no impact on execution

15. Try not to use the cursor. It occupies a large amount of resources. If you need row-by-row execution, try to use non-cursor technology, such as: loop on the client, use temporary tables, table
Variable, subquery, case statement, and so on. The cursor can be classified according to the extraction options it supports: only the rows must be extracted from the first row to the last row. Fetch
Next is the only allowed extraction operation, which is also the default method. Rollability
Arbitrary rows can be randomly extracted anywhere in the cursor. The cursor technology becomes very powerful in SQL2000, and its purpose is to support loops. There are four concurrent options
Read_only: update cannot be located through the cursor, and there is no lock in the rows that make up the result set. Optimistic
Values: Optimistic Concurrency Control is a standard part of transaction control theory. Optimistic Concurrency control is used in this situation, that is, in the interval between opening the cursor and updating the row, there is only a small chance for the second user to update
A row. When a cursor is opened with this option, there is no lock to control the rows, which will help maximize its processing capability. If you try to modify a row, the current value of the row is obtained
Compare the value. If any value changes, the server will know that the other person has updated the row and will return an error. If the value is the same, the server executes the modification.
Select this concurrency option optimistic with row
Versioning: this optimistic concurrency control option is based on Row version control. Use row version control. The table must have a version identifier, which can be used by the server to determine whether the row has a read cursor.
Changed. In SQL Server, the performance is determined by the timestamp
The data type is provided. It is a binary number that indicates the relative sequence of changes in the database. Each database has a global current timestamp value: @ dbts. Each time you change
When running the timestamp column, SQL Server first stores the current @ dbts value in the timestamp column, and then adds the value of @ dbts. If a table has
Timestamp
Column, the timestamp is recorded at the row level. The server can compare the current timestamp value of a row with the timestamp value stored during the last extraction to determine whether the row has been updated. The server does not need to compare the values of all columns.
Compare the timestamp column. If the application has no Timestamp
Column tables require Optimistic Concurrency Based on Row version control, and the cursor is Optimistic Concurrency Control Based on numerical value by default. Scroll locks
This option implements pessimistic concurrency control. In pessimistic concurrency control, when the row of the database is read into the cursor result set, the application attempts to lock the row of the database. When a server cursor is used, when a row is read into the cursor
Put an update lock on. If the cursor is opened in the transaction, the update lock of the transaction will be kept until the transaction is committed or rolled back. When the next row is extracted, the cursor lock will be removed. If the cursor is opened outside the transaction, extract
A row is discarded. Therefore, whenever you need full pessimistic concurrency control, the cursor should be opened in the transaction. Update locks prevent any other tasks from obtaining update locks or exclusive locks.
New row. However, the update lock does not prevent the shared lock, so it does not prevent other tasks from reading rows, unless the second task also requires reading with the update lock. The scroll lock is based on the select defined in the cursor
The lock prompt specified in the statement. These cursor concurrency options can generate a scroll lock. The scroll lock is obtained on each row during extraction and is kept until the next extraction or cursor is closed. The first occurrence prevails. Next time, the server
Obtain the scroll lock for the Newly Extracted row and release the scroll lock for the last extracted row. The rolling lock is independent of the transaction lock and can be kept after a commit or rollback operation. If the option to close the cursor when submitting is off
The commit statement does not close any opened cursor, and the scroll lock is retained until it is committed to maintain isolation of the extracted data. The type of the obtained scroll lock depends on the concurrency options of the cursor and the cursor.
The lock prompt in the SELECT statement. Lock prompt read-only optimistic value optimistic row version control lock No prompt not locked update nolock not locked
Unlocked, unlocked, holdlock, shared sharing, update, updlock, error update, tablockx, error, unlocked, unlocked
Update other unlocked, unlocked, unlocked updates * The specified nolock prompt will make the table with the specified prompt read-only in the cursor.

16. Use profiler to track the query, obtain the time required for the query, and locate the SQL problem. Use the index optimizer to optimize the index.

17. Pay attention to the difference between Union and Union all. Good union all

18. Use distinct unless necessary. Similar to union, it slows down the query. Duplicate records are no problem in the query.

19. Do not return unwanted rows or columns during query.

20. Use sp_configure 'query Governor cost limit 'or set
Query_governor_cost_limit is used to limit the resources consumed by queries. When the resource consumed by the evaluation query exceeds the limit, the server automatically cancels the query and kills the query before the query.
Set locktime: Set the lock time.

21. Use select Top 100/10 percent to limit the number of rows returned by the user or set rowcount to limit the rows to be operated.

22. Before SQL2000, do not use the following words: "Is null", "<> ","! = ","!> ",
"! <"," Not "," not exists "," not in "," not like ", and" like
'% 100' ", because they do not leave the index and all are table scans. Do not add a function to the column name in the WHERE clause, such as convert and substring. If a function is required,
Create a calculated column and then create an index to replace it. You can also change the Syntax: Where substring (firstname,) = 'M' to where
Firstname like'm % '(index scan), be sure to separate the function from the column name. In addition, the index cannot be too large or too large. Not
In scans the table multiple times and uses exists, not exists, in, left Outer Join.
And exists is faster than in, especially the left join. The slowest operation is not. if the column value is null, its index does not work in the past. Now the 2000 optimizer can process it. Same
Is null, "not", "not exists", "not in" can optimize her, but "<>" cannot be optimized, and no index is used.

23. Use query analyzer to check the SQL statement query plan and evaluate and analyze whether the SQL statement is optimized. Generally, 20% of the Code occupies 80% of the resources, and our optimization focuses on these slow points.

24. If the in or query is not indexed, use the display statement to specify the index:

Select * From personmember (Index = ix_title) Where processid in ('male', 'female ')

25. Pre-calculate the results to be queried and place them in the table. Select the results when querying. This was the most important method before sql7.0. For example, hospital hospitalization fee calculation.

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.
High Order 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 the value using an embedded insert Statement (whether Java is used or not ). In this way, the application first converts the binary value to a string (twice the size of the string), and then converts it to a binary value after the server receives the character. stored procedures do 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. Use the query optimizer to see the difference.

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

Select * From chineseresume where title between 'male' and 'female'

Is 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 also 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
Connect multiple queries. Their speed is only related to whether an index is used. If a query requires a union index, the Union
The execution efficiency of all is higher. If multiple or statements do not use indexes, rewrite them to the form of union and try to match the indexes. Whether or not indexes are used in a key issue.

33. Use a view as little as possible, which is less efficient. Operations on views are slower than operations on tables. You can use stored
Procedure to replace her. In particular, do not use nested views. nested views increase the difficulty of searching for original data. Let's take a look at the essence of the View: it is generated after the optimization has been stored on the server.
The SQL statement of the query plan. When retrieving data from a single table, do not use a view pointing to multiple tables. Read data directly from the view that only contains the table. Otherwise, unnecessary overhead is added, query Result
To speed up 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 increase additional overhead. 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 value at the end to reduce the number of judgments.

36. When select into is used, it locks the system table (sysobjects, sysindexes, etc.) and blocks access from other connections. 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 can see that select into locks the system table and Create Table
It also locks the system table (whether it is a temporary table or a system table ). So never use it in things !!! In this case, use real tables or temporary table variables for temporary tables that are frequently used.

37. Generally
Having clauses can be used to remove redundant rows. Therefore, do not use them as much as possible. Their execution sequence should be as follows: select
Select all the appropriate rows, group by is used to group statistical rows, and having is used to remove redundant groups. In this way, group
Having is costly and fast to query. Grouping and having large data rows consumes a lot of resources. If the group
The purpose of by is not to include computation, but to group, so 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.

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, optimized, organized into an execution plan, and stored in
SQL statements in the database are a collection of control flow languages, and 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
SQL
Server does not support complex mathematical computing, so it has to put this job on another layer to increase network overhead. SQL2000 supports udfs and now supports complex mathematical computing and functions
The returned value is not too large, which is costly. User-Defined Functions consume a large amount of resources like the cursor. 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 = the maximum number of connections + 5, which can maximize the efficiency; otherwise, use the number of prepared threads <maximum number of connections to enable the SQL Server thread pool. If the number is equal to the maximum number of connections + 5, the performance of the server is seriously damaged.

45. Access your table in a certain order. If you lock table A and table B first, lock them in this order in all stored procedures. If you first lock table B in a stored procedure and then lock Table A, this may lead to a deadlock. 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. 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
Running is an interval with lower priority than the standard interval ). Because DPC is executed in privileged mode, the percentage of DPC time is the privileged time
Percentage. These time values are calculated separately and are not part of the total number of interval values. This total number shows the average busy hours as the percentage of instance time.

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

3.% privileged time
Percentage of idle processor time used in privileged mode. (Privileged mode is a processing mode designed for operating system components and operating hardware drivers. It allows direct access to hardware and all memory. Another model
It is a finite processing mode designed for applications, Environment subsystems, and integer subsystems. The operating system converts the application thread to the privileged mode to access the Operating System Service ).
The privileged time % includes disconnections and DPC
The time when the service is provided. The high privileged time ratio may be caused by a large number of failed device 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 and so on. If the value is very high, you can consider increasing the index and try to reduce the value by using simple table join and horizontal table segmentation methods. Physical Disk:
Curretn disk queue length counter this value should 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 parameter value is from the SQL
After the server is started, it keeps accumulating records. 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 make
Use 3000.0 instead of waiting for the DBMS to convert during 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 you run the SQL statement: 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 statements. 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. The new method for querying pages in jobcn is as follows: Use the performance optimizer to analyze performance bottlenecks. If I/O or network speed is used, the following method is effective. It is better to use the current method on the CPU or memory. Please differentiate the following methods, indicating that 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