在SQL Server中，对大型表的查询会超时

本文关键字：查询 超时 大型 SQL Server | 更新日期: 2023-09-27 18:10:05

我正在运行简单的查询，如(Select top 10 records, joins, counts, group by)根据要求获取行和统计。表大约有150,000到200,000行。所有主键和外键都定义良好。

但是我在运行查询时随机收到超时错误。相同的查询或存储过程需要不同的时间来完成。数据库托管在共享服务器上。我将命令超时属性设置为600，但它不起作用。我的服务提供商说，如果处理没有完成，系统将在30秒后终止查询。他们让我调整查询以匹配时间。

。

CREATE PROCEDURE [dbo].[NP_TableReport]  
(    
 @CreatedOnFrom datetime = NULL,  
 @CreatedOnTo datetime = NULL
)    
AS    
BEGIN  
 SELECT COUNT(ID) [Count]    
 FROM Table1
 WHERE     
     (@CreatedOnFrom IS NULL OR @CreatedOnFrom <= CreatedOn)  
 AND (@CreatedOnTo IS NULL OR @CreatedOnTo >= CreatedOn)  
END

请告诉我是什么原因导致随机超时错误?当我在专用服务器上尝试同样的事情时，错误消失了。有人能解释一下发生了什么吗?

在SQL Server中，对大型表的查询会超时

首先用CreateOn字段在Table1上添加索引。

使用count(*)而不是count(id)，除非您特别想计数id列中的非空值。

根据您的输入值，您有四种不同的查询，因此对它们进行专门化可能会有所帮助:

if (@CreatedOnFrom is null and @CreatedOnTo is null) begin
  select count(*) [count]
  from Table1
end else if (@CreatedOnFrom is null) begin
  select count(*) [count]
  from Table1
  where @CreatedOnTo >= CreatedOn
end else if (@CreatedOnTo is null) begin
  select count(*) [count]
  from Table1
  where @CreatedOnFrom <= CreatedOn
end else begin
  select count(*) [count]
  from Table1
  where @CreatedOnTo >= CreatedOn and @CreatedOnFrom <= CreatedOn
end

对于超过200K的元组，平均一次查询很容易花费30秒以上的时间来完成。如果您的主机提供商不允许查询花费超过30秒的时间，请考虑使用客户端技术获取和过滤数据。如果这样也不行，那就考虑更换主机提供商，或者为你的应用程序购买一个单独的数据库主机。

您已经提到您已经在"createdOn"列上创建了索引。我认为您的查询和数据库状态目前处于最优化的形式。只有删除主机提供商强加的超时约束才能解决问题。

我想我不会把这么多的计算在查询，如果它可以事先完成。带有null的不等式通常是相当恶心的，IMX。

CREATE PROCEDURE [dbo].[NP_TableReport]  
(    
 @CreatedOnFrom datetime = NULL,  
 @CreatedOnTo datetime = NULL
)    
AS    
BEGIN  
 SET @CreatedOnFrom = COALESCE(@CreatedOnFrom, CAST('01/01/1753' as datetime))
 SET @CreatedOnTo = COALESCE(@CreatedOnTo, CAST('12/31/9999' as datetime))
 SELECT COUNT(ID) [Count]    
 FROM dbo.Table1
 WHERE CreatedOn BETWEEN @CreatedOnTo AND @CreatedOnFrom
END

注意，如果Table1.CreatedOn包含午夜之后的时间组件，但您只想向过程提交日期，则可能需要使用以下方式:

 SET @CreatedOnTo = COALESCE(DATEADD(day,1,@CreatedOnTo), CAST('12/31/9999 23:59:59' as datetime))

您甚至可以从中减去一秒来获得DATEADD，以获得指定日期的最后一秒。

您可以将COALESCE语句放入WHERE子句中，但我发现这样更容易阅读。

相关文章：