有没有办法删除middel加入以获得更好的性能？我希望在中间加入后获得cardno字段， [英] Is there a way to remove middel join to better performance ? I want to get cardno field after middle join ,

问题描述

SELECT Top 1  details.PersonnelBaseID FROM dbo.tkp_PersonnelDetails INNER JOIN
(
	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
)details ON tkp_PersonnelDetails.PersonnelBaseID = details.PersonnelBaseId AND tkp_PersonnelDetails.cardNo = @CardNo
ORDER BY tkp_PersonnelDetails.Effectivedate DESC 

我尝试了什么：



有没有办法删除middel加入以获得更好的性能？我想得到CardNo字段加入

What I have tried:

is there a way to remove middel join to better performance ? i want to get CardNo Field After join

推荐答案

认为逻辑不应该改变，你可以对语句进行不同类型的修改但是你不能完全删除连接因为这会影响结果。



例如，一个变体可能是

Taken that the logic should not change, you can make different kinds of modifications of the statement but you cannot completely remove the join since that would affect the results.

For example one variation could be

WITH r1 (
   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
)
SELECT Top 1  details.PersonnelBaseID FROM dbo.tkp_PersonnelDetails INNER JOIN
(
	SELECT personnelDetails.EffectiveDate,personnelDetails.PersonnelBaseID,CardNo 
	FROM dbo.tkp_PersonnelDetails AS personnelDetails 
	INNER JOIN r1 ON r1.PersonnelBaseID = personnelDetails.PersonnelBaseId 
                      AND r1.EffectiveDate = personnelDetails.EffectiveDate
) details ON tkp_PersonnelDetails.PersonnelBaseID = details.PersonnelBaseId 
          AND tkp_PersonnelDetails.cardNo = @CardNo
ORDER BY tkp_PersonnelDetails.Effectivedate DESC 

另一种变体可能是使用EXISTS结构。



但是性能如何，首先要做的是检查是否有所有必要的索引。例如：

- 表prs_Personnel列Id，活动，已删除

- 表tkp_PersonnelDetails列PersonnelBaseID，Deleted，EffectiveDate

Another variation could be to use an EXISTS structure.

But what comes to the performance, the first thing to do is to check if you have all necessary indexes in place. For example something like:
- Table prs_Personnel columns Id, Active, Deleted
- Table tkp_PersonnelDetails columns PersonnelBaseID, Deleted, EffectiveDate

这篇关于有没有办法删除middel加入以获得更好的性能？我希望在中间加入后获得cardno字段，的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！