主页 / user-127231

Roger's questions

Martin Hope
Roger
Asked: 2017-06-22 08:46:56 +0800 CST
  • 2

我在运行并运行但从未完成的 SQL 查询时遇到问题。该数据库是 Azure SQL 数据库。

我的表有数千万行，看起来像这样：

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

我的选择语句如下所示：

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

奇怪的是，如果我删除@StartDate和@EndDate变量，而只是TimeStamp在 where 子句中硬编码实际值，查询将在不到 1 秒的时间内返回结果。与变量相比，为什么硬编码TimeStamp查询中的值会产生如此显着的差异？关于如何提高性能的任何建议？不幸的是，我需要使用变量，因为这个查询在不同的时间范围内重复执行。

azure