SQL查询调优- MS SQL Server -2012

I am new to sql tuning. I have the following SQL which takes around 15 to 20 seconds to produce the results.

我是sql调优新手。我有以下SQL语句，生成结果大约需要15到20秒。

SELECT D.DealerName,
       Z.Zone,
       C.Id                             ,
       L.Id                             ,
       A.Id                             ,
       L.LeadDate,
       LT.LeadType                      ,
       EM.FirstName + ' ' + EM.LastName ,
       LS.LeadSource                    ,
       --C.*,
       E.Id                             ,
       E.StartDateTime,
       0                                ,
       Chiefed = CASE A.AppointmentTypeId
                   WHEN 3 THEN 'True'
                   ELSE ''
                 END,
       9                                AS WorkflowPhase
FROM   Customers C( NOLOCK )
       INNER JOIN Dealers D
               ON C.DEALERId = D.Id
       INNER JOIN Leads L( NOLOCK )
               ON L.CustomerId = C.Id
       INNER JOIN Appointments A( NOLOCK )
               ON A.LeadId = L.Id
                  AND ( NOT( A.AppointmentTypeId = 5
                              OR A.AppointmentTypeId = 6 ) )
       JOIN CalendarEvents E( NOLOCK )
         ON E.TableId = 1
            AND E.TableRowId = A.Id
            AND E.IsDeleted = 0

            AND Dateadd(hh, @TZO, Getdate()) >= E.StartDateTime
       LEFT OUTER JOIN AppointmentResults AR( NOLOCK )
                    ON AR.EventId = E.Id
       LEFT OUTER JOIN LeadSources LS( NOLOCK )
                    ON LS.Id = L.LeadSourceId
       LEFT OUTER JOIN LeadTypes LT( NOLOCK )
                    ON LT.Id = L.LeadTypeId
       LEFT OUTER JOIN Users EM( NOLOCK )
                    ON EM.Id = E.EmployeeId
       LEFT OUTER JOIN Zone Z( NOLOCK )
                    ON Z.Id = C.ZoneId
WHERE  EXISTS(SELECT 1
              FROM   WorkflowStatus WS( NOLOCK )
              WHERE  TableId = 1
                     AND TableRowId = A.Id
                     AND WorkflowPhaseId = 9
                     AND IsCompleted = 0
                     AND IsDeleted = 0)
       AND ( EXISTS (SELECT 1
                     FROM   dbo.Uft_userpermissionzonesbyworkflow(@EmployeeId, 9)
                     WHERE  ZoneId = C.zoneid) )
       AND EXISTS (SELECT 1
                   FROM   Uft_userenableddealers(@EmployeeId)
                   WHERE  DealerId = C.DealerId)
ORDER  BY C.LastName,
          C.CompanyName,
          C.CompanyContact 

I already tuned up to my knowledge but still I can see some index scans. I tried to convert those index scans to index seek but it is not possible due to number of records.

我已经掌握了我的知识，但我仍然可以看到一些索引扫描。我试图将这些索引扫描转换为索引查找，但由于记录的数量，这是不可能的。

Please refer the screenshot of plan diagram and top operations Kindly provide any suggestions to improvise this query.

请参考计划图和top operations的截屏，请提供任何建议来临时处理这个查询。

2 个解决方案

#1

DECLARE @p TABLE (DealerId INT PRIMARY KEY WITH (IGNORE_DUP_KEY=ON))
INSERT INTO @p
SELECT DealerId
FROM dbo.Uft_userenableddealers(@EmployeeId)

DECLARE @z TABLE (ZoneId INT PRIMARY KEY WITH (IGNORE_DUP_KEY=ON))
INSERT INTO @z
SELECT ZoneId
FROM dbo.Uft_userpermissionzonesbyworkflow(@EmployeeId, 9)

SELECT ...
FROM ...
WHERE  EXISTS(SELECT 1
              FROM   WorkflowStatus WS( NOLOCK )
              WHERE  TableId = 1
                     AND TableRowId = A.Id
                     AND WorkflowPhaseId = 9
                     AND IsCompleted = 0
                     AND IsDeleted = 0)
       AND C.zoneid IN (SELECT * FROM @z)
       AND C.DealerId IN (SELECT * FROM @p)
ORDER  BY C.LastName,
          C.CompanyName,
          C.CompanyContact
OPTION(RECOMPILE)

#2

as discussed below Devarts answer here the example with a CTE instead of the declared table variables. I'd assume that the declared TVs are faster due to the key, but the CTE is ad-hoc and - maybe - better integrated. Thx for testing:

正如下面讨论的那样，Devarts在这里使用CTE而不是声明的表变量来回答这个示例。我认为，由于密钥的原因，声明的电视机速度更快，但CTE是临时的，而且——可能——集成得更好。谢谢测试:

;WITH p AS
(
    SELECT DealerId
    FROM dbo.Uft_userenableddealers(@EmployeeId)
)
,z AS
(
    SELECT ZoneId
    FROM dbo.Uft_userpermissionzonesbyworkflow(@EmployeeId, 9)
)
SELECT ...
FROM ...
WHERE  EXISTS(SELECT 1
              FROM   WorkflowStatus WS( NOLOCK )
              WHERE  TableId = 1
                     AND TableRowId = A.Id
                     AND WorkflowPhaseId = 9
                     AND IsCompleted = 0
                     AND IsDeleted = 0)
       AND C.zoneid IN (SELECT ZoneId FROM z)
       AND C.DealerId IN (SELECT DealerId FROM p)
ORDER  BY C.LastName,
          C.CompanyName,
          C.CompanyContact
OPTION(RECOMPILE)