使用TOP和ESCAPE更改查询计划和执行时间
||
其中一个查询（如下所示）需要90秒钟以上的时间才能执行。它从一个很大的表LogMessage返回约500行。如果从查询中删除了“ 0”，它将在几秒钟内执行。同样，如果删除了“ 1”，它将在几秒钟内执行。在第一种情况下，查询计划显示
Key Lookup (Clustered) PK_LogMessage, Index Scan (NonClustered) IX_LogMessage and Nested Loops (Inner Join)
SELECT TOP (1000)
[Project1].[MessageID] AS [MessageID],
[Project1].[TimeGenerated] AS [TimeGenerated],
[Project1].[SystemName] AS [SystemName],
[Project1].[ApplicationName] AS [ApplicationName]
FROM
(
SELECT
[Project1].[MessageID] AS [MessageID],
[Project1].[TimeGenerated] AS [TimeGenerated],
[Project1].[SystemName] AS [SystemName],
[Project1].[ApplicationName] AS [ApplicationName]
FROM
(
SELECT
[Extent1].[MessageID] AS [MessageID],
[Extent1].[TimeGenerated] AS [TimeGenerated],
[Extent1].[SystemName] AS [SystemName],
[Extent1].[ApplicationName] AS [ApplicationName]
FROM
[dbo].[LogMessage] AS [Extent1]
INNER JOIN
[dbo].[LogMessageCategory] AS [Extent2]
ON
[Extent1].[CategoryID] = [Extent2].[CategoryID]
WHERE
([Extent1].[ApplicationName] LIKE N\'%tier%\' ESCAPE N\'~\')
) AS [Project1]
) AS [Project1]
ORDER BY
[Project1].[TimeGenerated] DESC
CREATE TABLE [dbo].[LogMessage](
[MessageID] [int] IDENTITY(1000001,1) NOT NULL,
[TimeGenerated] [datetime] NOT NULL,
[SystemName] [nvarchar](256) NOT NULL,
[ApplicationName] [nvarchar](512) NOT NULL,
[CategoryID] [int] NOT NULL,
CONSTRAINT [PK_LogMessage] PRIMARY KEY CLUSTERED
(
[MessageID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
ALTER TABLE [dbo].[LogMessage] WITH CHECK ADD CONSTRAINT [FK_LogMessage_LogMessageCategory] FOREIGN KEY([CategoryID])
REFERENCES [dbo].[LogMessageCategory] ([CategoryID])
ALTER TABLE [dbo].[LogMessage] CHECK CONSTRAINT [FK_LogMessage_LogMessageCategory]
ALTER TABLE [dbo].[LogMessage] ADD DEFAULT ((100)) FOR [CategoryID]
CREATE NONCLUSTERED INDEX [IX_LogMessage] ON [dbo].[LogMessage]
(
[TimeGenerated] DESC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF,
IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
CREATE TABLE [dbo].[LogMessageCategory](
[CategoryID] [int] NOT NULL,
[Name] [nvarchar](128) NOT NULL,
[Description] [nvarchar](256) NULL,
CONSTRAINT [PK_LogMessageCategory] PRIMARY KEY CLUSTERED
(
[CategoryID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
没有找到相关结果
已邀请:
4 个回复
厘恼轨
排序的SQL Server可以向下扫描上的索引并针对基表进行查找以评估上的谓词，并在找到第1,000行时停止，或者可以执行聚集索引扫描，找到所有匹配“ 13”谓词的行，然后对它们进行“ 15”排序。 SQL Server维护有关字符串列的统计信息。如果认为第一个计划更有可能被选中，因为它认为很多行最终都将与ѭ13plan谓词匹配，但是该计划并不真正适合作为参数化查询重用，因为在少数情况下它可能会造成灾难性的低效率行匹配。如果匹配项少于1,000，则肯定需要进行与表中行数一样多的键查找。 通过测试，我无法发现添加或删除冗余“ 17”会更改SQL Server基数估计的任何情况。当然，更改参数化查询的文本意味着不能使用原始计划，并且它需要编译一个不同的计划，该计划可能更适合当前考虑的特定值。链眷克袒姜
SELECT TOP(1000) [Extent1].[MessageID] AS [MessageID], [Extent1].[TimeGenerated] AS [TimeGenerated], [Extent1].[SystemName] AS [SystemName], [Extent1].[ApplicationName] AS [ApplicationName] FROM [dbo].[LogMessage] AS [Extent1] INNER JOIN [dbo].[LogMessageCategory] AS [Extent2] ON [Extent1].[CategoryID] = [Extent2].[CategoryID] WHERE ([Extent1].[ApplicationName] LIKE N\'%tier%\' ESCAPE N\'~\') ORDER BY [Extent1].[TimeGenerated] DESC死狸蔽垂藐
SELECT LM.CategoryID AS FromLogMessage, LMC.CategoryID AS FromLogMessageCategory FROM dbo.LogMessage AS LM FULL OUTER JOIN dbo.LogMessageCategory AS LMC ON LMC.CategoryID = LM.CategoryID WHERE LM.CategoryID IS NULL OR LMC.CategoryID IS NULL骇毖煽洁铂
因为如果滚动..您有机会继续使用该框架，并从非常糟糕的sql中获得不错的性能（例如...这意味着您创建了完整的rs，然后仅从中选择1k ... ）。