gpt4 book ai didi

sql - 查询调整 SQL Server 2008

转载 作者:行者123 更新时间:2023-12-04 23:43:40 25 4
gpt4 key购买 nike

我有一个 View 名称“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

此 View 仅包含 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

我的问题是

  1. Why it is taking so much time to execute?
  2. How can I reduce the execution time?

非常感谢您的关注。

最佳答案

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

在查询分析器中检查这个,并尽量避免第二个查询 ^^

查看自定义函数后,我建议使用连接表重写该 View 。在此类函数中进行查找时,SQL Server 会为它接触或传递的每一行调用它们。使用 LEFT JOIN 允许服务器更快地使用索引和键,并且应该在不到一秒的时间内交付数据。

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

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

关于sql - 查询调整 SQL Server 2008,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/5565664/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com