0

I am trying to select a result set in to a list of object type.

var temp = res.Select(a => new ContentAudit { 
    locale = a.locale, 
    product_code = a.product_code, product_name = a.product_name, 
    image = a.image, product_short_description = a.product_short_description, 
    product_long_description = a.product_long_description, 
    items = a.items, 
    service_articles = GetServiceArticleCount(a.product_id).ToString(),
    is_deleted = a.is_deleted, views = a.views, 
    placed_in_cart = GetPlacedInCartCount(a.product_id).ToString(), 
    ordered = GetOrderedCount(a.product_id).ToString(), 
    Importance = GetImportance(a.product_id), 
    operation = (a.product_id.ToString()) }
).ToList();

I am selecting from 'res' variable which is the result set selected from the database. Which has aroun 65000 records. So because of that the line of code above dosent work and the server get stucked. Is there anyother way i can achieve this? Thank you

tschmit007
  • 7,559
  • 2
  • 35
  • 43
imanshu15
  • 734
  • 3
  • 21
  • Do you get any error or server goes to deadlock state? – Subash Kharel Jan 09 '18 at 10:36
  • no i dont get any errors, Server just get stucked. I have to restart it to get it work – imanshu15 Jan 09 '18 at 10:37
  • 3
    Your line of code calls other methods; if those methods issue further queries you're simply doing too much work. I don't think there's a simple answer to this. – Alex Paven Jan 09 '18 at 10:38
  • the image is a varbinary field with the actual picture? If it is varbinary this means that you will have to download a huge amount of data. – pitaridis Jan 09 '18 at 10:40
  • Yes actually those methods has some more queries. So is there a best way to do this? – imanshu15 Jan 09 '18 at 10:40
  • @pitaridis no its not. just an Url – imanshu15 Jan 09 '18 at 10:41
  • 3
    If the `GetImportance`, `GetOrderedCount` and `GetPlacedInCartCount`methods is getting data from database, dude, the problem isn't that _Linq fails to selec from large dataset_, for sure – Diego Rafael Souza Jan 09 '18 at 10:42
  • Try splitting the code and save intermediate variables. – Subash Kharel Jan 09 '18 at 10:44
  • On each of that inner methods you are seeking for an register by id... Try to group the ids you will look for before send the request to the database. And do it in just one request. You'll go improve from 195.000 database requests to just 3. (Sorry my bad english) – Diego Rafael Souza Jan 09 '18 at 10:53
  • 4
    you are probably facing an [N+1 query problem](https://stackoverflow.com/questions/97197/what-is-n1-select-query-issue). The use of a sql profiler should show you the problem. – tschmit007 Jan 09 '18 at 11:39
  • Res contains 65000 elements. Do you really need all 65000 elements in Temp as well (ToList()), or do you want every element one-by-one? if the latter is the case, why use ToList() instead as foreach? – Harald Coppoolse Jan 09 '18 at 12:46
  • 65,000 rows is big?? – GrahamJ Mar 29 '21 at 15:50

1 Answers1

2

There are many problems with this query.

1st You are trying to select 65000 records from DB and use .ToList() It will iterate all objects. You should use IEnumerable (or IQueryable), and use lazy loading. If you do not need all of this objects try to add .Where() statement to limit number of entities.

2nd in query You are using methods wich are trying to make even more request to db. Do You realy need all this data? If yes make sure that everything is using lazy loading. Do not iterate it all in one time!

I can see two solutions. If You don't need all this data, take only data You need from db and limit number of retrived entitiies as much as its posible.

If you realy need all this data, try to use lazy loading, and add pagination (.take() and .skip() methods) to limit number of entites retrived in one call.

garret
  • 1,134
  • 8
  • 16