sql server 2008 - Decrease the execution time of the query

Question

I write a query the result of the query is fine but issue with the time that is taking to complete the execution. For increase the performance i create index as per my query so this decrease from 5 minutes to 0.44 seconds. But i want to make this more faster. The issue i release with my function that i comment then its execution time is 0.00 but the function i am using is important for me. Is any option to decrease my execution time of my query .

**My function*

ALTER FUNCTION [dbo].[fnCount]

(

      @StartDate DATE,@EndDate DATE,@DropDate DATE

)

RETURNS INT     
AS    
BEGIN    
      DECLARE @WeekStartDate DATE    
      DECLARE @WeekEndDate DATE    
      DECLARE @Count AS INT    
      SET @Count=0    
      SET @WeekStartDate=CONVERT(DATE,DATEADD(dd, -(DATEPART(dw, @DropDate)-1), @DropDate))    
      SET @WeekEndDate=CONVERT(DATE,DATEADD(dd, 7-(DATEPART(dw, @DropDate)), @DropDate))          

      IF @StartDate <= @WeekStartDate    
            SET @StartDate=@WeekStartDate    
      IF @EndDate >=@WeekEndDate    
            SET @EndDate=@WeekEndDate  
      SELECT @Count=COUNT(distinct DROPDATE)    
      FROM  orde_    
      WHERE  orde_.CANCELLED = 0     
      AND DROPDATE >= @StartDate AND DROPDATE <= @EndDate    
      AND (DISPID IS NULL OR DISPID NOT IN ( '291', '327') )    
      RETURN @Count

END

Query

    SELECT READYDATE,TOTAL,DRV1PAY,ORDERNUM,
CASE WHEN dbo.fnCount('20170901','20171031',DROPDATE) >=4
THEN  (CONVERT(VARCHAR(4),LEFT(DROPDATE,4))  + '' + CONVERT(VARCHAR(4),DATEPART( wk, DROPDATE)))
ELSE NULL
END AS WEEKOfYear1,
FROM  orde_
WHERE  CANCELLED = 0  AND DROPDATE >='20170901'  AND DROPDATE <='20171031'
AND (DISPID IS NULL OR DISPID NOT IN ( '291', '327') )
ORDER BY DISPID

Thanks for your help and comments

Answer 1

Waitting for answers