Optimizing a sql function - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Optimizing a sql function

Author

Topic

willfindavid
Starting Member

27 Posts

Posted - 2008-06-26 : 07:54:01

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

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-06-26 : 07:56:25

[code]DECLARE @Result varchar(500)

Select
@Result = coalesce(@Result + ';', '') + ltrim(rtrim(RoleName))
from ...[/code]

Note:If RoleName column is varchar, you don't need RTRIM() function either.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

willfindavid
Starting Member

27 Posts

Posted - 2008-06-26 : 08:03:27

It takes the same time of 30 seconds to process this function.
There are more than 500 records sent to the function as input.

Willfin

willfindavid
Starting Member

27 Posts

Posted - 2008-06-26 : 08:07:36

The function is called like this:

SELECT TOP (100) PERCENT dbo.PCProcessPackages.ProliteProjectID, dbo.PCTasks.ProcessPackageID,
dbo.PC_fnDeliveryRolesApplicable(dbo.PCProcessPackageTasks.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

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-06-26 : 08:08:29

How this function is called? What is the query which uses this function?

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

willfindavid
Starting Member

27 Posts

Posted - 2008-06-26 : 08:13:30

I've mentioned it above.

willfindavid
Starting Member

27 Posts

Posted - 2008-06-26 : 08:24:59

The function is not displaying 500 records at a stretch. First it displays 50 records then processes and then displays another 200 records... ..

Wats the reason for this delay?

Willfin

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources