I am having an issue with a SQL query that runs and runs but never completes. The database is an Azure SQL database.
My table has tens of millions of rows and looks like this:
CREATE TABLE [dbo].[MyData]( [CustomerId] [int] NOT NULL, [TagName] [nvarchar](100) NOT NULL, [TagValue] [real] NULL, [TimeStamp] [datetime2](7) NOT NULL, [status] [int] NULL, CONSTRAINT [PK_MyData] PRIMARY KEY CLUSTERED ( [CustomerId] ASC, [TagName] ASC, [TimeStamp] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) )
My select statement looks like this:
DECLARE @StartDate as datetime DECLARE @EndDate as datetime Select @StartDate = LastReadDT from MyData_LastDT_Read Select @EndDate = dateadd(minute,30,@StartDate) SELECT CAST([TagName] as varchar(100)) as [tag], CAST(isnull(TagValue, 0) as real) as tagvalue, CAST([TimeStamp] as datetime) as [DataTimeStamp], CAST(192 as int) as [status] FROM [dbo].[MyData] where CustomerID = 1 and TimeStamp <= @EndDate and TimeStamp > @StartDate order by TimeStamp asc
The strange thing is if I remove the
@EndDate variables and instead just hard-code the actual
TimeStamp values in the where clause, the query will return results in less than 1 second. Why would hard-coding the
TimeStamp values in the query make such a dramatic difference compared to the variables? Any suggestions on how to improve the performance? Unfortunately I need to use the variables as this query is executed repeatedly with different time ranges.