query optimization techniques in sql server

In the previous article "Supplement to SQL query optimization -- (in)", we mainly discussed the query statements such as select... from... where... in. Next, let's discuss Select... from... where... exists. The query speed of exists is much higher than that of in, which is w

query can be completed successfully　　This is the index to change the execution plan of the query, to avoid the full table scan when there is no row of data on the lock blocking the situation. The index mentioned here is not just for the sake of efficiency, but more importantly, to change the way the query is executed, to avoid encountering locks on the table tha

SQL Server CPU performance check and optimization related SQL statements, very good SQL statements, recorded here: -- Begin SQL related to CPU Analysis and Optimization -- use DMV to a

find books, we generally use the way (the definition of parameters and data collection on the Assignment simulation page): 1 DECLARE @publishers VARCHAR (a); 2 SET @publishers = ' Tsinghua University Press ' ; 3 SELECT * from WHERE Publishers=@publishers The results are as follows: When the publisher filter condition is NULL, how do you write SQL? 1 DECLARE @publishers VARCHAR (a); 2 SELECT * from WHERE Publishers=ISNULL(@publishers, Publishers

from T where 1=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

the first b_table left associated a_table, very slow, about 1 seconds more , and the amount of data is very small, but if in turn, a_table left associated b_table, then quickly, It's about 100 milliseconds . So, another interesting phenomenon is found: Large table Left association small table, very slow, small table left associative right table, soon. Of course, these we all know theoretically, but the actual development will be forgotten. Or when both tables are empty at first, and without ta

', ' 11451 ') and (g.user_id = U.useridand g.is_up = ' true ' and g.checks = 1AND g.disabled = ' false ' And u.disabled = ' false ' and U.checkid = 2) LIMIT 50The above SQL runs for dozens of seconds because the data is a bit more, and then I use Explan.The index of walking is IND_IS_UPAnd then look at the is_up of how much data, in fact, are all true, so the ind_is_up here is not really interesting.So we're going to go back to the

the complexity of O (n) algorithms to ensure that the data between datasets are not duplicated. With the theoretical basis, you can change the SQL structure. After ensuring that there are no duplicates in the Data logic, you can change the two Union statements to Union all, the response speed of a query changes from 1.7 seconds to 300 milliseconds, which takes only 17% of the previous time. Union is also u

with delete can have a certain effect on the performance of the stored procedure. Because delete takes a full table-by-scan approach, it is a transactional operation that is counted into the SQL Server transaction log. Not only increases the running time, but also writes log files frequently, which causes the log file to be too large and consumes disk space excessively. Therefore, you can use the truncate

optimization based on the WHERE clause, it is still necessary to understand how the query optimizer works, if not, and sometimes the query optimizer does not query quickly as you intended.During the query analysis phase, the query