sql query optimization tips

1. IntroductionIn the early stage of application system development, because the database data is relatively small, for querying SQL statements, the use of indexes and the writing of complex views can not understand the performance of the SQL statement of various ways, but the application system in practice, with the data in the database increases, The response speed of the system is one of the most importa

Label:One, SET STATISTICS IO (about the amount of disk activity generated by the TSQL statement query) --Displays information about the amount of disk activity generated by Transact-SQL statements SET STATISTICS IO on --close information about the amount of disk activity generated by Transact-SQL statements SET STATISTICS IO OFF The information displayed is a

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 to the application. 11. Rebuild the index DBCC reindex, DBCC indexdefrag, shrink data and log DBCC shrinkdb, and DBCC shrinkfile. set automatic log shrinking. for large databases, do not set Automatic database growth, which will reduce the server performance. The writing of T-

block according to the pointer on the index block, so that the IO traffic is much less. Second, index optimization technology Is there an index that must be retrieved quickly? The answer is yes. Sometimes it's better to use indexes than to index faster. For example, we want to retrieve all the records in the above table, if not indexed, need access to 8,000 x1000 bytes/8k = 1000 pages, if you use the index, first retrieves the index, accesses 8,000 x

optimization of SQL dynamic Query stitching stringThe most primitive direct write: String sql= "select * from Testtables where 1=1";... Such code is inefficient, which affects the index reference of the databaseAs shown below:private void Testonemethod (){String querysql = "SELECT * from Testtables where 1=1";if (hason

This article is a MySQL table and query optimization of some practical tips for a detailed analysis of the introduction of the needs of friends under the reference The MySQL table stage is a very important link, the table structure of good or bad directly affect the follow-up management and maintenance, to go to work before tomorrow to share the summary of Perso

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: Select * From Table1 where TID> 10000 and name = 'hangsan' Some

the operation, all of which provide favorable evidence for optimizing the query. 1th, the 3 diagram IO overhead is relatively large, the 2nd graph estimates the number of rows is larger, and then according to other information, the first thought should be to build the index, not the words to change the query.Let's take a look at what optimization information the Database Engine Tuning Advisor can give us,

SQL Optimization Tips (1):Connection order in the WHERE clause: Oracle parses the WHERE clause in a bottom-up order, and according to this principle, the connections between tables must be written before other where conditions, those that can filter out a large number of recordsMust be written at the end of the WHERE clause.For exampleInefficient: SELECT * from R

Tags: style blog http color using IO data htmlOne, SET STATISTICS IO (about the amount of disk activity generated by the TSQL statement query) --Displays information about the amount of disk activity generated by Transact-SQL statementsSET STATISTICS IO on--close information about the amount of disk activity generated by Transact-SQL statementsSET STATISTICS IO

From: http://www.cnblogs.com/cpcpc/archive/2011/02/28/2123047.html In our ordinarySQLIn the query, we actually have a lot of principles that should be paid attention to, since the implementation of SQL queryOptimizationThis article will introduce 30 query optimization principles. Principles that should be paid a

=0This type of code does not return any result sets, but consumes system resources and should be changed to this:CREATE TABLE #t (...)13, a lot of times with exists instead of in is a good choice:Select num from a where num in (select num from B)Replace with the following statement:Select num from a where exists (select 1 from b where num=a.num)14, not all indexes are valid for the query, SQL is based on th

not return any result sets, but consumes system resources and should be changed to this:CREATE TABLE #t (...) 13, a lot of times with exists instead of in is a good choice:Select num from a where num in (select num from B)Replace with the following statement:Select num from a where exists (select 1 from b where num=a.num) 14, not all indexes are valid for the query, SQL is based on the data in the table to

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

SQL Server query performance optimization and analysis of transactions and locks (1) Step 2: analyze the locking status using SQL statements. sys. in the sysprocesses view, find out the source of waiting for the initial lock of resources and the subsequent processes to be forced to stop. The following code is applicabl

system resources and should be changed to this:CREATE TABLE #t (...)13, a lot of times with exists instead of in is a good choice:Select num from a where num in (select num from B)Replace with the following statement:Select num from a where exists (select 1 from b where num=a.num)14, not all indexes are valid for the query, SQL is based on the data in the table to quer