CODEWITHSUNDEEP
sqllinq
user1406162
user1406162

Reputation: 1

Linq very slow query (never completed)

I wonder this linq query takes hours even for a small db There is an old version with a sub query but it throw an exeption.

var a = (
    from item1 in fullMappingData
    from item2 in dc.EntityMasters
    from item3 in dc.Entities
    from item4 in dc.Contributors

    where (
        item1.Name
             .Replace(" ", "")
             .Replace(",", "")
             .Trim()
            == item2.CBEntityName
                    .Replace(" ", "")
                    .Replace(",", "")
                    .Trim()
            &&
        item1.ContributedName
             .Replace(" ", "")
             .Replace(",", "")
             .Trim()
            == item3.EntityName
                    .Replace(" ", "")
                    .Replace(",", "")
                    .Trim()
            &&
        item1.ContributorName
             .Replace(" ", "")
             .Replace(",", "")
             .Trim()
            == item4.ContributorName
                    .Replace(" ", "")
                    .Replace(",", "")
                    .Trim()
    )
    select new Mapping
    {
        ContributedID =item3.ContributedID, // (from x in dc.Entities
                                            //where x.EntityName.Replace(" ", "").Replace(".", "").Replace(",", "").Trim() == item1.ContributedName.Replace(" ", "").Replace(",", "").Trim()
                                            //select x.ContributedID).First(),

        ContributorID = item4.ContributorID,//(from x in dc.Contributors
                                            //where x.ContributorName.Replace(" ", "").Replace(".", "").Replace(",", "").Trim() == contributor.Replace(" ", "").Replace(",", "").Trim()
                                            //select x.ContributorID).First(),

        NameID = item2.NameID   //(from x in dc.EntityMasters
                                // where x.EntityName.Replace(" ", "").Replace(".", "").Replace(",", "").Trim() == item1.Name.Replace(" ", "").Replace(",", "").Trim()
                                //select x.NameID).First()
    }
);

Thanks!

Upvotes: 0

Views: 114

Answers (3)

alc
alc

Reputation: 1557

First off, you should deal with the code smell and go ahead and make a new method for your string pre-processing:

private static string Simplify(string value)
{
  return value.Replace(" ", "").Replace(",", "").Trim();
}

As @Darren points out, the .Trim() likely does nothing if you've already removed all the spaces, but I assume you've left it in there because you're worried about leading/trailing tabs and newlines?

Now, you can greatly simplify the logic (and speed up the whole process) by taking @Virus's suggestion and using join instead of from:

var a =
  from item1 in fullMappingData
  join item2 in dc.EntityMasters on Simplify(item1.Name) equals Simplify(item2.CBEntityName)
  join item3 in dc.Entities on Simplify(item1.ContributedName) equals Simplify(item3.EntityName)
  join item4 in dc.Contributors on Simplify(item1.ContributorName) equals Simplify(item4.ContributorName)
  select new Mapping
  {
    ContributedID = item3.ContributedID,
    ContributorID = item4.ContributorID,
    NameID = item2.NameID
  };

Upvotes: 0

George Mavritsakis
George Mavritsakis

Reputation: 7093

Linq is great but if you want performance you will have to do more than that. I think you should break your query into smaller pieces and use Hasshets/Discitonaries.

Using where or even using joins you will have to face the fact thet performace will be degraded as matching is performed using linear searches.

Especially when you trasform the conditions with many Replace i wouldn't expect any good performance.

From my point of view, you should insert into dictionaries the keys -after you filter them with Replace - that take part in join conditions and after that lookup them using TryGet, ContainsKey or any other hash based lookup and not linear ones.

Of course, you must be aware that with this procedure memory requirements will get worse.

Upvotes: 0

Virus
Virus

Reputation: 2541

Try to use join in place of where.

Upvotes: 3

Related Questions