有没有更有效的方法来查找 Sql 服务器中的重复项
我有这个问题 我为此提供了一个解决方案。但是,我认为这并没有达到应有的效率:
using (DataContext context = new DataContext(SqlConnection)
{
var custInfo = context.GetTable<tbl_CustomerInfo>();
string compID = ImportCust.Rows[0]["CompanyID"].ToString();
var imports = from cust in ImportCust.AsEnumerable()
select cust.Field<int>("CustomerID");
var dupes = from import in imports
join cust in custInfo
on import equals cust.CustomerID
where cust.CompanyID== pivnum
select cust;
var records = dupes.GetEnumerator();
while (records.MoveNext())
{ custInfo.DeleteOnSubmit(records.Current); }
context.SubmitChanges();
}
我使用了 Stopwatch 来查看从 records
迭代到完成 SubmitChanges 所用的时间
。流逝的时间似乎没有规律或理由:
20秒完成10666条记录
12秒完成15425条记录
21 秒内完成 289 条记录
显然,如果我删除索引,可以加快速度。可以通过编程来完成吗?另外,还有比我提供的更好的方法吗?
I have this question for which I provided a solution. However, I don't feel that is as efficient as it could be:
using (DataContext context = new DataContext(SqlConnection)
{
var custInfo = context.GetTable<tbl_CustomerInfo>();
string compID = ImportCust.Rows[0]["CompanyID"].ToString();
var imports = from cust in ImportCust.AsEnumerable()
select cust.Field<int>("CustomerID");
var dupes = from import in imports
join cust in custInfo
on import equals cust.CustomerID
where cust.CompanyID== pivnum
select cust;
var records = dupes.GetEnumerator();
while (records.MoveNext())
{ custInfo.DeleteOnSubmit(records.Current); }
context.SubmitChanges();
}
I have used Stopwatch
to look at elapsed time spanning iteration of records
to completion of SubmitChanges
. The elapsed times seem to have no rhyme or reason:
10666 records completed in 20 seconds
15425 records completed in 12 seconds
289 records completed in 21 seconds
Obviously, one thing that would speed things up is if I were to drop indexes. Can that be done programmatically? In addition, is there a better method than what I have provided?
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(2)
你可以使用SQL语句:
you could use SQL statment:
Tefod 的 sql 的 linq 版本:
The linq version of Tefod's sql: