有没有更有效的方法来查找在SQL Server愚弄 [英] Is there a more efficient method to find dupes in Sql server

问题描述

我有这样的<一个href="http://stackoverflow.com/questions/4450329/possible-to-write-a-join-between-sql-and-datatable-using-linq/4454871#4454871">question为此我公司提供的在的解决方案。但是，我不觉得这是有效率，因为它可能是：

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();
}

我已经使用秒表来看看经过的时间跨越了记录迭代到的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条记录在20秒内完成
15425的记录在12秒内完成
289记录在21秒内完成

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?

推荐答案

您可以使用SQL说明书：

you could use SQL statment:

-- TSQL (SQL Server 2005/2008): --

select CompanyID from tbl_CustomerInfo
    group by CompanyID
    having COUNT(*)>1

这篇关于有没有更有效的方法来查找在SQL Server愚弄的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！