sql optimization book

Label:ProfileReasons for optimization1. Impact of casing on SQL statements (ORACLE)2. Try to use (not) EXISTS override (not) in such an operation3. Use as few format conversions as possible in a massive query4. Query massive data Yes, you can use Optimizer HINTS (ORACLE)5. ORDER by and Gropu by6. Operations on Columns7. Avoid using or8. Replace or with in9. FromTen. WHEREOne. Is null or is not NULLAvoid using ' * ' in the SELECT clause13. Avoid using

Label:Optimize your goals1. Reduce the number of IOIo is always the most vulnerable to the database, which is determined by the responsibility of the database, most of the database operations more than 90% of the time is occupied by IO operations, reduce the number of IO is the first priority in SQL optimization, of course, is the most obvious optimization means.

Optimization | statement Writing executable SQL statements in a database application system can be implemented in many ways, but it is difficult to determine which is the best solution. In order to solve this problem, it is necessary to optimize SQL implementation. Simply put, the optimization of

, including average, minimum, maximum, Rows examined, and number of scanned Rows. It can also generate table reports and store analysis results. Here we will not introduce them one by one. Through these slow log analysis software, you can find that the slow query statement has completed most of the SQL optimization. Run the explain or desc command in MySQL to check the slow query statement. mysql> explain s

Label:Many people don't know how SQL statements are executed in SQL Server, and they worry that the SQL statements they write will be misunderstood by SQL Server. Like what: Name=> 10000 and execution: Name=' Zhangsan ' Some people do not know whether the executio

', ' female ', ' 2 ', ' Male ', ' not described ') gender_t from student;Results:EG2:Select Name,score,Case where score >80 then ' excellent 'When score>=60 and score When scoreEnd Evalutionfrom student;Results: Set the default value to place null as no score:Select Name,score,Case where score >80 then ' excellent 'When score>=60 and score When scoreElse ' no grades 'End Evalutionfrom student;Results:4) Note:1.case has two forms, where the case expression is then more efficient than the case-w

The earliest notation: withT as(SELECT Case whenCol1 is NULL ORCol1=N"' ThenCol2ElseCol1End asCode, Case whenCol1 is NULL ORCol1=N"' Then 1 Else 0 End asFlag fromYMWHEREcol_076between '2018-07-25' and '2018-08-03' andcol_478=N'xx' andcol_346 likeN'%dd%'), D as(SELECTCode,province,city fromAdds)SELECTProvince asProvince, City asCityCOUNT(1) Number of votes from (SELECTa.dr_250 asprovince,a.dr_251 asCity fromTINNER JOINTB asA ona.dr_203=T.codeWHERET.flag=0 UNION All SELECTD.province,d.c

Tags: Oracle database SQL optimization about SQL statement optimization methods Some are generic (such as avoiding select *); Some different database management systems differ (such as WHERE clause order); You then have to tune according to the actual environment, because even the same databases and tables,

The so-called SQL refers to Structured Query Language (Structured Query Language), which is currently the most widely used database language. It is used to deal with databases and obtain user data from databases. However, it is not a simple task to be familiar with SQL statements. Some statements are also troublesome to use. If we optimize SQL statements, it is m

Optimize your goals Reduce the number of IOIo is always the most vulnerable to the database, which is determined by the responsibility of the database, most of the database operations more than 90% of the time is occupied by IO operations, reduce the number of IO is the first priority in SQL optimization, of course, is the most obvious optimization means.

methods of emptying SQL Server log files from the 10thOne way: Empty the log.1. Open Query Analyzer, enter command dump TRANSACTION database name with NO_LOG2. Open Enterprise Manager again--right click on the database you want to compress--all tasks--shrink the database--Shrink the file--Select the log file--in the contraction mode to choose to shrink to XXM, here will give an allowable contraction to the minimum number of M, directly enter this num

SQL statement optimization and efficiency in SQL Server, serversql Many people do not know how SQL statements are executed in SQL server. They are worried that the SQL statements they write will be misunderstood by

Many of you have encountered SQL injection, most of which are caused by less rigorous Code. they have made many mistakes before learning to be serious. Many of you have encountered SQL injection, most of which are caused by less rigorous Code. they have made many mistakes before learning to be serious. However, it would be very painful for you to take over a second-class disability website and ask you to m

at the statement level, the hint syntax is:/*+ bind_aware*/;Ø about outlines: In the scene of outlines existence, the ACS will fail whether the system level is enabled or the statement level is enabled through hint;6. Conclusion:New features of Oracle Database 11g The ACS feature provides an easy way to efficiently share SQL statement execution plans with bound variables. Because ACS produces a new execution plan only if the binding variable value se

First, the question of the proposedIn the early stage of application system development, because the database data is relatively small, for querying SQL statements, complex views of the writing of the SQL statement can not be used to write a variety of performance advantages and disadvantages, but if the application system submitted to the actual application, with the data in the database increases, The res

SQL optimization tips and guidelines To optimize queries and avoid full table scanning, you should first consider creating an index on the columns involved in where and order. Try to avoid null value determination on the field in the where clause. Otherwise, the engine will discard the index and perform full table scan. select id from t where num is null It is best NOT to leave NULL for the database and try

Essential SQL query optimization techniques to speed up Website access and SQL access In this article, I will introduce how to identify queries that cause performance problems, how to locate their problems, and how to quickly fix these problems and other methods to speed up the query. You must know that a website with quick access can be liked by users, help web

Recommended this SQL optimization book, "Effective MySQL SQL statement optimization."The main explanation: How to analyze the performance of SQL, index principle, how to create appropriate index, how to analyze the performance bot

The third part of SQL optimization summary1. Optimization of general principles2. Specific considerations1. General principles of SQL optimization1) Target: Reduce server resource consumption (mainly disk IO)2) Design:1. Rely on the Oracle optimizer as much as possible2. Appropriate index (data repeat large column do n

Ii. Improve SQL statements Many people do not know how SQL statements are 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: S