Databases
StackOverflow2013
Postsdbo
POLINQ to SQL Query using all memory before timing out
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POLINQ to SQL Query using all memory before timing out
    primarykey
    data
    text
    <p>Hi I have a stored procedure within SQL that is a simple select with multiple inner joins, I converted this stored procedure to LINQ and when executing the memory usage climbs until I get out of memory exceptions. I am definitely new to LINQ so I am hoping it is just the way I am doing things. Below is the LINQ code</p> <p>EDIT: StockRoomID, CustomerID (integers) StartDate, EndDate (datetime) variable passed into the function</p> <pre><code>Dim query = (From prds In context.prds Join prd_colour In context.prd_colours On prd_colour.prd_id Equals prds.prd_id Join prd_colour_srsp In context.prd_colour_srsps On prd_colour.prd_colour_id Equals prd_colour_srsp.prd_colour_id Join prd_br In context.prd_brs On prd_br.prd_colour_id Equals prd_colour.prd_colour_id Join prd_stk_rm In context.prd_stk_rms On prd_stk_rm.prd_br_id Equals prd_br.prd_br_id Join ct_colour In context.ct_colours On ct_colour.ct_colour_id Equals prd_colour.ct_colour_id Join ct_pmg In context.ct_pmgs On ct_pmg.ct_pmg_id Equals prds.ct_pmg_id Join ct_pmg_detail In context.ct_pmg_details On ct_pmg_detail.ct_pmg_id Equals ct_pmg.ct_pmg_id Join ct_stk_rm In context.ct_stk_rms On ct_stk_rm.ct_br_id Equals ct_pmg_detail.ct_br_id And ct_stk_rm.ct_stk_rm_id Equals prd_stk_rm.ct_stk_rm_id Join ct_cust_type In context.ct_cust_types On ct_cust_type.ct_cust_type_id Equals ct_pmg_detail.ct_cust_type_id Join ct_cust_br In context.ct_cust_brs On ct_cust_br.ct_cust_type_id Equals ct_cust_type.ct_cust_type_id Where prds.prd_invisible_yn = 0 And prds.sell_yn = 1 And prds.ct_apg_id = 3 And ct_stk_rm.ct_stk_rm_id = StockRoomID And _ ct_cust_br.ct_cust_id = CustomerID And prd_colour_srsp.srsp_date_beg &lt;= StartDate And prd_colour_srsp.srsp_date_end &gt;= EndDate Or _ prd_colour_srsp.srsp_date_end Is Nothing And ct_pmg_detail.pmg_date_beg &lt;= StartDate And ct_pmg_detail.pmg_date_end &gt;= EndDate Or _ ct_pmg_detail.pmg_date_end Is Nothing Select New prd_colour_mix With {.pid = prd_colour.prd_colour_id, .prdcode = prds.prd_sbcode &amp; "-" &amp; ct_colour.ct_colour_code, .prddesc = prds.prd_desc &amp; "-" &amp; ct_colour.ct_colour_desc, .prdLegacyCode = prd_colour.prd_legacy_code}).ToList </code></pre> <p>This query should return roughly 7k records, the stored procedure takes about 500ms to execute</p> <p>Thanks</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USRoss
    1. USRoss
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. COAs i know, when you use ToList(), then you forget about lazy load and all data from your query goes to your memory. Are you sure you need this? Try to remove it.
      singulars
      1. POLINQ to SQL Query using all memory before timing out
      1. This table or related slice is empty.
    2. COI should have been a bit more specific, this is within a function that returns a collection of entities which get bound to a grid, fetching 7k records is the worst case scenario but I am trying to explore my limits when using linq to sql as the limits of using stored procs and returning the results seem to be much greater
      singulars
      1. POLINQ to SQL Query using all memory before timing out
      1. USRoss
    3. COI dont think its good idea to return big set of data (even > 100) from query, where u have 10 JOIN, and after that bind it to grid. You have to use paging and LIMIT your returning data (0, 20), and maybe on 20 rows your ToList will work fine. I told you, that your problem probably in big fetch of data and ToList() in the end, which goes to memory and raise exception
      singulars
      1. POLINQ to SQL Query using all memory before timing out
      1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload