peformance tuning of update query

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

General SQL Server Forums

New to SQL Server Administration

peformance tuning of update query

Author

Topic

nitinsomal
Starting Member

4 Posts

Posted - 2013-12-26 : 07:58:18

update #T_EquipmentSchedule
set OffTimeCodes = dbo.fn_GetResourceActivityCodes(@scenarioID, @scheduleTypeID, #T_EquipmentSchedule.EquipmentID, #T_EquipmentSchedule.ScheduleDate)

When i try to update #T_EquipmentSchedule temporary table it is taking too much time, how i can improve the performance of query
Note:
dbo.fn_GetResourceActivityCodes(@scenarioID, @scheduleTypeID, #T_EquipmentSchedule.EquipmentID, #T_EquipmentSchedule.ScheduleDate)

is working fine

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2013-12-26 : 12:40:22

You'll need to post the code of the function for us to help.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-12-26 : 13:05:36

Why do need a function here? if your attempt is to concatenate values from multiple rows baed on matching value, see this
http://sqlblog.com/blogs/adam_machanic/archive/2006/07/12/rowset-string-concatenation-which-method-is-best.aspx

My preferred one is FOR XML PATH method discussed in comments section.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

nitinsomal
Starting Member

4 Posts

Posted - 2013-12-27 : 08:22:44

quote:
Originally posted by tkizer

You'll need to post the code of the function for us to help.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

ALTER FUNCTION [dbo].[fn_GetResourceActivityCodes]
(
@scenarioID numeric(18,0),
@equipmentScheduleTypeID int,
@equipmentID numeric(18,0),
@scheduleDate date
)
RETURNS nvarchar(max)
AS
BEGIN

--
declare @activityCodes nvarchar(max)
declare @materialCode_Lunch nvarchar(50) = '80'
declare @materialID_Lunch numeric(18,0)
--

-- get the lunch material ID
select @materialID_Lunch = M.MaterialID
from dbo.Material (nolock) M
where M.ScenarioID = @scenarioID and
M.MaterialCode = @materialCode_Lunch
--

--
select @activityCodes = substring (
(select distinct
',' + dbo.fn_GetCSAActivityCode(@scenarioID, ES.MaterialID) as [text()]
from EquipmentSchedule (nolock) ES
where ES.ScenarioID = @scenarioID and
EquipmentScheduleTypeID = @equipmentScheduleTypeID and
MaterialID not in (@materialID_Lunch) and
cast(StartDateTime as date) = @scheduleDate and
equipmentid = @equipmentID
order by 1 desc
for xml path('')),
2,
8000)
--
return @activityCodes
END

Note: It is workin fine

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-12-28 : 01:42:01

why do you need nested UDFs here? whats fn_GetCSAActivityCode doing?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

nitinsomal
Starting Member

4 Posts

Posted - 2013-12-28 : 05:37:11

to retrive the ActivityCodes

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-12-28 : 09:48:27

quote:
Originally posted by nitinsomal

to retrive the ActivityCodes

Why cant that be done inline by means of correlated query?
using nested UDFs will have an impact on performance especially for large datasets

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Kristen
Test

22859 Posts

Posted - 2014-01-02 : 07:30:27

I don't know if it is the issue you are facing - i.e. whether just calling the Function ONCE is slow?

We have a function that was called very VERY often in our code. It did a relatively simple query, but the query was such that it was recompiled often / every time the function was called. The recompile caused the function to BLOCK, and that caused other function calls to be queued, and that killed performance in our APP.

I went off using Function to "wrap" simple queries after that!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources