December 12, 2013 at 7:44 am
Hi folks,
Please I need to know what is wrong with this query. I have a scalar function as below:
CREATE FUNCTION [dbo].[BT_Clean_missing_hrs]
(
-- Add the parameters for the function here
@empl_uno int,
@startDate datetime,
@enddate datetime
)
RETURNS Money
AS
BEGIN
-- Declare the return variable here
DECLARE @Result Money
;With Resultset (result,starting_date,end_date) as (
SELECT distinct isnull(dbo.BT_get_missing_hrs ( @Empl_uno, dateadd(dd,0-datepart(dw,tran_date),tran_date),dateadd(dd,7-datepart(dw,tran_date),tran_date)),0) missing, dateadd(dd,0-datepart(dw,tran_date),tran_date) starting_date, dateadd(ss,-1,dateadd(dd,7-datepart(dw,tran_date),tran_date)) end_date
from BO_LIVE2.dbo.tat_time
where tran_date between @startDate and @endDate
and datepart(dw,tran_date)=3)
--Add the T-SQL statements to compute the return value here
select @Result = sum( result) from Resultset t where t.end_date>@startDate and t.starting_date < @endDate
-- Return the result of the function
RETURN @Result
END
GO
Now I want this query Below to utilise this function but my query is running forever. Where could be the problem?
declare @startdate datetime,
@enddate datetime,
@employees nvarchar (max)
set @startdate = '2013/11/01'
set @enddate = '2013/11/30'
set @employees = 1111
select '00-MISSING','MISSING_HOURS', BO_CUSTOM.dbo.BT_clean_MISSING_HRS(empl.empl_uno,@startDate,@endDate)
from hbm_persnl empl
where empl.empl_uno in (@Employees) and BO_CUSTOM.dbo.BT_clean_MISSING_HRS(empl.empl_uno,@startDate,@endDate) > 0
Thanks
EO
December 12, 2013 at 7:54 am
This is the problem with scalar functions. They run once for every row. If you convert to a table valued function, you'd probably see a significant improvement in performance.
John
December 12, 2013 at 7:56 am
Scalar functions are notoriously poor for performance. You have a scalar function nested inside of a scalar function. Then you call that same scalar not once but twice in your query. This has completely crippled any chance at all of having anything resembling decent performance. You will be calling this scalar function twice for each and every single row in your base table.
Normally for performance issues we want to see execution plans but in this case there is no need. Your sql needs a complete rewrite to get rid of the scalar functions. The one you posted looks like we can fairly easily convert it to an iTVF (inline table value function) which will greatly help performance. I will be happy to help you but you need to post details of your tables and an explanation of what that function is doing. I suspect the nest function can also be converted to an iTVF but I would need to see that code and any table definitions there too.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
December 12, 2013 at 8:29 am
And here's three. Likely your query is extremely slow for the reasons mentioned above.
In general Scalar Functions that have their own queries inside should not be used.
December 13, 2013 at 10:01 am
Without knowing what is in the embedded scalar function, my first thought is why don't you simply do a SELECT? if you want to hide SQL from the user, why not do a stored procedure that accepts the same parameters and selects out a row?
All of the other commenters are correct. Scalar functions are horrible performers, and nesting them can geometrically increase latency.
Thanks
John.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply