SQL Server 2014标准版大表性能
问题描述:
我有一个关于性能的问题,目前我有一个表在查询性能方面遇到麻烦,只要表中的行已经有数百万条记录。SQL Server 2014标准版大表性能
这是表:
CREATE TABLE [dbo].[HistorySampleValues]
(
[HistoryParameterID] [int] NOT NULL,
[SourceTimeStamp] [datetime2](7) NOT NULL,
[ArchiveTimestamp] [datetime2](7) NOT NULL CONSTRAINT [DF__HistorySa__Archi__2A164134] DEFAULT (getutcdate()),
[ValueStatus] [int] NOT NULL,
[ArchiveStatus] [int] NOT NULL,
[IntegerValue] [bigint] SPARSE NULL,
[DoubleValue] [float] SPARSE NULL,
[StringValue] [varchar](100) SPARSE NULL,
[EnumNamedSetName] [varchar](100) SPARSE NULL,
[EnumNumericValue] [int] SPARSE NULL,
[EnumTextualValue] [varchar](256) SPARSE NULL
) ON [PRIMARY]
CREATE CLUSTERED INDEX [Source_HistParameterID_Index] ON [dbo].[HistorySampleValues]
(
[HistoryParameterID] ASC,
[SourceTimeStamp] ASC
) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO
它与HistoryParameterID
和SourceTimeStamp
聚集索引相当平坦。
这是我使用
SET NOCOUNT ON;
DECLARE @SqlCommand NVARCHAR(MAX)
SET @SqlCommand = 'SELECT HistoryParameterID,
SourceTimestamp, ArchiveTimestamp,ValueStatus,ArchiveStatus,
IntegerValue,DoubleValue,StringValue,EnumNumericValue,
EnumTextualValue,EnumNamedSetName
FROM [HistorySampleValues] WITH(NOLOCK)
WHERE ([HistoryParameterID] =' + @ParamIds + '
AND
[SourceTimeStamp] >= ''' + CONVERT(VARCHAR(30),@StartTime, 25) + '''
AND
[SourceTimeStamp] <= ''' + CONVERT(VARCHAR(30),@EndTime, 25) + ''')
AND ValueStatus = ' + @ValueStatus
EXECUTE(@SqlCommand)
正如你可以看到HistoryParameterID
和SourceTimestamp
被用作第一查询参数的存储过程。并检索8小时的记录,这是约28千记录,它返回与不稳定的表现,1.8秒 - 700毫秒
设计的规模?每当它达到770亿条记录?还是有什么策略可以使用? SQL Server的版本是标准版,所以没有使用分区的列存储。还是我达到了SQL Server标准版的最高性能?
这是更新的存储过程
@ParamIds int,
@StartTime datetime,
@EndTime datetime,
@ValueStatus int
AS
BEGIN
SET NOCOUNT ON;
SELECT HistoryParameterID,
SourceTimestamp, ArchiveTimestamp,ValueStatus,ArchiveStatus,
IntegerValue,DoubleValue,StringValue,EnumNumericValue,
EnumTextualValue,EnumNamedSetName
FROM [HistorySampleValues] WITH(NOLOCK)
WHERE
HistoryParameterID = @ParamIds
AND (SourceTimeStamp >= @StartTime AND SourceTimeStamp <[email protected])
AND (@ValueStatus = -1 OR ValueStatus = @ValueStatus)
我在检索41213列在表中的〜849600000行得到了1.396秒客户端的处理时间。
有没有办法改善这一点?
答
每次执行新的SQL命令时,都必须由MS SQL Server编译。如果您重新使用该命令,则可节省编译时间。你需要直接在存储过程中执行这个命令,这样可以编译并给出更一致的结果。
SELECT ...
WHERE ([HistoryParameterID] = @ParamIds
AND [SourceTimeStamp] >= @StartTime
AND [SourceTimeStamp] <= @EndTime
AND ValueStatus = @ValueStatus
这会给你一个机会来监视命令的执行。
好奇知道为什么动态查询? – Anil 2014-12-08 08:25:54
基于测试,我可以使用动态查询来提高性能,与其他人相比 – carlcrol 2014-12-08 08:28:42
如果您打算重用查询,参数将为您提供更好的结果。 – 2014-12-08 08:31:04