steps to optimize sql query

following SQL statement selects 400 rows. SELECTSalesDate,SalesPersonIDFROMSalesWHEREProductID=112 First, let's figure out how to execute SQL statements in the database engine: 1. The sales table has a non-focused index in the productid column. Therefore, first query the non-focused index tree and find the entry containing productid = 112. 2. The

to force the table to join in the order in which it appears in the FROM clause First_rows_n Tips Oracle has two tips based on cost optimization, one is First_rows_n, and the other is all_rows. First_rows mode will be returned to the customer as soon as the data is queried Household end. While the all_rows pattern is designed to optimize resources, it needs to wait until all results have been completed before returning data to the client.

On the forum, I often complained that SQL Server is too memory-consuming. Here I will give a brief introduction to memory-related tuning knowledge based on experience. First, describe the memory used by SQL Server. The memory occupied by SQL Server is mainly composed of three parts:Data Cache (DATa buffer),Procedure Cache), AndSQL Server engine Program. The cache

the amount of data (size), the more important it is to increase I/O. 2. Vertical and horizontal partition table, reduce the size of the table (Sp_spaceuse) 3. Upgrading hardware 4, according to the query criteria, index, optimize the index, optimize access mode, limit the data volume of the result set. Note that the fill factor is appropriate (preferably using t

executed in SQL server. They are worried that the SQL statements they write will be misunderstood by SQL SERVER. For example: Select * from table1 where name = 'hangsan' and tID> 10000 And execution: Select * from table1 where tID> 10000 and name = 'hangsan' Some people do not know whether the execution efficiency of the preceding two statements is the same, bec

First, describe the memory used by SQL Server. The memory occupied by SQL Server is mainly composed of three parts: Data Buffer, Procedure Cache, and SQL Server engine program. The cache occupied by SQL Server engine programs is generally relatively small, so the main focus of memory optimization is data cache and exec

First, describe the memory used by SQL Server. The memory occupied by SQL Server is mainly composed of three parts: Data Buffer, Procedure Cache, and SQL Server engine program. The cache occupied by SQL Server engine programs is generally relatively small, so the main focus of memory optimization is data cache and exec

Label:1, first to understand what is called the implementation plan? An execution plan is a query scheme that the database makes based on the statistics of SQL statements and related tables, which are generated automatically by the query optimizer, such as an SQL statement that searches for 1 records from a 100,000-rec

Label:Parameter sniffing is a function of generating an execution plan based on the parameters passed in when SQL Server creates the execution plan for a stored procedure, in layman's terms, the execution plan is generated based on the first parameter for stored procedure, but stored The execution plan generated by the first parameter of the procedure is not necessarily optimal, and when the execution plan is reused for subsequent arguments, the origi

Mysql tuning three steps (slow query and explain profile) often encounter some SQL problems in performance tests. In fact, the most problems encountered in performance tests over the past few years are databases, either high I/O or high cpu, Data Optimization plays an important role in performance testing, below I will introduce some of the three tools that are f

optimization is to split the SQL, separating the sort operation from the operation of querying all information. The first statement: query matching data, only need to query g.id canSELECT DISTINCT g.id from gm_game G left join gm_cp cp in cp.id = g.cp_id and cp.deleted = 0 left join gm_category C on C . id = g.category_id and c.deleted = 0 left JOIN gm_type t in

subsequent execution does not need to be re-compiled, and the final execution10000The total number of queries is only used1Second. 2 , Check and analyze SQL Server Execute the execution plan in the cache Through the above introduction, we can see thatSQLThe memory used by the cache. You know.SQL ServerThe content in the execution cache is mainly a varietySQLStatement execution plan. To optimize the c

Tags: io using ar strong data SP on C logFirst, prefaceMany database system performance is not ideal because the systems are not overall optimized, there are a lot of low-performing SQL statements. The primary reason for the poor performance of such SQL statements is the lack of efficient indexing. No index In addition to the statement itself to run slow, but also caused a large number of disk read and writ

High-performance SQL statements will play a very important role in the operation of the software, the following small series of recently compiled SQL statements to optimize the data to share. First: Select the most efficient table name order (valid only in rule-based seo/' target= ' _blank ' > Optimizer): The ORACLE parser processes the table name in the FROM cl

most important condition for an index to be used for a query is that the index keyword must be used in the query condition. If multiple-column indexes are used, only the query condition uses multiple-column indexes. SQL INDEX OPTIMIZATION In data, many friends like to use MySQLSQL to

system monitor may display high CPU usage, but it does not identify the cause; you can use the system monitor to monitor multiple computers from one location, which can reduce resource usage for each system to be monitored, we also provided you with a method to compare performance statistics of different computers. Extended event and analysis service dynamic management view An extended Event (XEvents) is an event infrastructure that monitors the system for high scalability and lightweight perfo

Tags: des blog http io ar using Java for SP============ Recommended ============csdn:http://blog.csdn.net/e3002/article/details/1817941= = = How to optimize SQL statements = =(1) Select the most efficient table name order (valid only in the rule-based optimizer): Oracle's parser processes the table name from the FROM clause in a right-to-left order, written in the last table in the FROM clause (base table d

required to produce this result. The physical processing is how to get this result, so the performance issue will be considered at this time. Next we will discuss how to execute this physical processing. When a query arrives at the database engine, the database engine needs to execute the query plan for this query. There are two situations at this time, one poss

Label:Oracle-based application system many performance problems are caused by poor application SQL performance, so, SQL performance optimization is very important, analysis and optimization of SQL performance we generally look at the SQL implementation plan, this article on how to understand the implementation plan, An

On-Line Analytical Processing (OLAP) system is the main application of Data Warehouse system, specially designed to support complex analysis operations, focusing on decision support for decision-makers and senior managers, and can quickly and flexibly carry out complex query processing of large amount of data according to the requirements of analysts. And in an intuitive and understandable form of the query