How to resolve execution timeout expired issue occuring in a stored procedure-CodePudding

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?

CodePudding user response：

The use of IN in your function can be replaced by a JOIN.

Your query :

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);

The rewriting :

SELECT @tdaycount = COUNT(DISTINCT(CONVERT(DATE, tdays)))
FROM   tbl_holidays  AS h
       JOIN tbl_code  AS c
          ON h.tcode = c.id
WHERE    (CONVERT(DATE, tdays, 105) >= CONVERT(DATE, @startdate, 105))
          AND (CONVERT(DATE, tdays, 105) <= CONVERT(DATE, @enddate, 105))
          AND  c.id = @ccode;

When A = B and B = C the A = C, so :

...
              ON h.tcode = c.id
    WHERE    ...
              AND  c.id = @ccode;

The rewriting (2) :

SELECT @tdaycount = COUNT(DISTINCT(CONVERT(DATE, tdays)))
FROM   tbl_holidays  AS h
WHERE    (CONVERT(DATE, tdays, 105) >= CONVERT(DATE, @startdate, 105))
          AND (CONVERT(DATE, tdays, 105) <= CONVERT(DATE, @enddate, 105))
          AND  tbl.tcode = @ccode;

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...

CodePudding user response：

Your function contains this bit of SQL.

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)

Date-conversion error

CONVERT(DATE, @enddate, 105) produces a date of the form 31-12-2006. I suspect it makes no sense to use <= comparisons on that format because 30-12-2007 will come before 31-12-2006. Your data is already in the DATE format, so you can compare it without converting it.

Sargability performance antipattern

It contains a couple of performance antipatterns like this:

WHERE function(column) >= function(constant)

This is an antipattern because it's a non-sargable filter. What you want is this sort of pattern

WHERE column >= constant

WHERE column >= function(constant)

combined with an apppropriate index on the table.

Index

A multicolumn index on your tbl_holidays table on (tcode, tdays) will allow the server to satisfy your queries by range-scanning that index. Try this.

CREATE INDEX tcode_tdays ON tbl_holidays (tcode, tdays);

Rewrite

SELECT @tdaycount = COUNT(DISTINCT tdays) 
  FROM tbl_holidays WITH (NOLOCK)
 WHERE tdays >= @startdate
   AND tdays <= @enddate
   AND tcode in (SELECT id 
                   FROM tbl_code WITH (NOLOCK) 
                  WHERE id = @ccode)

I don't know how much time this will save. But it will save some.

Default timeout too short

.NET's SQL classes are set up with 30-second timeouts. If you're processing millions (lakhs upon lakhs) of rows that's not long enough. Try setting the timeout to ten minutes or whatever duration makes sense.

Dim command = conn.CreateCommand()
...
command.CommandTimeout = 600  ' ten minutes