Performance of Linq on entity framework vs SQL stored procedure

c# entity-framework linq performance

Question

We are using the Entity Framework to fetch some data. The LINQ query is using multiple joins, as shown in the code below. I have been asked to change this to a SQL Stored Procedure because its faster. How can i optimize this LINQ code and why is it slow?

var brands = (from b in entity.tblBrands
                          join m in entity.tblMaterials on b.BrandID equals m.BrandID
                          join bm in entity.tblBranchMaterials on m.MaterialID equals bm.MaterialID
                          join br in entity.tblBranches on bm.BranchID equals br.BranchID
                          where br.BranchID == branch.branchId
                          select new Brand { brandId=b.BrandID, brandName=b.BrandName, SAPBrandId=b.SAPBrandID}).Distinct();
            return brands.ToList();
1
3
3/6/2013 7:55:22 AM

Popular Answer

I suspect the the major performance issue is due to a major gripe of mine. Abuse of the keyword join.

Due to the usage of JOIN, you are getting too many results. So you then used a DISTINCT. Worse, you did so for the outer result set, which SQL server has no index on.

var brands = from b in context.Brands
where 
    (from m in context.Materials 
        where b.BrandID == m.BrandID 
        where (from bm in context.BranchMaterials 
                where (from br in context.Branches
                        where bm.BranchID == br.BranchID
                        where br.BranchID == branch.branchId
                        select br).Any()
                where m.MaterialID == bm.MaterialID select bm).Any()
        select m).Any()
    ).Any()
select b;

Should be more performant. However this again is STILL wrong. Since when using ORMs we should be thinking about ASSOCIATIONS and not JOINs. Assuming your model makes any sense, I would do the following.

var brands = from b in context.Brands
             where (from m in b.Materials
                    //Assuming that BranchMaterials is just a Many-Many mapping table
                    from br in m.Branches
                    where br.BranchID == branch.branchId).Any()     
                select new Brand { brandId=b.BrandID, brandName=b.BrandName, SAPBrandId=b.SAPBrandID};
5
3/6/2013 8:12:15 AM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow