执行查询时捕获超时异常 [英] Time out exception caught while executing query

问题描述

我试图使用子查询删除主键表中不存在于外键表中的记录。最初它工作正常，但随着数据变得庞大，执行需要大量时间并导致超时。主键是uniqueidentifier类型，这会导致问题吗？



考虑一个表Employee具有EmpID uniqueidentifier和Name varchar（200）作为列，并考虑具有ProjectID uniqueIdentifier和EmpID [FK]和ProjectName varchar（200）作为列的Project表



现在我写的从Employe表中删除记录的查询如下：

 删除 来自员工其中​​ EmpID    （选择 EmpID 来自项目 （ nolock ））

请建议我如何解决这个问题。



提前谢谢。

解决方案

如果是SQL Server，那就试试吧像这样：

  DELETE   FROM 员工
  FROM 员工A 
  LEFT   OUTER   JOIN 项目B 
  ON  A .EmpID = B.EmpID 
  WHERE  B.EmpID  IS  空白 

（未经测试。）



一般来说， JOIN 的表现要比subselect好得多。



https ：//msdn.microsoft.com/en-us/l ibrary / ms189835.aspx [ ^

Hi,
I am trying to delete the records from primary key table which are not present in foreign key table using sub query. Initially it was working fine but as data become huge it takes lot of time to execute and results in "time out" .The primary key is of type uniqueidentifier , doets this causing problem ?

Consider a table Employe having EmpID uniqueidentifier and Name varchar(200) as columns and consider Project table having ProjectID uniqueIdentifier and EmpID [FK] and ProjectName varchar(200) as columns

Now the query i have written to delete record from Employe table is as below

Delete from Employe where EmpID not in(select EmpID from Project with(nolock))

Please suggest me how to solve the issue.

Thanks in advance .

解决方案

If SQL Server, then try something like this:

DELETE FROM Employe
FROM Employe A
LEFT OUTER JOIN Project B
ON A.EmpID = B.EmpID 
WHERE B.EmpID IS NULL

(Not tested.)

Generally, a JOIN will perform much better than a subselect.

https://msdn.microsoft.com/en-us/library/ms189835.aspx[^]

这篇关于执行查询时捕获超时异常的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！