CODEWITHSUNDEEP
c#performancelinqentity-framework
V.K.
V.K.

Reputation: 31

Performance of Linq on entity framework vs SQL stored procedure

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

Upvotes: 3

Views: 8125

Answers (1)

Aron
Aron

Reputation: 15772

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

Upvotes: 5

Related Questions