sql query optimization techniques

Analysis of slow SQL queries by a user, reason and description of optimization problems A user reports that the execution time of the previous SQL statement is too slow to accept. The SQL statement looks simple (the table name and field name are modified in this description): SELECT count (*) FROMaJOINb ONa.'s '= B.'s

the cold data should be only a cold database to initiate these 20 data requests. At this point the R table data needs to be compared, only part of the cold data.6. Nonlocality = Cold storage: When you query and use hot data, move hot data that is no longer used for a period of time to the cold storage.7. Cold storage = nonlocality: Query cold storage, the results of this

the merge do not contain duplicate data, use Union all, as shown below:Select * From test_union1Union allSelect * From test_union2 There are two basic rules for query result sets using union: 1. The columns and columns in all queries must be in the same order. 2. Data type must be compatible Database Query Optimization: 1. To optimize the

Tag: SEL Access SQL statement through Date diff Union All database table variableIn a TENS database search, how to improve the efficiency of the query?1 ) Database design aspects: A, to optimize the query, should try to avoid full table scan, first of all should consider the where and order by the columns involved in the index.b, you should try to avoid the null

% 870 (4688 lines) Stored Procedures Proc: @ProductID = 870 1305 17 928 17 Is KB 26% 870 (4688 lines) In-stored procedure declaration Proc:where P.productid [email protected] 1305 18 984 18 Is KB 24% 897 (2 lines) Instant Query where P.productid =897 20 10 792 10 Is KB 13%

Tags: store style col MySQL share picture lob Div using indexfirst, MySQL's slow query log opening and storage 1, check whether the SQL records that do not use the index to the slow query log, view the log_queries_not_using_indexes variable; like "Log_queries_%" Results: If the value of the query is off, it indicat

after each statement that executes the stored procedure and trigger. 29. Try to avoid large transaction operation and improve the system concurrency ability. 30. Try to avoid the return of large data to the client, if the amount of data is too large, should consider whether the corresponding demand is reasonable. Besides, friends, if you have to use a query like '%a% ' It is recommended that you do a full-text index of the table, originally like

tables are explicitly deleted at the end of the stored procedure, TRUNCATE table first, and then drop table, which avoids longer locking of the system tables. 25, avoid using cursors as much as possible, because cursors are inefficient and should be considered for overwriting if the cursor is manipulating more than 10,000 rows of data. 26, you should look for a set-based solution to solve the problem before using a cursor-based method or a temporary table method, which is often more efficient t

by Cust.nameThen query in the view in the following way:SELECT * from V_cust_rcvlbesWHERE postcode> "98000"The rows in the view are less than the rows in the primary table, and the physical order is the required order, reducing the disk I/O, so checkThe workload can be significantly reduced.23, you can use distinct without GROUP bySELECT OrderID from Details WHERE UnitPrice > Ten GROUP by OrderIDCan be changed to:SELECT DISTINCT OrderID from Details

Tags: server ASI How much data query size optimization way up and down no1. When you need to query all the data in the tableCompare the following three types of query statements:Suppose the data table is BasicMsg20170401, there are 17 columns, and the number of data bars is 2.84 million(1) SELECT * from BasicMsg2017040

SQL Server multi-Table query optimization solution highlights, sqlserver The multi-Table query optimization solution of SQL Server is what we will introduce in this article. We provide the opt