I have a query that selects a pair of rows of a view based on the declared variables. I ran the same query in lower environments and, to my surprise, I got the result in less than 3 minutes, whereas when I ran the query in the production environment, the query was never completed.
DECLARE @TodayDt INTEGER, @YestDt INTEGER; SET @TodayDt = (SELECT the date FROM the calendar WHERE Date_Gate = DATEADD (DAY, -365, CAST (FLOOR (CAST (GETDATE () AS FLOAT)))) SET @YestDt = (SELECT the date FROM the calendar WHERE Calendar_Date = CAST (FLOOR (CAST (GETDATE () AS FLOAT)) AS DATETIME)); SELECT Cast (Emp_Name as varchar (10)), CAST (Emp_Division AS VARCHAR (3)), Cast (Emp_Sales as varchar (10)), Cast (Emp_Join_Dt as int) FROM dbo.View_Employee_Records WHERE Emp_ID EN (& # 39; 281 & # 39 ;, & # 39; 274 & # 39;) AND JoinDt> = @TodayDt And ResignDt <= @YestDt
Also, if I run my query without variables in the production environment, it gives me results in much less time. Can anyone suggest me a possible cause and resolution for this problem.