返回首页

亲爱的朋友们,

我有一个程序的性能问题,首先我想解释的过程

我,把我一个邮件列表,在这里查看:


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

回答

评论会员:游客 时间:2012/02/06
FastEvo8:我注意到的第一个问题是你是你的函数中使用游标,游标,您将需要转换的功能,用一个CTE递归查询(公用简单低效和缓慢。表表达式)是什么类型的数据模型使用您的层次结构(邻接)
?RaisKazi
评论会员:游客 时间:2012/02/06
性能优化并不总是具有确切的答案。但这里有一些链接,这可以帮助你在你的T-SQL查询的性能调整。imgsrc={A}