如何避免Sql查询超时 [英] How to avoid Sql Query Timeout

问题描述

我对 SQL 视图具有 RO 访问权限.下面的这个查询超时.如何避免这种情况?

I have RO access on a SQL View. This query below times out. How to avoid this?

select  
  count(distinct Status)  
from 
  [MyTable]  with (NOLOCK)
where 
  MemberType=6

我得到的错误信息是:

消息 121，级别 20，状态 0，第 0 行

Msg 121, Level 20, State 0, Line 0

从服务器接收结果时发生传输级错误(提供者:TCP 提供者，错误:0 - 信号量超时期限已过期.)

A transport-level error has occurred when receiving results from the server (provider: TCP Provider, error: 0 - The semaphore timeout period has expired.)

推荐答案

尽管显然存在某种网络不稳定或干扰您的连接的情况(15 分钟可能会跨越 NAT 边界或网络中的某些内容正在放弃会话)，我认为您想要这样一个简单的?)查询在任何预期的时间(例如 1 秒)内都能很好地返回.

Although there is clearly some kind of network instability or something interfering with your connection (15 minutes is possible that you could be crossing a NAT boundary or something in your network is dropping the session), I would think you want such a simple?) query to return well within any anticipated timeoue (like 1s).

我会与您的 DBA 交谈并获取在 MemberType、Status 的基础表上创建的索引.如果没有单个基础表，或者这些表更复杂并且由视图或 UDF 创建，并且您运行的是 SQL Server 2005 或更高版本，请让他考虑索引视图(基本上以索引方式具体化视图).

I would talk to your DBA and get an index created on the underlying tables on MemberType, Status. If there isn't a single underlying table or these are more complex and created by the view or UDF, and you are running SQL Server 2005 or above, have him consider indexing the view (basically materializing the view in an indexed fashion).

这篇关于如何避免Sql查询超时的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！