Facing timeout expired issue in a code developed. Shared below is the stored procedure where timeout occurs. Purpose of the code : Dates being passed from frontend (using a forloop in Windows application vb.net code) for 1 million cases for which date difference needs to be calculated basis the date received.
create procedure sp_getdatediff
@strd1 date = null,
@strd2 date = null,
@strd3 date = null,
@strd4 date = null,
@strd5 date = null,
@strd6 date = null,
@strd7 date = null,
@strd8 date = null,
@strd9 date = null,
@strd10 date = null,
@strd11 date = null
as
begin
declare @vardatediff1 int
declare @vardatediff2 int
declare @vardatediff3 int
declare @vardatediff4 int
declare @vardatediff5 int
set @vardatediff1 = [fn_getdiff](@strd1,@strd2,@strd3) ----- input parameters are dates passed from frontend
set @vardatediff2 = [fn_getdiff](@strd2,@strd4,@strd5)
set @vardatediff3 = [fn_getdiff](@strd4,@strd5,@strd6)
set @vardatediff4 = [fn_getdiff](@strd5,@strd7,@strd8)
set @vardatediff5 = [fn_getdiff](@strd9,@strd10,@strd11)
update tbl_Scheduler set col_dif1 = @vardatediff1 , col_dif2 = @vardatediff2 ,
col_dif3 = @vardatediff3 , col_dif4 = @vardatediff4 , col_dif5 = @vardatediff5
where id = @id
end
Function code :
create function [fn_getdiff]
(
@startdate date = null,
@enddate date = null,
@ccode varchar(10) = null
)
returns integer
as
begin
declare @count integer
declare @tdaycount integer
if (@startdate is null or @startdate = '')
begin
set @count = 0
end
else if (@enddate is null or @enddate = '')
begin
set @count = 0
end
else
begin
select @tdaycount = count(distinct(convert(date,tdays))) from tbl_holidays with (nolock) where (convert(date,tdays,105) >= convert(date,@startdate,105))
and (convert(date,tdays,105) <= convert(date,@enddate,105)) and tcode in (select id from tbl_code with (nolock) where id = @ccode)
select @count = datediff(@startdate,@enddate)
set @count = @count - @tdaycount
end
return @count
end
Is there optimization required in this code to eliminate timeout issue? How can same be done?
The use of IN in your function can be replaced by a JOIN.
Your query :
The rewriting :
When A = B and B = C the A = C, so :
The rewriting (2) :
You do not respects rules of posting... So we cannot helps you so much. Put the description of your tables and indexes as DDL SQL Statement please. Probably the CONVERTs are useless...