CODEWITHSUNDEEP
vb.netlinq
Ross
Ross

Reputation: 373

LINQ to SQL Query using all memory before timing out

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

EDIT: StockRoomID, CustomerID (integers) StartDate, EndDate (datetime) variable passed into the function

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 <= StartDate And prd_colour_srsp.srsp_date_end >= EndDate Or _
                prd_colour_srsp.srsp_date_end Is Nothing And ct_pmg_detail.pmg_date_beg <= StartDate And ct_pmg_detail.pmg_date_end >= 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 & "-" & ct_colour.ct_colour_code, .prddesc = prds.prd_desc & "-" & ct_colour.ct_colour_desc, .prdLegacyCode = prd_colour.prd_legacy_code}).ToList

This query should return roughly 7k records, the stored procedure takes about 500ms to execute

Thanks

Upvotes: 0

Views: 338

Answers (1)

Tormod
Tormod

Reputation: 4573

Most likely it is doing client side something that you want it to do serverside.

Download LINQPad (free), fire it up and add connection to your datasource, select the added datasource as the active one for your query, set Language to "VB Statements" and then run it. Select the "SQL" tab in the result output window and inspect the SQL. Then you'll see what parts of the expression that is handled server side.

Upvotes: 1

Related Questions