sql query performance tuning tips

Let's look at what bookmarks look for: When the optimizer chooses a nonclustered index that contains only part of the query request, a lookup (lookup) is required to retrieve other fields to satisfy the request. A key lookup for a table with a clustered index (key lookup) is a RID lookup (RID lookup) for a heap table. This lookup is--bookmark lookup. The bookmark lookup reads the data from the table based on the row locator of the index. Therefore,

50 tips for optimizing query: (see which one is suitable for you) 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 hard

Tags: MySQL index performanceTitleCREATE TABLE ' score ' ( ' id ' int (one-by-one) not null, ' studentid ' int (one) not null, ' subjectid ' int (one) NOT NULL , ' score ' int (one) not NULL, PRIMARY KEY (' id ') ) Engine=innodb DEFAULT Charset=utf8; --Create a new index to improve query performance of three SQL at the same time ALTER TABLE ' score ' ADD Inde

TRIGGERCREATETYPECREATEOPERATORCREATEIndextypeTenrows selected. CHF@XFF>SELECTtable_name fromuser_tab_privs; no rows selectedCreate a CHF user, authorize Create Session,resource, no V$session,v$sesstat and v$statname view access, log in with Plsql Dev and query the User_tables table (no prompt at logon , only the user will be prompted when the query is executed or the related class is performed)Second, sol

: It can be seen that there is a clustered index PK_UserID and a non-clustered index IX_UserName. Look at the effect of creating a bookmarked search: Select UserName, Gender from dbo. UserInfo where UserName = 'usern600' According to the preceding SQL Execution Plan, a Key Lookup is generated, as shown in figure If you rewrite the preceding SQL statement Select UserName from dbo. UserInfo where User

The database was restarted on Sunday, just looking at the optimized results. By the way, look for new SQL statements to be optimized.Just found several similar statements, as shown belowSelect * From tablea where id not in (select ID from tableb)From execution time 20 seconds ~ 70 seconds.Start AnalysisFirst, whether to use the index. Because both IDs are primary keys, there is no index problem.Next, we analyze the index and find that although these t

By observing the execution plan, we found that the previous execution plan used a hash join for many of the large table connections, and that the query optimizer chose to use parallel execution, faster because of the large number of data involved in the table. And we optimized the execution plan because of the existence of the index, and the data in the table is very large, the filter condition value in a very wide statistical information step range,

1. The query conditions are accurate.II. SQL logic execution sequence The code is as follows:Copy code FROM --> JOIN --> WHERE --> GROUP --> HAVING --> DISTINCT --> ORDER --> TOP III. HorizontalQuery required fieldsWhen connecting multiple tables in an SQL statement, use the table alias and prefix the alias on each Column, this reduces the parsing time and s

A Data Analysis System was developed some time ago, using Asp.net + sqlserver 2000. Because the data volume is collected in real time, two or three pieces of data are inserted per second on average. The customer uses the system to analyze the data during working days. During table creation, the latest data is stored in the History Table due to the large amount of data. Some manually processed data or automatically processed data based on conditions (the automatically processed data accounts f

Tags: style using SP on C log r ad Size1. Split the multi-table connection into smaller parts in order to execute2. Replace the outer connection with an inner connection.3. Priority line which can greatly reduce the operation of the return row, and then the production of the temporary table and other tables to the left connection.4. Modify the like program to remove% because there is no index to use.5. Use stored procedures to encapsulate which complex SQL