SQL Optimization-notin

Reading the directory has been engaged in O & M work, and it is inevitable to optimize some SQL statements. Due to my laziness, many classic cases have not been recorded. I am sorry for the case statement of a large real estate company, inspection Date, Problem Statement (*). application, 3 COUNT (*) ASUserCount4FROM (SELECTf. applic

Reading the directory has been engaged in O & M work, and it is inevitable to optimize some SQL statements. Due to my laziness, many classic cases have not been recorded. I am sorry for the case statement of a large real estate company, inspection Date, Problem Statement (*). application, 3 COUNT (*) AS UserCount 4 FROM (SELECT f. applic

Reading directory

I have been engaged in O & M work and will not be able to optimize some SQL statements. Due to my laziness, many classic cases have not been recorded, so I am sorry.

Case statement

A large real estate company, inspection date, Problem Statement

(*). Application, 3 COUNT (*) AS UserCount 4 FROM (SELECT f. application 5 FROMmyUserRights urmyFunction f ON ur. objectType = f. functionCodemyUser u ON ur. userGUID = u. userGUID (IsDisabeld) (13 SELECT UserGUID14FROM myUserRoles) f. application, 17 ur. userGUID. application20) AS temp2myApplication a ON. application = temp2.Applicationa. application IN (24 SELECT Application25FROM myFunction ,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,, ,,,,,,,,,,,,,,,,,,,,,) application) temp2.UserCount temp2.UserCount)>. licenseUserCount

Table 'worktable '. Scan count 1, logical reads 109, physical reads 0, pre-reads 0, lob logic reads 0, lob physical reads 0, and lob pre-reads 0. Table 'myuserroles '. 1 scan count, 73 logical reads, 0 physical reads, 0 pre-reads, 0 lob logical reads, 0 physical reads, and 0 lob pre-reads. Table 'myuserrights '. The scan count is 2772576, the logic reads 11812753, the physical reads 0, the pre-read 0, the lob logic reads 0, the lob physical reads 0, and the lob pre-read 0. Table 'myfunction '. Scan count 2, logical reads 90043 times, physical reads 0 times, pre-reads 0 times, lob logic reads 0 times, lob physical reads 0 times, and lob pre-reads 0 times. Table 'myuser '. Scan count 1, logical reads 163, physical reads 0, pre-reads 0, lob logic reads 0, lob physical reads 0, and lob pre-reads 0. Table 'myapplication '. 1 scan count, 2 logical reads, 0 physical reads, 0 pre-reads, 0 lob logical reads, 0 physical reads, and 0 lob pre-reads.

(One row is affected)
Table 'worktable '. Scan count 1, logical reads 109, physical reads 0, pre-reads 0, lob logic reads 0, lob physical reads 0, and lob pre-reads 0.
Table 'myuserroles '. 1 scan count, 73 logical reads, 0 physical reads, 0 pre-reads, 0 lob logical reads, 0 physical reads, and 0 lob pre-reads.
Table 'myuserrights '. The scan count is 2772576, the logic reads 11812753, the physical reads 0, the pre-read 0, the lob logic reads 0, the lob physical reads 0, and the lob pre-read 0.
Table 'myfunction '. Scan count 2, logical reads 90043 times, physical reads 0 times, pre-reads 0 times, lob logic reads 0 times, lob physical reads 0 times, and lob pre-reads 0 times.
Table 'myuser '. Scan count 1, logical reads 163, physical reads 0, pre-reads 0, lob logic reads 0, lob physical reads 0, and lob pre-reads 0.
Table 'myapplication '. 1 scan count, 2 logical reads, 0 physical reads, 0 pre-reads, 0 lob logical reads, 0 physical reads, and 0 lob pre-reads.

(One row is affected)

SQL Server execution time:
CPU time = 12625 milliseconds, occupied time = 12652 milliseconds.
SQL Server Analysis and Compilation Time:
CPU time = 0 ms, occupied time = 0 ms.

SQL Server execution time:
CPU time = 0 ms, occupied time = 0 ms.

I/O: tens of millions. This statement is a corresponding display page. count (*) is used for paging. The execution time is 12 S. It is confusing to get the statement.

Analysis Methods

I am talking about my analysis methods. I cannot give you the database here, so I can only share my analysis methods. I can inspire you to achieve the purpose of this Article. Generally, I get this SQL statement, run it again first.

Then, let's look at the execution plan, find the biggest overhead, and see if we can add index optimization. It's not very useful if we look too much,

The biggest overhead lies in index search. In fact, what I hate most is this. The biggest overhead is index search, and there is no way to optimize it by adding indexes. This is usually the case, this is because the execution plan is selected incorrectly,

If you select the wrong execution plan, the SQL statement you wrote is incorrect. What is the problem? If the SQL statements contain OR, not in, which does NOT comply with SARG, I have adopted a more effective verification method to force change the connection.

Method, very violent, and dangerous

(*). Application, 3 COUNT (*) AS UserCount 4 FROM (SELECT f. application 5 FROMmyUserRights urmyFunction f ON ur. objectType = f. functionCodemyUser u ON ur. userGUID = u. userGUID (IsDisabeld) (13 SELECT UserGUID14FROM myUserRoles) f. application, 17 ur. userGUID. application20) AS temp221RIGHT hash join myApplication a ON. application = temp2.Applicationa. application IN (24 SELECT Application25FROM myFunction ,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,, ,,,,,,,,,,,,,,,,,,,,,) application) temp2.UserCount temp2.UserCount)>. licenseUserCount