加速TSQL

Question

大家好，我想知道是否有更有效的方法来执行这个TSQl脚本。它基本上是按照帐户名称获取最新的活动排序，然后将其连接到accounts表中。因此，您可以获得帐户的最新活动。问题是目前有大约22,000个最新的活动，所以显然它必须通过大量的数据，只是想知道是否有更有效的方式来做我正在做的事情？

DECLARE @pastAppointments TABLE (objectid NVARCHAR(100), account NVARCHAR(500), startdate DATETIME, tasktype NVARCHAR(100), ownerid UNIQUEIDENTIFIER, owneridname NVARCHAR(100), RN NVARCHAR(100))

INSERT INTO @pastAppointments (objectid, account, startdate, tasktype, ownerid, owneridname, RN)
SELECT * FROM (
    SELECT fap.regardingobjectid, fap.regardingobjectidname, fap.actualend, fap.activitytypecodename, fap.ownerid, fap.owneridname,
    ROW_NUMBER() OVER (PARTITION BY fap.regardingobjectidname ORDER BY fap.actualend DESC) AS RN 
    FROM FilteredActivityPointer fap
    WHERE fap.actualend < getdate() 
    AND fap.activitytypecode NOT LIKE 4201 
) tmp WHERE RN = 1
ORDER BY regardingobjectidname

SELECT fa.name, fa.owneridname, fa.new_technicalaccountmanagername, fa.new_customerid, fa.new_riskstatusname, fa.new_numberofopencases,
fa.new_numberofurgentopencases, app.startdate, app.tasktype, app.ownerid, app.owneridname
FROM FilteredAccount fa LEFT JOIN @pastAppointments app on fa.accountid = app.objectid and fa.ownerid = app.ownerid
WHERE fa.statecodename = 'Active' 
AND fa.ownerid LIKE @owner_search 
ORDER BY fa.name

Answer 1

您可以从第一个INSERT查询中删除ORDER BY regardingobjectidname -这种排序在INSERT查询上的唯一(狭义)目的是在要插入的表上存在标识列。在这种情况下没有，所以如果优化器不够智能，它将执行无意义的排序。