If you know SQL optimize this script

See more:

SQL

optimization

T-SQL

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

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