SQL Server database optimization 50 Methods

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. Add servers
Number of 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.
. 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. However, update, insert,
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' %'
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 form of database through partition data
The server consortium mechanism 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 Member
Run on the 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.
　　
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. T-SQL has a lot of emphasis on writing, the following lists common points: first
First, the DBMS processes the query plan 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 change
Data volume, 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 and 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. 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, unlocked, holdlock, shared sharing, update, updlock, error update, tablockx, error, unlocked
Unlocked update other unlocked update * 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 ). Because in this way, the application first converts binary
The value is converted to a string (twice the size of the string). After receiving the character, the server converts it to a binary value. 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. Use the query optimizer to see the difference. Select *
From chineseresume where title in ('male', 'female ') Select * From chineseresume
Where between '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 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)
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 is used
Into, 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, 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 the database.
The SQL statement in is 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
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
The returned 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, DPC
The percentage of time is part of the percentage of privileged time. 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 ). Privileged hours
The % between them 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 '(
= 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 = 'jcnprh1_1 'and A. personmemberid = 'jcnprh1_1' (B =
'Number', A = 'number ')
　　
49,
　　
(1) If no owner code is entered, then code1 = 0 code2 = 9999 else code1 = code2 = owner code end if
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
　　
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

Appendix: storage process writing experience and optimization measures from: Web Teaching Network

1. Suitable for readers: database developers, who have a large amount of data in the database and who are interested in optimizing the SP (stored procedure.

II. Introduction: complex business logic and database operations are often encountered during database development. In this case, SP is used to encapsulate database operations. If there are many SP projects, write
There are no certain specifications, which will affect the difficulty of system maintenance and the difficulty of understanding the big SP logic. In addition, if the database has a large amount of data or the project has high performance requirements on the SP, you may encounter optimization problems. Otherwise
The speed may be slow. After personal experience, an optimized SP is several hundred times more efficient than an optimized sp.

Iii. content:

1. If developers use tables or views of other databases, they must create a view in the current database to perform cross-database operations. It is best not to directly use "Databse. DBO. table_name ", because sp_depends cannot display the cross-database table or view used by the SP, it is not convenient to verify.

2. Before submitting the SP, the developer must have used set showplan on to analyze the query plan and perform its own query optimization check.

3. High program running efficiency and application optimization. Pay attention to the following points during SP writing:

A) SQL usage specifications:

I. Avoid large transaction operations as much as possible. Use the holdlock clause with caution to improve the system concurrency capability.

Ii. Try to avoid repeated accesses to the same or several tables, especially tables with large data volumes. You can consider extracting data to a temporary table based on the conditions and then connecting it.

III. avoid using a cursor whenever possible because the cursor is inefficient. If the cursor operation contains more than 10 thousand rows of data, it should be rewritten. If the cursor is used, try to avoid table join operations in the cursor loop.

IV. note that when writing where statements, the order of statements must be taken into account. The order before and after condition clauses should be determined based on the index order and range size, and the field order should be consistent with the index order as much as possible, the range is from large to small.

V. do not perform functions, arithmetic operations, or other expression operations on the left side of "=" in the WHERE clause. Otherwise, the system may not be able to correctly use the index.

VI. use exists instead of select count (1) to determine whether a record exists. The count function is used only when all the rows in the statistical table are used, and count (1) is more efficient than count.

VII. Try to use "> =" instead of "> ".

VIII. Note the replacement between the or clause and the union clause.

IX. Pay attention to the data types connected between tables to avoid the connection between different types of data.

X. Pay attention to the relationship between parameters and data types in stored procedures.

XI. Pay attention to the data volume of insert and update operations to prevent conflicts with other applications. If the data volume exceeds 200 data pages (400 Kb), the system will update the lock and the page lock will be upgraded to the table lock.

B) Specification for indexing:

I. You should consider creating indexes in combination with applications. We recommend that you create a large OLTP table with no more than six indexes.

Ii. Try to use the index field as the query condition, especially the clustered index. If necessary, you can use index index_name to forcibly specify the index.

Iii. Avoid performing table scan when querying large tables. If necessary, create an index.

IV. when using an index field as a condition, if the index is a joint index, you must use the first field in the index as the condition to ensure that the system uses the index, otherwise, the index will not be used.

V. Pay attention to index maintenance, rebuild indexes periodically, and recompile the stored procedure.

C) use of tempdb:

I. Avoid using distinct, order by, group by, having, join, and cumpute as much as possible, because these statements will increase the burden on tempdb.

Ii. Avoid frequent creation and deletion of temporary tables and reduce the consumption of system table resources.

III. when creating a temporary table, if a large amount of data is inserted at one time, you can use select into instead of create table to avoid logs and increase the speed. If the data volume is small, in order to ease the system table resources, we recommend that you first create table and then insert.

IV. if the temporary table has a large amount of data and requires an index, you should place the process of creating a temporary table and creating an index in a single sub-storage process, in this way, the system can use the index of the temporary table.

V. if a temporary table is used, you must explicitly delete all temporary tables at the end of the stored procedure. First truncate the table and then drop the table, so that the system table can be locked for a long time.

Vi. Use caution when connecting large temporary tables to other large tables to query and modify them, reducing the burden on the system table, because this operation will use the tempdb system table multiple times in one statement.

D) Reasonable algorithm usage:

Based on the SQL optimization technology and ASE mentioned above
The SQL Optimization content in the tuning manual, combined with practical application, uses a variety of algorithms for comparison to obtain the method that consumes the least resources and has the highest efficiency. Specific available ASE optimization command: Set
Statistics Io on, set statistics time on, set showplan on, etc.

 

URL: http://hi.baidu.com/djcm99/blog/item/173352ddf3e287de8d1029b7.html