How can I speed up SQL database queries?

1. upgrade hardware 2. Create indexes based on query conditions, optimize indexes, optimize access methods, and limit the data volume of result sets. 3. Expand Server Memory 4. Increase server CPU count 5. Do not set Automatic database growth for large databases, it will reduce the server performance. 6. Use the Where clause in the Select statement to limit the number of returned rows to avoid table

1. upgrade hardware 2. Create indexes based on query conditions, optimize indexes, optimize access methods, and limit the data volume of result sets. 3. Expand Server Memory 4. Increase server CPU count 5. Do not set Automatic database growth for large databases, it will reduce the server performance. 6. Use the Where clause in the Select statement to limit the number of returned rows to avoid table

1. upgrade hardware

2. Create an index based on the query conditions, optimize the index, optimize the access mode, and limit the data volume of the result set.

3. Expand the server memory

4. Increase the number of server CPUs

5. Do not set Automatic database growth for large databases, which will reduce server performance.

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

7. Do not return unwanted rows or columns during Query

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

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

10. 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 as follows:

Select Where clause Selects all appropriate rows, Group By is used to Group statistical rows, and Having clause is used to remove redundant groups. In this way, the consumption of Group By Having is small, and queries are fast. Grouping and Having large data rows 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.

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

Optimization of ms SQL Server Query

There are many reasons for slow query speed.

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 support 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 search feature and intend to run the Microsoft Search Service for full-text indexing and query, consider: set the virtual memory size to at least three times the physical memory installed on the computer. Configure the SQL Server max server memory Server configuration option to 1.5 times the physical memory (half 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 tasks and can be run on the processor. For example, if the sort, connection, scan, and group by statements of delayed queries are executed simultaneously, SQL 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. However, UPDATE, INSERT, and DELETE operations 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 % 'when the index like' % a' is used and like '% a %' is not used for the query, the query time is proportional to the total length of the field value, so the CHAR type cannot be used, but VARCHAR. 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 mechanism of forming Database Server consortium through partition data can expand a group of servers to support the processing needs of large multi-layer Web sites. 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 run on any 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. The data location is transparent to the application.

11. Rebuild the index dbcc reindex, dbcc indexdefrag, shrink data and log dbcc shrinkdb, and dbcc shrinkfile. set automatic log shrinking. for large databases, do not set Automatic database growth, which will reduce the 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 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, such as begin tran exec (@ s) commit trans, or write dynamic SQL into functions or stored procedures.

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, using temporary tables, Table variables, subqueries, and Case statements. 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 and is also the default method. You can extract arbitrary rows randomly 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.