改进此SQL查询以获得更好的性能
问题描述:
DBA不喜欢我正在使用case语句和子查询。我可以采取另一种方法来提高性能吗?此更新语句是存储过程的一部分。改进此SQL查询以获得更好的性能
UPDATE dbo.CMN_PersonsFerpa
SET
IsWorkEmailFerpa = (CASE WHEN (SELECT EmailType FROM dbo.CMN_PersonsEmailLinks WHERE CMN_PersonsEmailLinksID = @CMN_PersonsEmailLinksID) = 'Work' THEN @IsFERPA ELSE IsWorkEmailFerpa END,
IsPersonalEmailFerpa = (CASE WHEN (SELECT EmailType FROM dbo.CMN_PersonsEmailLinks WHERE CMN_PersonsEmailLinksID = @CMN_PersonsEmailLinksID) = 'Personal' THEN @IsFERPA ELSE IsPersonalEmailFerpa END,
IsParentEmailFerpa = (CASE WHEN (SELECT EmailType FROM dbo.CMN_PersonsEmailLinks WHERE CMN_PersonsEmailLinksID = @CMN_PersonsEmailLinksID) = 'Parent' THEN @IsFERPA ELSE IsParentEmailFerpa END,
IsTempEmailFerpa = (CASE WHEN (SELECT EmailType FROM dbo.CMN_PersonsEmailLinks WHERE CMN_PersonsEmailLinksID = @CMN_PersonsEmailLinksID) = 'Temporary' THEN @IsFERPA ELSE IsTempEmailFerpa END,
IsFAFSAEmailFerpa = (CASE WHEN (SELECT EmailType FROM dbo.CMN_PersonsEmailLinks WHERE CMN_PersonsEmailLinksID = @CMN_PersonsEmailLinksID) = 'FAFSA' THEN @IsFERPA ELSE IsFAFSAEmailFerpa END,
IsCSSProfEmailFerpa = (CASE WHEN (SELECT EmailType FROM dbo.CMN_PersonsEmailLinks WHERE CMN_PersonsEmailLinksID = @CMN_PersonsEmailLinksID) = 'CSS Profile' THEN @IsFERPA ELSE IsCSSProfEmailFerpa END,
IsCommenceEmailFerpa = (CASE WHEN (SELECT EmailType FROM dbo.CMN_PersonsEmailLinks WHERE CMN_PersonsEmailLinksID = @CMN_PersonsEmailLinksID) = 'Commencement' THEN @IsFERPA ELSE IsCommenceEmailFerpa END,
IsAcctHoldEmailFerpa = (CASE WHEN (SELECT EmailType FROM dbo.CMN_PersonsEmailLinks WHERE CMN_PersonsEmailLinksID = @CMN_PersonsEmailLinksID) = 'Account Holder' THEN @IsFERPA ELSE IsAcctHoldEmailFerpa END
Where CMN_PersonsFerpa.cmn_personsID = (select cmn_personsID from CMN_PersonsEmailLinks WHERE CMN_PersonsEmailLinksID = @CMN_PersonsEmailLinksID)
在这里有几个建议是我有。这可以接受吗?还是有更好的办法?:
SELECT @DBType = EmailType, @cmn_personsID = CMN_PersonsID FROM dbo.CMN_PersonsEmailLinks WHERE CMN_PersonsEmailLinksID = @CMN_PersonsEmailLinksID
UPDATE dbo.CMN_PersonsFerpa
SET
IsWorkEmailFerpa = CASE WHEN @DBType = 'Work' THEN @IsFERPA END,
IsPersonalEmailFerpa = CASE WHEN @DBType = 'Personal' THEN @IsFERPA END,
IsParentEmailFerpa = CASE WHEN @DBType = 'Parent' THEN @IsFERPA END,
IsTempEmailFerpa = CASE WHEN @DBType = 'Temporary' THEN @IsFERPA END,
IsFAFSAEmailFerpa = CASE WHEN @DBType = 'FAFSA' THEN @IsFERPA END,
IsCSSProfEmailFerpa = CASE WHEN @DBType = 'CSS Profile' THEN @IsFERPA END,
IsCommenceEmailFerpa = CASE WHEN @DBType = 'Commencement' THEN @IsFERPA END,
IsAcctHoldEmailFerpa = CASE WHEN @DBType = 'Account Holder' THEN @IsFERPA END,
LastChangeBy = @UserGUID,
LastChangeDateTime = GETDATE()
Where CMN_PersonsFerpa.cmn_personsID = @CMN_PersonsID
答
目标是消除子查询。您可以通过在更新之前构建临时表或使用适当的链接来完成此操作。不知道你的数据模式很难帮你设计一些东西,但这里是一个镜头:
update p set
IsWorkEmailFerpa = case when e.EmailType = 'Work' then @IsFERPA else IsWorkEmailFerpa end,
IsPersonalEmailFerpa = case when e.EmailType = 'Personal' then @IsFERPA else IsPersonalEmailFerpa end,
IsParentEmailFerpa = case when e.EmailType = 'Parent' then @IsFERPA else IsParentEmailFerpa end,
IsTempEmailFerpa = case when e.EmailType = 'Temporary' then @IsFERPA else IsTempEmailFerpa end,
...
IsAcctHoldEmailFerpa = case when e.EmailType = 'Account Holder' then @IsFERPA else IsAcctHoldEmailFerpa end,
from dbo.CMN_PersonsFerpa p
join dbo.CMN_PersonsEmailLinks e
on e.cmn_personsID = p.cmn_personsID
where e.CMN_PersonsEmailLinksID = @CMN_PersonsEmailLinksID
答
而不是运行查询8次,运行它一旦进入一个变量,该变量本身的执行情况。
会是这个样子:
SELECT @EmailType = EmailType FROM dbo.CMN_PersonsEmailLinks WHERE CMN_PersonsEmailLinksID = @CMN_PersonsEmailLinksID
UPDATE dbo.CMN_PersonsFerpa
SET
IsWorkEmailFerpa = (CASE WHEN @EmailType = 'Work' THEN @IsFERPA ELSE IsWorkEmailFerpa END,
IsPersonalEmailFerpa = (CASE WHEN @EmailType = 'Personal' THEN @IsFERPA ELSE IsPersonalEmailFerpa END,
IsParentEmailFerpa = (CASE WHEN @EmailType = 'Parent' THEN @IsFERPA ELSE IsParentEmailFerpa END,
IsTempEmailFerpa = (CASE WHEN @EmailType = 'Temporary' THEN @IsFERPA ELSE IsTempEmailFerpa END,
IsFAFSAEmailFerpa = (CASE WHEN @EmailType = 'FAFSA' THEN @IsFERPA ELSE IsFAFSAEmailFerpa END,
IsCSSProfEmailFerpa = (CASE WHEN @EmailType = 'CSS Profile' THEN @IsFERPA ELSE IsCSSProfEmailFerpa END,
IsCommenceEmailFerpa = (CASE WHEN @EmailType = 'Commencement' THEN @IsFERPA ELSE IsCommenceEmailFerpa END,
IsAcctHoldEmailFerpa = (CASE WHEN @EmailType = 'Account Holder' THEN @IsFERPA ELSE IsAcctHoldEmailFerpa END
Where CMN_PersonsFerpa.cmn_personsID = (select cmn_personsID from CMN_PersonsEmailLinks WHERE CMN_PersonsEmailLinksID = @CMN_PersonsEmailLinksID)
+0
-1如果EmailType在SELECT之后但UPDATE之前更改,会怎么样? – Anon 2014-09-24 22:06:33
答
你为什么不使用连接?相关的子查询像光标一样一行一行地工作,几乎从不应该使用。
@CMN_PersonsEmailLinksID从哪里来?它是否从CMN_PersonsFerpa中拉出来? – Malk 2014-09-24 21:00:38
@CMN_PersonsEmailLinksID来自前端而不是任何表。 – NonProgrammer 2014-09-24 21:04:58