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