亲爱的朋友们,
我有一个程序的性能问题,首先我想解释的过程
我,把我一个邮件列表,在这里查看:
SELECT m.Mail_ID ,
mt.MailTo_ID ,
mt.MailTo_ToType ,
( CASE WHEN mp.MailPass_ID IS NULL
THEN ( CASE WHEN mt.Mail_FromInternal = 0
THEN ( SELECT Name
FROM dbo.Lookups
WHERE ID = m.MailFromID
) + ' -External Mail'
ELSE emFrom.Employee_Name + ' / '
+ hFrom.Hierarchy_Name
END )
ELSE mp.MailsPasses_Sender
END ) AS Sender ,
( CASE WHEN mp.MailPass_ID IS NULL THEN toExternal.Name
ELSE mp.MailsPasses_Reciver
END ) AS Reciver ,
m.Mail_Title ,
ISNULL(SUBSTRING(CONVERT(NVARCHAR, Mail_ContentSize / 1024.0 / 1024.0),
0,
CHARINDEX('.',
CONVERT(NVARCHAR, Mail_ContentSize / 1024.0
/ 1024.0) + 2.0, 0) + 3), '0.00') + ' M.B' AS MailContentSize ,
( CASE WHEN ( SELECT Configuration_Value
FROM [Configurations]
WHERE Configuration_ID = 14
) = 'True' THEN CONVERT(NVARCHAR ,m.Mail_SenderDate,131)
ELSE CONVERT(NVARCHAR,m.Mail_SenderDate)
END ) AS Mail_SenderDate ,
emFrom.Employee_ID AS FromEmployeeID ,
emFrom.Hierarchy_ID AS FromHierarchyID ,
toExternal.ID AS ToHierarchyID ,
NULL AS ToEmployeeID ,
( SELECT dbo.[fn_GetSenderFirstReciever](emFrom.Hierarchy_ID)
) AS SenderFirstReciever ,
NULL AS RecieverFirstReciever ,
ISNULL(priority.Name, 'No Data') AS PriorityName ,
ISNULL(mailStatus.Name, 'No Data') AS MailStatusName ,
Mail_Owner_Name ,
Mail_Owner_Mobile_Number ,
Mail_ReadDate ,
Mail_Body ,
Mail_BodyPlanText ,
Mail_StatusID ,
Mail_Priorty_ID ,
Mail_Owner_Send_SMS ,
m.Mail_Status ,
mt.Employee_Folder_ID ,
m.Mail_Catigory_ID ,
m.Mail_General_ID ,
m.Mail_PublicID ,
m.Mail_ParentMailID ,
m.Process_ID ,
mp.MailPass_ID ,
mp.MailPass_ByEmployeeID ,
mp.MailPass_Hierarchy_ID ,
mp.MailPass_IsPublished ,
mp.MailPass_Note ,
mp.MailPass_PublishedByEmployeeID ,
mp.MailPass_PublishingDate ,
mp.MailPass_ToEmployeeID ,
mp.MailPass_Type ,
mp.MailPass_ReadDate ,
mp.MailPass_Rank ,
mp.MailPass_IsInernal ,
mp.MailTo_ID AS MailPass_MailTo_ID ,
mpp.MailsPassesProccess_Name AS LastActionProcess ,
m.IdentificationNo
FROM dbo.Mails m
INNER JOIN dbo.MailsTo mt ON m.Mail_ID = mt.Mail_ID
INNER JOIN dbo.Employees emFrom ON emFrom.Employee_ID = m.Employee_ID
INNER JOIN dbo.Hierarchy hFrom ON hFrom.Hierarchy_ID = emFrom.Hierarchy_ID
INNER JOIN dbo.Lookups toExternal ON toExternal.ID = mt.MailTo_ToID
LEFT JOIN dbo.Lookups priority ON priority.ID = m.Mail_Priorty_ID
LEFT JOIN dbo.Lookups mailStatus ON mailStatus.ID = m.Mail_StatusID
LEFT JOIN vwMailsPasses mp ON mp.Mail_ID = mt.Mail_ID
AND ( mp.MailPass_Rank = ( SELECT
MAX(MailPass_Rank)
FROM
dbo.MailsPasses
WHERE
MailTo_ID = mp.MailTo_ID
)
OR mp.MailPass_Rank IS NULL
)
LEFT JOIN dbo.MailsPassesProccess mpp ON mpp.MailsPassesProccess_ID = m.MailsPassesProccess_ID
WHERE mt.MailTo_ToType = 2
没有,我要选择这一观点在某些情况下的数据,如:
{C}
问题是我需要运行此功能,以确保该行出口从层次前该功能根据发件人层次包含递归:
如何增加这个程序的功能??
CREATE FUNCTION [dbo].[fn_GetCTSHierarchyIDUnderHierarchyIDWithoutTheSenderNode]
(
@Hierarchy_ID BIGINT ,
@senderHierarchyID BIGINT
)
RETURNS BIGINT
AS
BEGIN
DECLARE Hierarchy_ID CURSOR
FOR SELECT Hierarchy_ID
FROM dbo.Hierarchy
WHERE HierarchyParent_ID = @Hierarchy_ID AND Hierarchy_IsDeleted=0
OPEN Hierarchy_ID
FETCH NEXT FROM Hierarchy_ID
INTO @Hierarchy_ID
WHILE @@FETCH_STATUS = 0
BEGIN
IF ( @Hierarchy_ID IS NOT NULL )
BEGIN
IF EXISTS ( SELECT Hierarchy_ID
FROM dbo.Hierarchy
WHERE Hierarchy_ID = @Hierarchy_ID
AND Hierarchy_IsCTS = 1
AND Hierarchy_ID IN (
SELECT i
FROM dbo.fn_GetHierarchiesUpperHierarchyID(@senderHierarchyID) ) )
RETURN @Hierarchy_ID
ELSE
SET @Hierarchy_ID = ( SELECT dbo.[fn_GetCTSHierarchyIDUnderHierarchyIDWithoutTheSenderNode](@Hierarchy_ID,
@senderHierarchyID)
)
END
FETCH NEXT FROM Hierarchy_ID INTO @Hierarchy_ID
END
IF EXISTS ( SELECT Hierarchy_ID
FROM dbo.Hierarchy
WHERE Hierarchy_ID = @Hierarchy_ID
AND Hierarchy_IsCTS = 1 )
RETURN @Hierarchy_ID
ELSE
BEGIN
SET @Hierarchy_ID = NULL
END
RETURN @Hierarchy_ID
END
如果这还不够了解,请给我评论,我会更新
最好的问候
AB Abulubbad