c# - Optimizing LINQ Query using Entity Framework -


i have following linq query product information using entity framework

productdetails.items = (from productdetail in db.tolist()                             select new prod                             {                                 id = productdetail.id                             prodname = productdetail.productname,                                         ...                                         ...                                         ...                                         ...                             calaculation1 = getcalaculation(productdetail.calc1),                             calaculation1 = getcalaculation(productdetail.calc2),                                         ...                                         ...                                         ...                                         ...                                         calaculation15 = getcalaculation(productdetail.calc3)                             }                         ).tolist();

where getcalaculation method queries db using linq. query slow if fetching 100's of records. how can optimize it?

first of structure of select looks "little" problematic me since fetching 15 calaculation properties each record. if create view in data base have 15 joins calculations table bad performance. first thing should review object structure , confirm need calculations fetched in 1 request. if insist structure can not changed here steps improve performance:

  1. if tables don't change may consider create materialized view (view clustered index in sql server) contain calculated data. in case query fast inserts/updates in tables slower.

  2. do not use db.tolist() in query - doing fetch table in memory , issue separate queries each 1 of calculations.

  3. i little confused query:

    var dbquery = calculation in db.calculations                calculation.calc == calc1 select calculation);  var totalsum = (from xyz in dbquery select (decimal?)xyz.calc).sum() ?? 0;

you fetching records have calc == calc1 , calculating sum? wouldn't easier count how many records have calc == calc1 , multiply calc1

db.calculations.count(c=>c.calc == calc1) * calc1;
  1. it cheaper fetch calculations table memory product table ( var calctable = db.calculations.tolist() ) if has limited number of records getcalaculation work in-memory objects faster. if going may consider in parallel or in separate tasks.

Comments

Post a Comment

Popular posts from this blog

java - Spring Data JPA: Why findOne(id) executing delete query internally? -

i have 1 application using @manytomany annotation link 2 different entities 1 parent entity. not sure if have implemented properly. when execute .findone() on parent entity's repository find it, @ first fetch data along executing delete queries internally delete record mapping table (a table jpa creates after adding @manytomany annotation.). following generated sql queries , code snippet: java class @entity @table(name = "inventory") public class inventory { @id long id; // many other properties @manytomany(fetch=fetchtype.lazy) private list<stream> streams; @manytomany(fetch=fetchtype.lazy) private list<subject> subjects; } there no other annotation i've put in stream , subject entity class link inventory class. when run app, create following tables: inventory stream subject inventory_stream inventory_subject (inventory_stream , inventory_subject, 2 tables created automatically, don't k...
Read more

python - Mongodb How to add addtional information when aggregating? -

i beginner , wrote line pipeline works, wanna add other information output, screen name , or number of tweets.i tried add under $group gave me syntax error everytime here pipeline: def make_pipeline(): # complete aggregation pipeline pipeline = [ { '$match': { "user.statuses_count": {"$gt":99 }, "user.time_zone": "brasilia" } }, { "$group": { "_id": "$user.id", "followers": { "$max": "$user.followers_count" } } }, { "$sort": { "followers": -1 } }, { "$limit" : 1 } ]; i using on example : { "_id" : objectid("5304e2e3cc9e684aa98bef97"), "text" : "first week of school on :p", "in_reply_to_status_id" : null, "retweet_count" : null, ...
Read more

java - Incorrect order of records in M-M relationship in hibernate -

Image
i have 4 records in bridge table in mysql database. using @manytomany in hibernate, accurate order of employees incorrect incorrect of projects. following required material figure out doing mistake. this source. @entity @table(name="project") public class projectbean { @id @generatedvalue @column(name="project_id") private int projectid; @column(name="title") private string projecttitle; @manytomany(mappedby="projects") private collection<employeebean> employees; public projectbean() { employees = new arraylist<employeebean>(); } //getters & setters } @entity @table(name="employee") public class employeebean { @id @generatedvalue @column(name="employee_id") private int employee_id; @column(name="first_name") private string ...
Read more