Dear All ,
I Have A Problem with the Procedure Performance , First I want To Explain the process
I have View That Get Me a list of Mails Here Its :
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
No I want to select Data From This VIEW in some Cases LIKE :
CREATE TABLE #temp ( hierarcyID BIGINT ) ;
INSERT INTO #temp
SELECT i
FROM dbo.fn_GetCTSHierarchiesUnderHierarchyIDWithoutTheSenderNode(@HierarchyID)
SELECT vm.* ,
me.MailExporter_ID ,
me.Mail_ExportNo ,
me.Mail_Exported ,
me.Hierarchy_ID AS ExportedByHierarchy_ID ,
me.MailExporter_Rank ,
me.Employee_ID AS ExportedByEmployee_ID ,
me.Hidden AS ExportedIsHidden ,
mi.MailImporter_ID ,
mi.Mail_ImportNo ,
mi.Mail_Imported ,
mi.Hierarchy_ID AS ImportedByHierarchy_ID ,
mi.MailImporter_Rank ,
mi.Employee_ID AS ImportedByEmployee_ID ,
mi.Hidden AS ImportedIsHidden
FROM dbo.vwMails vm
LEFT JOIN dbo.MailsExporter me ON vm.MailTo_ID = me.MailTo_ID
AND ( EXISTS ( SELECT
MailExporter_ID
FROM
dbo.MailsExporter
WHERE
Hierarchy_ID = ( SELECT
dbo.[fn_GetCTSHierarchyIDUnderHierarchyIDWithoutTheSenderNode](@HierarchyID,
FromHierarchyID)
)
AND MailTo_ID = vm.MailTo_ID )
OR ( SELECT
dbo.[fn_GetCTSHierarchyIDUnderHierarchyIDWithoutTheSenderNode](@HierarchyID,
FromHierarchyID)
) IS NULL
)
AND ( EXISTS ( SELECT
MailExporter_ID
FROM
dbo.MailsExporter
WHERE
Hierarchy_ID = ( SELECT
dbo.[fn_GetFarestHierarchyIDByHierarchyID](FromHierarchyID)
)
AND MailTo_ID = vm.MailTo_ID )
OR ( SELECT
dbo.[fn_GetFarestHierarchyIDByHierarchyID](FromHierarchyID)
) IS NULL
)
LEFT JOIN dbo.MailsImporter mi ON vm.MailTo_ID = mi.MailTo_ID
AND mi.Hierarchy_ID = @HierarchyID
The Problem is I need To run this function to ensure that the row is exported before from the Hierarchy Under the sender Hierarchy THE FUNCTION IS Contains Recursive:
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
How to increase this Procedure function ?!?
if this not enough to understand please send me a comment and i'll Update
Best Regards
Ab Abulubbad
Submit an article or tip