1.update table1 set field='' where id in(select distinct(id) from table2)
2.SELECT * fROM Db2 WHERE (BDJH IN (SELECT BDJH FROM Db2 GROUP BY BDJH HAVING (COUNT(BDJH) > 1)))')(删除bdjh字段有重复的记录)
以上两名数据量都很大,在程序中实现会提示"超时",就算直接在数据库中执也很慢。
怎样改进?
...全文
237打赏收藏
看一下这句话,给点建议。
1.update table1 set field='' where id in(select distinct(id) from table2) 2.SELECT * fROM Db2 WHERE (BDJH IN (SELECT BDJH FROM Db2 GROUP BY BDJH HAVING (COUNT(BDJH) > 1)))')(删除bdjh字段有重复的记录) 以上两名数据量都很大,在程序中实现会提示"超时",就算直接在数据库中执也很慢。 怎样改进?
首先定义索引。
1:
试试
update table1 set field='' where exists(select top 1 * from table2 where table1.id=table2.id)
2:
试试
declare @temp table (bdjh int)
select bdjh from db2 group by bdjh having count(*) >1
select * from db2 where exists(select * from @temp a where db2.bdjh = a.bdjh)
当数据量很大时,exists可能会比in要快。
SQL SERVER数据库超时设置:SQL SERVER-属性-连接-查询超时属性
Delphi超时设置:ADOConnection.CommandTimeout:=600; (s)