如何在没有“Timeout expired”的情况下执行查询大数据的例外 [英] How to execute query without "Timeout expired" exception for large data

问题描述

下面是查询，我已经使用了索引。

  SELECT   DISTINCT  MASTREL_MASTER  AS  MasterNo，MASTREL_RELNO  AS  RelationNo，REL_FULLNAME  AS 名称
， CONVERT （ VARCHAR ， REL_DOB， 106 ） AS  DateOfBirth，REL_MOTHER  AS  MotherName，REL_UNIQUEID1  AS  PassportNo 
，REL_UNIQUEID2  AS  DrivingLicenceNo，REL_UNIQUEID3  AS  TINNo，REL_UNIQUEID4  AS  PhotoIdNo，REL_UNIQUEID5  AS 其他
 ，ADDRESS_ADDRESS  AS 地址，CONTACT_CONTACT  AS 联系人，FATHERSPOUSE_FATHER  AS  FatherName，FATHERSPOUSE_SPOUSE  AS  SpouseName 
 < span class =code-keyword> FROM  t_AspMastRel 
  LEFT   OUTER  < span class =code-keyword> JOIN  t_AspAcc  ON  t_AspMastRel.MASTREL_MASTER = t_AspAcc.ACC_MASTER 
  LEFT   OUTER   JOIN  t_AspRel  ON  t_AspMastRel.MASTREL_RELNO = t_AspRel.REL_RELNO 
  LEFT   OUTER   JOIN  t_AspContact  ON  t_AspContact.CONTACT_RELNO = t_AspMastRel.MASTREL_RELNO 
  LEFT   OUTER   JOIN  t _AspAddress  ON  t_AspAddress.ADDRESS_RELNO = t_AspMastRel.MASTREL_RELNO 
  LEFT   OUTER   JOIN  t_AspFatherSpouse  ON  t_AspFatherSpouse.FATHERSPOUSE_RELNO = t_AspMastRel.MASTREL_RELNO 
 < span class =code-keyword> INNER   JOIN （ SELECT   DISTINCT  REL_RELNO  AS  RELNO  FROM  t_AspRel  WHERE  REL_RELNO = '  ********'）< span class =code-keyword> AS  TBL  ON  t_AspMastRel.MASTREL_RELNO = TBL.RELNO 
  WHERE  MASTREL_RELNO = '  ********' 

解决方案

阅读本文：

http://www.hosting.com/support/sql/configure-query-timeout-period-in-sql-management-studio/ [ ^ ]

这个：

http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.connectiontimeout（v = vs.110）的.aspx [ ^ ]



在任何情况下 - SQL服务器都没有超时 - 错误表明你的环境没有得到来自SQL服务器的任何答案在特定的时间。这两篇文章都解释了如何设置该时间段，具体取决于您的环境......

此错误的原因很多。检查这些



肮脏的方式

增加 SQL连接 [ ^ ]

增加SQL命令 [ ^ ]。



好方法

使用 SQL Server Profiler [ ^ ]

如何通过分析Profiler输出来识别SQL Server性能问题？ [ ^ ]

SQL Server最佳实践 [ ^ ]



优化数据库

在SQL Server中优化数据访问的十大步骤：第一部分（使用索引） [ ^ ]

优化SQL Server数据访问的十大步骤：第二部分（重新考虑TSQL并应用最佳实践） [ ^ ]

在SQL Server中优化数据访问的十大步骤：第III部分（应用高级索引和非规范化） [ ^ ]

前10名在SQL Server中优化数据访问的步骤：第IV部分（诊断数据库性能问题） [ ^ ]

优化的十大步骤SQL Server中的数据访问：第V部分（优化数据库文件并应用分区） [ ^ ]

Below is the query,i have already used indexing.

SELECT DISTINCT MASTREL_MASTER AS MasterNo,MASTREL_RELNO AS RelationNo,REL_FULLNAME AS Name
,CONVERT(VARCHAR,REL_DOB,106) AS DateOfBirth,REL_MOTHER AS MotherName,REL_UNIQUEID1 AS PassportNo
,REL_UNIQUEID2 AS DrivingLicenceNo,REL_UNIQUEID3 AS TINNo,REL_UNIQUEID4 AS PhotoIdNo,REL_UNIQUEID5 AS Others
,ADDRESS_ADDRESS AS Address,CONTACT_CONTACT AS Contact,FATHERSPOUSE_FATHER AS FatherName,FATHERSPOUSE_SPOUSE AS SpouseName
FROM t_AspMastRel
LEFT OUTER JOIN t_AspAcc ON t_AspMastRel.MASTREL_MASTER=t_AspAcc.ACC_MASTER
LEFT OUTER JOIN t_AspRel ON t_AspMastRel.MASTREL_RELNO=t_AspRel.REL_RELNO
LEFT OUTER JOIN t_AspContact ON t_AspContact.CONTACT_RELNO=t_AspMastRel.MASTREL_RELNO
LEFT OUTER JOIN t_AspAddress ON t_AspAddress.ADDRESS_RELNO=t_AspMastRel.MASTREL_RELNO
LEFT OUTER JOIN t_AspFatherSpouse ON t_AspFatherSpouse.FATHERSPOUSE_RELNO=t_AspMastRel.MASTREL_RELNO
INNER JOIN (SELECT DISTINCT REL_RELNO AS RELNO FROM t_AspRel WHERE REL_RELNO='********') AS TBL  ON t_AspMastRel.MASTREL_RELNO=TBL.RELNO
WHERE MASTREL_RELNO='********'

解决方案

Read this:
http://www.hosting.com/support/sql/configure-query-timeout-period-in-sql-management-studio/[^]
And this:
http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.connectiontimeout(v=vs.110).aspx[^]

In any case - there is no timeout in SQL server - the error says that your environment didn't get any answer from the SQL server within a specific time. Both articles explain how to set that time period, depending on your environment...

Many reasons for this error. Check these

Dirty ways
Increase timeout for SQL connection[^]
Increase timeout for SQL command[^].

Good ways
Use SQL Server Profiler[^]
How to identify SQL Server performance issues, by analyzing Profiler output?[^]
SQL Server Best Practices[^]

Optimize the database
Top 10 steps to optimize data access in SQL Server: Part I (use indexing)[^]
Top 10 steps to optimize data access in SQL Server: Part II (Re-factor TSQL and apply best practices)[^]
Top 10 steps to optimize data access in SQL Server: Part III (Apply advanced indexing and denormalization)[^]
Top 10 steps to optimize data access in SQL Server: Part IV (Diagnose database performance problems)[^]
Top 10 steps to optimize data access in SQL Server: Part V (Optimize database files and apply partitioning)[^]

这篇关于如何在没有“Timeout expired”的情况下执行查询大数据的例外的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！