Results 1 to 12 of 12
  1. #1
    Join Date
    Jun 2008
    Posts
    22

    Unanswered: Optimizing a sql function

    ALTER function [dbo].[PC_fnDeliveryRolesApplicable]
    (
    @TaskID as Bigint,
    @PCProcessPackageID as Bigint
    )
    RETURNS varchar(500)
    As
    Begin

    DECLARE @Result varchar(500)
    set @Result =''


    Select
    @Result = @Result + case when @Result = '' then ltrim(rtrim(RoleName))
    else ';'+ ltrim(rtrim(RoleName)) end
    from
    PCRoles
    Inner Join
    PCTaskRoles
    On
    PCRoles.RoleID = PCTaskRoles.RoleID
    PCProcessPackageTasks.TaskID
    where
    PCTaskRoles.PCProceessPackageTaskID = @TaskID
    and PCRoles.ProcessPacakgeID = @PCProcessPackageID order by RoleName


    RETURN(@Result)

    End

  2. #2
    Join Date
    Jun 2008
    Posts
    22

    Function Call

    The function is called like this:

    SELECT TOP (100) PERCENT dbo.PCProcessPackages.ProliteProjectID, dbo.PCTasks.ProcessPackageID,
    dbo.PC_fnDeliveryRolesApplicable(dbo.PCProcessPack ageTasks.TaskID, dbo.PCTasks.ProcessPackageID) AS Roles,
    dbo.PCProcessPackageTasks.TaskID
    FROM dbo.PCProcessPackageTasks INNER JOIN
    dbo.PCTasks ON dbo.PCProcessPackageTasks.PCTaskID = dbo.PCTasks.TaskID INNER JOIN
    dbo.PCProcessPackages ON dbo.PCProcessPackages.ProcessPackageID = dbo.PCTasks.ProcessPackageID


    Willfin

  3. #3
    Join Date
    Jun 2008
    Posts
    22
    It takes a time of 30 seconds to process this function.
    There are more than 500 records sent to the function as input.

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Please copy and paste the exact function. That will not compile.

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Also - SQL 2k or SQL 2k5?

  6. #6
    Join Date
    Jun 2008
    Posts
    22
    It is SQL 2005

  7. #7
    Join Date
    Jun 2008
    Posts
    22
    ALTER function [dbo].[PC_fnDeliveryRolesApplicable]
    (
    @TaskID as Bigint,
    @PCProcessPackageID as Bigint
    )
    RETURNS varchar(500)
    As
    Begin

    DECLARE @Result varchar(500)
    set @Result =''


    Select
    @Result = @Result + case when @Result = '' then ltrim(rtrim(RoleName))
    else ';'+ ltrim(rtrim(RoleName)) end
    from PCRoles Inner Join PCTaskRoles
    On PCRoles.RoleID = PCTaskRoles.RoleID
    where
    PCTaskRoles.PCProceessPackageTaskID = @TaskID
    and PCRoles.ProcessPacakgeID = @PCProcessPackageID order by RoleName


    RETURN(@Result)

    End

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    For this query ONLY nonclustered indexes on:
    PCRoles(ProcessPacakgeID, RoleID) with RoleName as an Includes column.
    and
    PCTaskRoles(PCProceessPackageTaskID, RoleID)
    would be optimal I think. Ideally, you would lose the order by clause.

    Explicitly set as unique if they are unique.

  9. #9
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    500 records? Your @Result variable accumulates concatenated value of RoleName field. Is it 1 character wide?
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  10. #10
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Good spot Robert - although I read that as meaning "the function is called 500 times", rather than "the function operates on 500 records each time it is called"

  11. #11
    Join Date
    Jun 2008
    Posts
    22
    Thanks a lot dude, it works after creating nonclustered indexes...
    Grt...

  12. #12
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Don't forget, of course, you should review all your indexes in light of the other indexes and all mdoification\ delete\ insert operations.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •