I have a query which selects a couple of rows from a view based on the variables declared. 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 on Production environment the query never completed.
DECLARE @TodayDt INTEGER, @YestDt INTEGER; SET @TodayDt = (SELECT date FROM Calendar WHERE Calendar_Date = DATEADD(DAY, -365, CAST(FLOOR(CAST(GETDATE() AS FLOAT)) AS DATETIME))); SET @YestDt = (SELECT date FROM 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 IN ('281','274') AND JoinDt >= @TodayDt AND ResignDt <= @YestDt
Also, If I run my query without variables on Production environment It gives me result in very less time. Can anyone suggest me a possible cause and resolution for this issue.