SQL Server 2008的查询优化

Question

SQL Server 2008的查询优化

sqlsql-serversql-server-2008

3

我有一个名为“vw_AllJobsWithRecruiter”的视图。

ALTER VIEW dbo.vw_AllJobsWithRecruiter
AS
SELECT TOP(SELECT COUNT(iJobID_PK) FROM dbo.tbUS_Jobs)
         iJobId_PK AS JobId,
         dbo.ufn_JobStatus(iJobId_PK) AS JobStatus,
         dbo.ufn_RecruiterCompanyName(iJobId_PK) AS CompanyName,
         sOther AS OtherCompanyName
FROM dbo.tbUS_Jobs
WHERE bDraft = 0
ORDER BY dtPostedDate DESC

这个视图只包含3278行数据。

如果我执行以下查询：

SELECT * FROM vw_AllJobsWithRecruiter
WHERE  OtherCompanyName LIKE '%Microsoft INC%'

执行时间不到一秒钟。

现在我的问题是：

如果我使用以下查询：

SELECT * FROM vw_AllJobsWithRecruiter
WHERE CompanyName LIKE '%Microsoft INC%' 
      OR OtherCompanyName LIKE '%Microsoft INC%'

执行需要30秒时间，前端会抛出超时错误。

函数如下：

CREATE Function [dbo].[ufn_RecruiterCompanyName] (@JobId bigint)      
RETURNS nvarchar(200)      
AS      
BEGIN      
 DECLARE @ResultVar nvarchar(200)     
 DECLARE @RecruiterId bigint     

 select @RecruiterId = iRecruiterId_FK from dbo.tbUS_Jobs     with (Nolock)   
 where iJobId_PK = @JobId;      

 Select @ResultVar = sCompanyName from dbo.tbUS_RecruiterCompanyInfo     with (Nolock)   
 where iRecruiterId_FK = dbo.ufn_GetParentRecruiterID(@RecruiterId)      

 return isnull(@ResultVar,'')      

END

另一个功能

CREATE Function [dbo].[ufn_GetParentRecruiterID](@RecruiterId bigint)  
returns bigint  
as  
begin   
declare @ParentRecruiterId bigint  

SELECT @ParentRecruiterId = iParentId FROM dbo.tbUS_Recruiter with (Nolock)   
WHERE iRecruiterId_PK = @RecruiterId  

IF(@ParentRecruiterId = 0)  
 SET @ParentRecruiterId = @RecruiterId  

RETURN @ParentRecruiterId  
end

我的问题是

为什么执行时间这么长？

如何减少执行时间？

非常感谢您的关注。

- Arindam Rudra

TOP(SELECT COUNT(iJobID_PK) FROM dbo.tbUS_Jobs) 这个子句的目的是什么？它很奇怪，可能没有用。如果你需要有 iJobID_PK 不为空的行，那么 WHERE 子句更合适。 - Skrol29

如果不使用TOP(SELECT COUNT(iJobID_PK) FROM dbo.tbUS_Jobs)子句，我无法在视图中使用ORDER BY。 - Arindam Rudra

@Skrol29，TOP（..）构造等同于“SELECT TOP 100％”，并指示SQL Server遵循“ORDER BY”。如果没有TOP，它将忽略排序。 - Stephan B

1

哇，使用 TOP 的方式真是太糟糕了！如果需要 ORDER BY，请将其放在查询中而不是视图定义中。你的方法也不能保证有效。 - Martin Smith

2

@ARINDAM - 我并没有说它会导致你的性能问题（虽然当然它可以节省一个不必要的子查询）。它只是不能保证有效，而且在概念上完全是错误的方法。 - Martin Smith

你误解了视图的目的。如果你绝对需要一个包装器来包含排序行的SQL语句，那么使用表函数，它至少从SQL Server 2005开始就可用了。但是为了保持清洁，SQL包装器在服务器端不受欢迎。 - Skrol29

2个回答

1

问题在于您的嵌套函数调用。

您在WHERE子句中间接地调用了ufn_RecruiterCompanyName。

这意味着您的WHERE子句是非Sargable 的，必须为每一行运行该函数。

该函数还调用了ufn_GetParentRecruiterID。由于它在第一个函数内出现在您的WHERE子句中，并且也不是Sargable，因此您基本上在处理表格时对每一行进行了两次表扫描。

将函数调用替换为JOIN，您将看到性能大幅提升。

- JNK

是的，这就是主要问题。我正在尝试使用Join来解决。非常感谢。 - Arindam Rudra

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Stephan B · Accepted Answer

第一个查询仅对返回的行调用dbo.ufn_RecruiterCompanyName()，它过滤了存储的值。对于第二个查询，SQL Server需要为所有行调用ufn。根据函数的不同，这可能会导致延迟。

在查询分析器中检查此内容，并尝试避免第二个查询^^

在查看自定义函数后，建议使用连接表重写该视图。当在这些函数中进行查找时，SQL Server会为其触及或传递的每一行调用它们。使用LEFT JOIN可以使服务器更快地使用索引和关键字，并应在不到一秒钟内提供数据。

如果没有所有自定义函数和所有表的定义，我无法为您提供新视图的示例，但它应该看起来像这样：

SELECT
    jobs.Jobid,
    jobstatus.Jobstatus,
    recruiter.Company
FROM jobs
LEFT JOIN jobstatus ON jobs.Jobid = jobstatus.Jobid
LEFT JOIN recruiter ON jobs.Recruiterid = recruiter.Recruiterid