SQL Server uses the lock prompt to optimize Row_number ()-programmer knowledge _ MySQL

Some old pages on the website still use window function similar to Row_number for paging. in this case, it may take a long time to wait and consume a lot if the grave Post is dug. here we will introduce how to use a specific Hint lock to improve the query speed based on the Row_number () feature. some old pages on the website are still processed by page using the window function similar to Row_number. in this case, it may take a long time to wait and consume a lot. here we will introduce how to use a specific Hint lock to improve the query speed based on the Row_number () feature.

Serving directly

Script environment can be found in "MapReduce" in SQL Server Optimization Tips

The following queries are common in paging

set statistics time on  select * from (select ProductID, rn = ROW_NUMBER() OVER (ORDER BY ProductID)from [bigTransactionHistory]) as twhere t.rn between 15631801 and 15631802

This query has been executed on my computer for 15 S. This is still the case where all the data is in the memory! 1-1

The execution of a simple execution plan is a bit incredible. after all, there are more than 6 million logical reads and no physical reads.

And the CPU time is almost the same as the occupied time. after blocking and other factors are eliminated, we locate the consumption on the query itself. in this case, we will mention the Row_number () feature. it can serialize thousands of data so that we can find the exact data point we want, however, the default implementation method is to add a row lock for each row of data.

We enable Trace Flag 1200 to capture the lock when executing the statement again. we can see that Row_number () has not implemented the lock upgrade 1-2.

dbcc traceon(3604,1200,-1)select * from (select ProductID, rn = ROW_NUMBER() OVER (ORDER BY ProductID)from [bigTransactionHistory]) as twhere t.rn between 15631801 and 15631802

Now we can solve this problem by using the lock hint format to manually upgrade it.

Here I use the page lock, 1-3

The two are the same in terms of execution plans, and the estimates are also the same. 1-4

select * from (select ProductID, rn = ROW_NUMBER() OVER (ORDER BY ProductID)from [bigTransactionHistory] with(paglock)) as twhere t.rn between 15631801 and 15631802

We can see that the method of viewing execution plans is not suitable here. we need to have a more detailed understanding of resource consumption.

Note: We can also use Trace Profiler to capture locks, but be careful when using them.

Row_number () cannot see lock upgrade by default, and may be upgraded back to the global performance bottleneck

If your application doesn't care about dirty reads, the nolock method is more pleasant :)

Others: when data is updated and blocked, some business colleagues may ask which data has been updated?

Here I wrote a simple query to find the row in which the specific update is locked, 2-1

begin tran tttupdate dbo.[bigProduct] set size=111 where ProductID<1100-- rollback when finish test--rollback tran ttt--open another sessionSELECT * FROM [bigProduct] with(nolock)WHERE    %%LOCKRES%% IN    (        SELECT             tl.resource_description        FROM sys.dm_tran_locks AS tl        INNER JOIN sys.partitions AS t2 ON            t2.hobt_id = tl.resource_associated_entity_id        WHERE             t2.object_id = OBJECT_ID('bigProduct')            AND tl.resource_type = 'KEY'    )

Conclusion: any element in the system may become a stumbling block to balance. find it, understand it, and use it.

The above is what SQL Server uses the lock prompt to optimize Row_number ()-the programmer needs to know _ MySQL content. For more information, please follow the PHP Chinese network (www.php1.cn )!