SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Administration
 peformance tuning of update query
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

nitinsomal
Starting Member

India
4 Posts

Posted - 12/26/2013 :  07:58:18  Show Profile  Reply with Quote

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

USA
36845 Posts

Posted - 12/26/2013 :  12:40:22  Show Profile  Visit tkizer's Homepage  Reply with Quote
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/
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 12/26/2013 :  13:05:36  Show Profile  Reply with Quote
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
Go to Top of Page

nitinsomal
Starting Member

India
4 Posts

Posted - 12/27/2013 :  08:22:44  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 12/28/2013 :  01:42:01  Show Profile  Reply with Quote
why do you need nested UDFs here? whats fn_GetCSAActivityCode doing?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

nitinsomal
Starting Member

India
4 Posts

Posted - 12/28/2013 :  05:37:11  Show Profile  Reply with Quote
to retrive the ActivityCodes
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 12/28/2013 :  09:48:27  Show Profile  Reply with Quote
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
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 01/02/2014 :  07:30:27  Show Profile  Reply with Quote
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!
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000