CODEWITHSUNDEEP
sqlsql-serverperformancequery-optimizationsql-function
Adel
Adel

Reputation: 17

When i call this function for 700k record it is very slow

When I call this function for 700k rows it is very slow.
This function calculates the number of holidays between two dates.

ALTER FUNCTION [dbo].[HolyCount] 
(
    
    @StartTime datetime,
    @EndTime datetime
)
RETURNS int 
AS
BEGIN
    
    DECLARE  @Holy int =0
            ,@DiffDate datetime ;

while(@EndTime >=@StartTime)
begin 
if (cast (@EndTime as date) in (select cast([georgian_date] as date) from [StageArea_Tickets].[dbo].[SW_Calendar] where [shamsi_state]='holiday'))
set @Holy = @Holy+1
else
set @Holy = @Holy+0
set @EndTime = Dateadd(Day,-1,@EndTime)
end
    
    RETURN @Holy

END

Upvotes: -4

Views: 87

Answers (2)

Abdul Alim Shakir
Abdul Alim Shakir

Reputation: 1227

Try to avoid running loop in functions.

ALTER FUNCTION [dbo].[HolyCount] 
(
    @StartTime DATETIME,
    @EndTime DATETIME
)
RETURNS INT 
AS
BEGIN
IF @StartTime > @EndTime
BEGIN
    RETURN 0;
END

DECLARE  @Holy int =0;

SELECT @Holy = COUNT([georgian_date])
FROM [StageArea_Tickets].[dbo].[SW_Calendar]
WHERE [shamsi_state] = 'holiday'
AND [georgian_date] BETWEEN CAST(@StartTime AS DATE) AND CAST(@EndTime AS DATE);

RETURN @Holy;
END

Ensure that [georgian_date] is indexed properly. It is highly recommended that use DATE instead of DATETIME in the function so casting will not be required.

It will be more faster if table valued function is used. Holiday data will be kept in memory when traversing 700K records.

Upvotes: 0

Thom A
Thom A

Reputation: 95924

If I understand correctly, couldn't this just be completely rewritten to a single COUNT?

DROP FUNCTION IF EXISTS [dbo].[HolyCount] ; --Bceause you can't change a scalar UDF to a TVF.
GO
CREATE OR ALTER FUNCTION [dbo].[HolyCount] (@StartDate date, @EndDate date)
RETURNS table AS
    RETURN SELECT COUNT(DISTINCT georgian_date) AS Holidays --DISTINCT as I assume you can have the date in there multiple times, if not, remove the DISTINCT
           FROM dbo.SW_Calendar
           WHERE shamsi_state = 'Holiday'
             AND georgian_date >= @StartDate
             AND georgian_date <= @EndDate;
GO

Then you can just reference the function is the FROM:

SELECT ...,
       HC.Holidays
FROM dbo.YourTable YT
     CROSS APPLY dbo.HolyCount (YT.StartDate, YT.EndDate) HC;

Upvotes: 5

Related Questions