如果你知道SQL优化这个脚本

问题描述

请不要因为性能问题而使用Temptable，我有一个卡片的时间我想在某个生效日期找到相关人员。每个人员都可以拥有多个不同的有效日期的卡片没有

please Dont Use Temptable Because Of Performance Issue ، I have A time With A cardNo that i want to found Related Personnel Base On some Effective Date. each Personnel can have Multiple EffectiveDate With Diffrent CardNo

<pre>CREATE PROCEDURE [dbo].[PersonnelDetails_GetPersonnelByCardNoAndTime]
(
@CardNo VARCHAR(16),
@Time SMALLDATETIME
)
AS
DECLARE @PersonnelBaseID INT;
DECLARE @EffectiveDate SMALLDATETIME;

SELECT EffectiveDate,PersonnelBaseID,CardNo INTO #PersonnelDetails FROM
(
	SELECT
		personnelDetails.EffectiveDate, personnelDetails.PersonnelBaseID, CardNo
	FROM
		dbo.tkp_PersonnelDetails AS personnelDetails
	INNER JOIN
	(
		SELECT
				MAX(EffectiveDate) AS EffectiveDate,details.PersonnelBaseID
		FROM
			dbo.tkp_PersonnelDetails AS details INNER JOIN
			prs_Personnel personnel ON personnel.ID = details.PersonnelBaseID AND
			Personnel.Active = 1 AND
			Personnel.Deleted = 0 AND
			details.Deleted = 0 AND
			details.EffectiveDate <= @Time
		GROUP BY PersonnelBaseID
	)result ON (result.PersonnelBaseID = personnelDetails.PersonnelBaseID AND result.EffectiveDate = personnelDetails.EffectiveDate AND personnelDetails.Deleted = 0  AND CardNo = @CardNo)
)details
WHERE
	CardNo = @CardNo
ORDER BY details.Effectivedate DESC

SELECT TOP 1
	@PersonnelBaseID = PersonnelBaseID ,
	@EffectiveDate = EffectiveDate,
	@CardNo = CardNo
FROM
	#PersonnelDetails
ORDER BY Effectivedate DESC
 IF ((SELECT COUNT(DISTINCT PersonnelBaseID) FROM #PersonnelDetails WHERE EffectiveDate = @EffectiveDate AND CardNo = @CardNo) > 1)--handle Multi Personnel With On CardNo In same EffectiveDate :(
	 SELECT 0;
 ELSE
	SELECT @PersonnelBaseID;
DROP TABLE #PersonnelDetails

我尝试了什么：



请不要因为性能问题而使用Temptable，我有一个时间没有卡片没有我希望在某个生效日期找到相关人员基础。每个人员可以有多个有效日期与不同的CardNo

What I have tried:

please Dont Use Temptable Because Of Performance Issue ، I have A time With A cardNo that i want to found Related Personnel Base On some Effective Date. each Personnel can have Multiple EffectiveDate With Diffrent CardNo

推荐答案

这篇关于如果你知道SQL优化这个脚本的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！