We do software development , Most people have to deal with databases , especially erp Developed , Dealing with databases is more frequent , Stored procedures are always thousands of lines , If you have a lot of data , Mobility Big , So can we guarantee the smooth operation of the system in the next period of time ? Can we guarantee that the next person can understand our stored procedure ? So I combine the company's usual training and personal work experience with you Share it , Hopefully that helped .
Need to know SQL sentence , I think we need to know SQL
Server How does the query analyzer perform our SQL Of the statement , A lot of us look at the implementation plan , Or use Profiler To monitor and tune why queries or stored procedures are slow , But like
If we know the logical order of query analyzer execution , When you start, you have a plan , So, are you sure about it ?
One 、 The logical execution order of the query
(1) FROM left_table
(3) join_type JOIN right_table (2) ON join_condition
(4) WHERE where_condition
(5) GROUP BY group_by_list
(6) WITH {cube | rollup}
(7) HAVING having_condition
(8) SELECT (9) DISTINCT (11) top_specification select_list
(9) ORDER BY order_by_list
The standard SQL The order of resolution is :
(1) FROM Clause Assemble data from different data sources
(2) WHERE Clause Filter records based on specified criteria
(3) GROUP BY Clause Divide the data into groups
(4) Use aggregate functions for calculations
(5) Use HAVING Clause filter group
(6) Calculate all expressions
(7) Use ORDER BY Sort the result set
Two 、 Execution order
1. FROM: Yes FROM The first two tables in Clause execute Cartesian product to generate virtual table vt1
2. ON: Yes vt1 Table application ON The filter only satisfies join_condition For the real line to be inserted vt2
3. OUTER(join): If you specify OUTER JOIN Keep table (preserved table) Rows not found in are added as external rows to vt2, Generate t3, If from Contains more than two tables , Then repeat the steps and steps for the result table generated by the previous join and the next table to end directly .
4. WHERE: Yes vt3 application WHERE The filter can only make where_condition by true The lines of are inserted vt4
5. GROUP BY: Press GROUP BY Column list pairs in the clause vt4 Line grouping generation in vt5
6. CUBE|ROLLUP: Supergroup (supergroups) Insert vt6, Generate vt6
7. HAVING: Yes vt6 application HAVING The filter can only make having_condition by true To insert vt7
8. SELECT: Handle select List generation vt8
9. DISTINCT: Repeat lines from vt8 In the process of removal, it produces vt9
10. ORDER BY: take vt9 Click on the line of order by Clause to generate a cursor vc10
11. TOP: from vc10 Select the specified number or proportion of rows to generate vt11 And back to the caller
See here , So used Linq to SQL It's a bit similar in grammar ? If we know SQL
Server Execution order , So let's further develop our daily life SQL Good habits , That is to say, we should consider the performance while realizing the function , A database is a tool that can perform set operations , We should
Make the most of this tool , The so-called set operation is actually batch operation , It is to minimize the cyclic operation of large amount of data on the client side , While using SQL Statement or stored procedure instead of .
3、 ... and 、 Just return the data you need
Returning data to the client requires at least data extraction from the database 、 Network transmission data 、 Client receives data and client processes data and so on , If you return data that you don't need , It will add servers 、 Invalid labor of network and client , The harm is obvious , To avoid this kind of event, we need to pay attention to :
A、 Horizontally
(1) Do not write SELECT * The sentence of , Instead, choose the fields you need .
(2) When in SQL When multiple tables are joined in a statement , Please use the alias of the table and prefix the alias with each Column On . thus , It can reduce the parsing time and reduce the parsing time Column Grammatical errors caused by ambiguity .
1
If there is a watch table1(ID,col1) and table2(ID,col2)
1
Select A.ID, A.col1, B.col2
2
-- Select A.ID, col1, col2 – Don't write like this , Not conducive to future program expansion
3
from table1 A inner join table2 B on A.ID=B.ID Where …
B、 Vertically
(1) Write reasonably WHERE Clause , Don't write without WHERE Of SQL sentence .
(2) SELECT TOP N * -- No, WHERE Replace the condition with this .
Four 、 Do as little repetitive work as possible
A、 Control multiple executions of the same statement , In particular, many programmers pay little attention to the multiple execution of some basic data .
B、 Reduce multiple data transformations , Maybe the need for data conversion is a design problem , But reducing the number of times is what programmers can do .
C、 Eliminate unnecessary subqueries and join tables , Subqueries are generally interpreted as external connections in the execution plan , The extra connection table brings extra cost .
D、 Merge multiple times of the same condition for the same table UPDATE, such as
UPDATE EMPLOYEE SET FNAME='HAIWER'
WHERE EMP_ID=' VPA30890F'UPDATE EMPLOYEE SET LNAME='YANG'
WHERE EMP_ID=' VPA30890F'
These two statements should be combined into one of the following statements
UPDATE EMPLOYEE SET FNAME='HAIWER',LNAME='YANG'WHERE EMP_ID=' VPA30890F'
E、UPDATE Do not break it into DELETE operation +INSERT Form of operation , Although the functions are the same , But the performance difference is very big .
5、 ... and 、 Note the use of temporary tables and table variables
In complex systems , Temporary tables and table variables are hard to avoid , About the use of temporary tables and table variables , We need to pay attention to :
A、 If the sentence is complicated , Too many connections , Consider using temporary tables and table variables step by step .
B、 If you need to use the same part of a large table more than once , Consider using temporary tables and table variables to hold this data .
C、 If you need to synthesize data from multiple tables , To form a result , Consider using temporary tables and table variables to summarize the data of these tables step by step .
D、 In other cases , You should control the use of temporary tables and table variables .
E、 About the selection of temporary tables and table variables , Many say that table variables are in memory , Fast , Table variables should be preferred , But it is found in practice that :
(1) The main consideration is the amount of data that needs to be placed in the temporary table , In case of large amount of data , A temporary watch is faster .
(2) Execution time period and estimated execution time ( How long? )
F、 About the use of temporary tables SELECT INTO and CREATE TABLE + INSERT INTO The choice of , In general :
SELECT INTO than CREATE TABLE + INSERT INTO It's a lot faster ,
however SELECT INTO Will lock TEMPDB System table of SYSOBJECTS、SYSINDEXES、SYSCOLUMNS, In a multi-user concurrent environment , It's easy to block other processes .
So my advice is , In a concurrent system , Use as much as possible CREATE TABLE + INSERT INTO, And the single statement with large amount of data is in use , Use SELECT INTO.
6、 ... and 、 Usage of subquery
Subquery is a SELECT Inquire about , It's nested in SELECT、INSERT、UPDATE、DELETE Statement or other subqueries .
Subqueries can be used wherever expressions are allowed , Subqueries make our programming flexible , It can be used to realize some special functions . But in terms of performance , Often an inappropriate subquery usage can create a performance bottleneck . If a subquery's criteria use the fields of its outer table , This seed query is called related sub query .
Related subqueries can be used IN、NOT IN、EXISTS、NOT EXISTS introduce . About related subqueries , It should be noted :
(1) NOT IN、NOT EXISTS The related subqueries of can be changed to LEFT JOIN Instead of writing . such as :
SELECT PUB_NAME FROM PUBLISHERS WHERE PUB_ID NOTIN (SELECT PUB_ID FROM TITLES WHERE TYPE ='BUSINESS')
It can be rewritten as :
SELECT A.PUB_NAME FROM PUBLISHERS A LEFTJOIN TITLES B ON B.TYPE ='BUSINESS'AND A.PUB_ID=B. PUB_ID WHERE B.PUB_ID IS NULL
such as NOT EXISTS:
SELECT TITLE FROM TITLES
WHERE NOT EXISTS
(SELECT TITLE_ID FROM SALES WHERE TITLE_ID = TITLES.TITLE_ID)
1
It can be rewritten as :
SELECT TITLE
FROM TITLES LEFTJOIN SALES
ON SALES.TITLE_ID = TITLES.TITLE_ID
WHERE SALES.TITLE_ID ISNULL
2) If you ensure that the subquery does not repeat ,IN、EXISTS The related subqueries of can be used INNER JOIN Instead of . such as :
SELECT PUB_NAME
FROM PUBLISHERS
WHERE PUB_ID IN
(SELECT PUB_ID
FROM TITLES
WHERE TYPE ='BUSINESS')
1
It can be rewritten as :
SELECT A.PUB_NAME --SELECT DISTINCT A.PUB_NAME
FROM PUBLISHERS A INNERJOIN TITLES B
ON B.TYPE ='BUSINESS'AND
A.PUB_ID=B. PUB_ID
(3) IN For related subqueries EXISTS Instead of , such as :
SELECT PUB_NAME FROM PUBLISHERS
WHERE PUB_ID IN
(SELECT PUB_ID FROM TITLES WHERE TYPE ='BUSINESS')
Instead of :
SELECT PUB_NAME FROM PUBLISHERS WHERE EXISTS
(SELECT1FROM TITLES WHERE TYPE ='BUSINESS'AND
PUB_ID= PUBLISHERS.PUB_ID)
4) Do not use COUNT(*) Sub query to determine whether there is a record , It is best to LEFT JOIN perhaps EXISTS, For example, someone wrote a sentence like this :
SELECT JOB_DESC FROM JOBS
WHERE (SELECTCOUNT(*) FROM EMPLOYEE WHERE JOB_ID=JOBS.JOB_ID)=0
Should be changed into :
SELECT JOBS.JOB_DESC FROM JOBS LEFTJOIN EMPLOYEE
ON EMPLOYEE.JOB_ID=JOBS.JOB_ID
WHERE EMPLOYEE.EMP_ID ISNULL

SELECT JOB_DESC FROM JOBS
WHERE (SELECT COUNT(*) FROM EMPLOYEE WHERE JOB_ID=JOBS.JOB_ID)0
Should be changed into :
SELECT JOB_DESC FROM JOBS
WHEREEXISTS (SELECT 1 FROM EMPLOYEE WHERE JOB_ID=JOBS.JOB_ID)
7、 ... and : Use index as much as possible
After indexing , Not every query uses an index , In the case of indexes , There will also be big differences in the efficiency of index usage . As long as we don't force an index in the query statement , Index selection
And the way to use it is SQLSERVER The optimizer's choice of self action , And it selects the conditions of the query statement and the statistics of the related tables , This requires us to write SQL Try to make a statement
The optimizer can use indexes . To enable the optimizer to use indexes efficiently , You should pay attention to :
(1) Do not operate on index fields , And find a way to change , such as :
SELECT ID FROM T WHERE NUM/2=100
Should be changed to :
SELECT ID FROM T WHERE NUM=100*2

SELECT ID FROM T WHERE NUM/2=NUM1
If NUM The index should be changed to :
SELECT ID FROM T WHERE NUM=NUM1*2
If NUM1 An index should not be changed .
(2) I've found such a statement :
SELECT year , month , amount of money FROM Balance sheet WHERE100* year + month =2010*100+10
1
Should be changed to :
SELECT year , month , amount of money FROM Balance sheet WHERE year =2010 AND month =10
(3) Do not format the index fields
An example of a date field :
WHERECONVERT(VARCHAR(10), Date fields ,120)='2010-07-15'
Should be changed to
WHERE Date fields 〉='2010-07-15'AND Date fields '2010-07-16'

ISNULL Examples of transformations :
WHEREISNULL( Field ,'')'' Should be changed to :WHERE Field ''
WHEREISNULL( Field ,'')='' Should not modify
WHEREISNULL( Field ,'F') ='T' Should be changed to : WHERE Field ='T'
WHEREISNULL( Field ,'F')'T' Should not modify
(4) Do not format the index fields
WHERELEFT(NAME, 3)='ABC' perhaps WHERE SUBSTRING(NAME,1, 3)='ABC'
Should be changed to : WHERE NAME LIKE'ABC%'
Example of date query :
WHEREDATEDIFF(DAY, date ,'2010-06-30')=0
Should be changed to :WHERE date ='2010-06-30' AND date '2010-07-01'
WHEREDATEDIFF(DAY, date ,'2010-06-30')0
Should be changed to :WHERE date '2010-06-30'
WHEREDATEDIFF(DAY, date ,'2010-06-30')=0
Should be changed to :WHERE date '2010-07-01'
WHEREDATEDIFF(DAY, date ,'2010-06-30')0
Should be changed to :WHERE date ='2010-07-01'
WHEREDATEDIFF(DAY, date ,'2010-06-30')=0
Should be changed to :WHERE date ='2010-06-30'
(5) Don't use functions on index fields
WHERE LEFT(NAME, 3)='ABC' perhaps WHERE SUBSTRING(NAME,1, 3)='ABC'
Should be changed to : WHERE NAME LIKE 'ABC%'
Example of date query :
WHEREDATEDIFF(DAY, date ,'2010-06-30')=0
Should be changed to :WHERE date ='2010-06-30'AND date '2010-07-01'
WHEREDATEDIFF(DAY, date ,'2010-06-30')0
Should be changed to :WHERE date '2010-06-30'
WHEREDATEDIFF(DAY, date ,'2010-06-30')=0
Should be changed to :WHERE date '2010-07-01'
WHEREDATEDIFF(DAY, date ,'2010-06-30')0
Should be changed to :WHERE date ='2010-07-01'
WHEREDATEDIFF(DAY, date ,'2010-06-30')=0
Should be changed to :WHERE date ='2010-06-30'
(6) Don't make multi field connections to index fields
such as :
WHERE FAME+'. '+LNAME='HAIWEI.YANG'

Should be changed to :
WHERE FNAME='HAIWEI' AND LNAME='YANG'
8、 ... and : The join condition of multi table join is very important for index selection , So we need to pay special attention when we write connection conditions .
A、 When multiple meters are connected , The connection conditions must be complete , Prefer to repeat , Don't miss out .
B、 Try to use clustered indexes for join conditions
C、 Be careful ON、WHERE and HAVING The difference of some conditions
ON It's the first to execute ,
WHERE second ,HAVING Last , because ON It is to filter the unqualified records before statistics , It can reduce the data to be processed by the intermediate operation , It's supposed to be the fastest
Of ,WHERE It should be better than HAVING Hurry up , Because it filters the data SUM, Only used when two tables join ON Of , So when it's a watch , That's all WHERE Follow
HAVING Comparison of the .
1
Consider join precedence :
2
(1) INNER JOIN
3
(2) LEFT JOIN ( notes :RIGHT JOIN use LEFT JOIN replace )
4
(3) CROSS JOIN
Other things to pay attention to and understand are :
A、 stay IN In the following list of values , Put the most frequent values first , Put the least in the back , Reduce the number of judgments .
B、 Be careful UNION and UNION ALL The difference between .-- Allow duplicate data to be used UNION ALL good
C、 Pay attention to DISTINCT, Don't use... When it's not necessary .
D、TRUNCATE TABLE And DELETE difference .
E、 Reduce access to databases .
And we write stored procedures , If it's longer , Finally, mark it with a marker , Because it's very readable , Even if the sentence is not well written , But the sentences are neat ,C# Yes region,SQL What I prefer to use is :
--startof Check the number of people on the job
SQL sentence
--end of
Generally, we can't debug the program on the formal machine , But most of the time, the program is OK on our machine , But there's a problem in the formal system , But we can't operate on a formal machine , So what to do ? We can use rollback to debug our stored procedures or SQL sentence , So as to correct the mistakes .
BEGINTRAN
UPDATE a SET Field =''
ROLLBACK
I usually add the following paragraph to the job stored procedure , In this way, the check error can be put in the stored procedure , If you perform an error rollback operation , But if there is a transaction rollback in the program , Then don't write stored procedures
Business , This will cause transaction rollback nesting and reduce the execution efficiency , But most of the time, we can put the check in the stored procedure , This helps us to interpret the stored procedure , And troubleshooting .
BEGINTRANSACTION
-- Transaction rollback started

-- Check for errors
IF ( @@ERROR0 )
BEGIN
-- Rollback operation
ROLLBACKTRANSACTION
RAISERROR(' Delete work report error ', 16, 3)
RETURN
END

-- End the business
COMMITTRANSACTION

quote : CSDN linxianliang5201314 Of blog ------sql More related articles explaining the order of execution

  1. sql Explain the order of execution

    One . The logical execution order of the query (1) FROM left_table (3) join_type JOIN right_table (2) ON join_condition (4) WHERE where ...

  2. sql The order in which keywords are interpreted and executed

      sql The order in which keywords are interpreted and executed classification :  Written interview summary 2013-03-17 14:49 1622 Human reading   Comment on (1)  Collection   report SQL Keyword order No symbols are added to the field names in the table , If it's worth it, put single quotation marks There is a ...

  3. sql Logical execution order

    Need to know SQL sentence , I think we need to know SQL Server How does the query analyzer perform our SQL Of the statement , A lot of us look at the implementation plan , Or use Profiler To monitor and tune why queries or stored procedures are slow , But if we know, check ...

  4. SQL Clause execution order and Join A summary of

    SQL Clause execution order and Join A summary of FROM ON JOIN WHERE GROUP BY WITH CUBE or WITH ROLLUP HAVING SELECT DISTINCT ORDE ...

  5. SQL The nickname of and SQL The order of execution and SQL Optimize

    SQL Another name for 1. Not in where Use the alias of the column name in the , namely select name t from emp where t>2999; It's not allowed 2. The benefits of using aliases : Improve SQL The readability of Improve SQ ...

  6. Oracle sql Statement execution order ( turn )

    from: http://blog.csdn.net/lailai186/article/details/12612263 sql The analysis of grammar is from right to left One .sql Statement execution steps :1) Syntax analysis , Analysis statement ...

  7. mysql sql Execution order of

    turn :http://blog.csdn.net/u014044812/article/details/51004754 About sql and MySQL The order in which the statements are executed ( Must see !!!) original  2016 year 03 month 29 Japan ...

  8. About sql Execution order of

    standard sql The order of resolution is 1)FROM Clause , Assemble data from different data sources 2)WHERE Clause Screening records based on established conditions 3)GROUP BY Clause to divide data into groups 4) Use aggregate functions for calculations 5) send ...

  9. Oracle sql Statement execution order

    sql The analysis of grammar is from right to left One .sql Statement execution steps : 1) Lexical analysis , The lexical analysis stage is the first stage of the compilation process . The task of this stage is to read the source program one character at a time from left to right , That is to scan the character stream that constitutes the source program, and then according to the structure ...

Random recommendation

  1. js Form validation

    good JS verification ~~~~~~~~~~~~~~~~~~~~~~~~~ purpose : check ip Format of address Input :strIP:ip Address return : If the verification returns true, Otherwise return to false: */ function i ...

  2. Special test of number theory —— About a number and (lucas The number theory of )

    #include <iostream> #include <cstdio> #include <cstring> #include <cmath> #i ...

  3. TrueType, OpenType, PCL and PostScript Font version nterleaved 2 of 5 Barcode Font Advantage Package

    Interleaved 2 of 5 Barcode Font Advantage Package Contains TrueType, OpenType, PCL and PostScript Font version . More than 30 individual ...

  4. stay Tomcat The lower part belongs to the project in three ways :

    stay Tomcat The lower part belongs to the project in three ways :       1 Direct copy :       2. By configuring the virtual path     Modify the configuration file directly writes tomcat/conf/server.xml     find <H ...

  5. html5 canvas An example of a circular lottery

    Nothing at the end of the year , I found time to study canvas, Wrote a html5 The lottery code , Built a wheel , Useful children's shoes can be taken away . among ,canvas.onclick Trigger the lottery , The probability can be in core.lottery() Add... To the function , Beautification ...

  6. 23 A classic JDK Design patterns ( turn )

    Here is JDK In the relevant 23 An example of a classic design pattern : Structural( Structural mode ) Adapter: To change an interface or class into another . o    ● java.util.Arrays#asList() o  ...

  7. spring-data-mongodb The query result returns the specified field

        Method 1 DBObject dbObject = new BasicDBObject(); dbObject.put("status", 1); DBObject field ...

  8. UVALive 6947 Improvements(DP+ Tree array )

    [ Topic link ] https://icpcarchive.ecs.baylor.edu/index.php?option=com_onlinejudge&Itemid=8&page=sho ...

  9. mp4、AAC data format 、 Creation and modification time of parsing file

    AAC file Information : http://blog.csdn.net/u013427969/article/details/53091594 http://blog.chinaunix.net/uid-260 ...

  10. RHEL/Centos Next Sendmail Server setup

    Purpose Linux The configuration Sendmail The server , And through the client verification . Environmental Science Cento6 LAN ( Access to the Internet ) Content To configure Sendmail The server , So that the client can pass foxmail perhaps outlook ex ...