SQL Server uses partition functions for query optimization

When you encounter a requirement in a project, you need to get the latest item published by the merchant in the Merchant's collection, the requirements are simple, the SQL statements are not complex,

SelectT_usercollectmerchant.collectid,t_usercollectmerchant.merchantid,t_usercollectmerchant.userid,t_ Usercollectmerchant.addtime, (Select Top 1Lastupdate fromT_goodsinfowhereMerchantid=T_usercollectmerchant.merchantidOrder  byLastupdatedesc) asLastnewtime fromt_usercollectmerchantwhereUserid= +Order  byCollectiddescOffset0RowFetch Next  +Rows only

However, when the product data reached millions, the execution efficiency of this code becomes appalling, often die,

And look at the execution plan.

The scan of the commodity table takes up almost all of the CPU resources

As a results, the way to check the last update time of the merchant must be optimized

A new view is created with the following content

Select      from (select    through ORDER  by DESC  as rowID  from  T_goodsinfo
 as T WHERE   < 2

The time to group product data by merchant ID and remove the latest data

Then update the SQL query statement to:

SelectT_usercollectmerchant.collectid,t_usercollectmerchant.merchantid,t_usercollectmerchant.userid, LastUpdate 
     asLastnewtime fromt_usercollectmerchant Left JoinV_goodsinfo_merchantcount onV_goodsinfo_merchantcount.merchantid=T_usercollectmerchant.merchantidwhereUserid= +Order  byCollectiddescOffset0RowFetch Next  +Rows only

Re-execute

Efficiency has greatly increased

From the execution plan, you can continue to optimize, time relationship, temporarily here

SQL Server uses partition functions for query optimization