c# - LINQ query slow, creates Timeout; generated SQL is fine? -
i have complex linq query slow, creates system.data.sqlclient.sqlexception: "the wait operation timed out".
however, when log generated sql (by assigning textwriter datacontext's log), , execute directly on sql server, completes in 4 seconds, fine.
where discrepancy come , how debug it?
edit: i've noticed in sql server management studio's activity monitor processor time spiking 100% when query executing .net, 3% or when execute generated sql query.
i'm not sure how posting code help, since requested, here code containing query:
var db = myproject.getdatacontext(); var statuspaymentsuccess = new string[] { "success", "rembours", "afterpay" }; var items = db.orders.where(item => (siteid == null || item.siteid == siteid) && (ls_list.contains(item.orderorderlifecycles.orderbydescending(it => it.id).first().orderlifecycleid)) && (item.orderorderpaymentstatus.any(ops => statuspaymentsuccess.contains(ops.orderpaymentstatus.code)) && (cycleid == null || item.orderorderlifecycles.first().orderlifecycleid == cycleid) && (locationid == null || item.salelocationid == locationid) && (string.isnullorempty(salesperson) || item.employeename.tolower() == salesperson.tolower())) ); var betweenorders = items.select(it => new orderbetween() { firstpaydate = it.orderorderpaymentstatus.firstordefault(ops => statuspaymentsuccess.contains(ops.orderpaymentstatus.code)).datetime, ordertotal = it.totalamount, vattotal = it.orderitems.sum(it2 => it2.btwamount ?? 0), quantity = it.orderitems.count, siteid = it.siteid }); return betweenorders.where(item => item.firstpaydate >= start && item.firstpaydate < stop) .groupby(item => item.firstpaydate.value.year + "-" + item.firstpaydate.value.month).select( item => new saletotal() { count = item.sum(sub => sub.quantity), month = item.firstordefault().firstpaydate.value.year + "-" + item.firstordefault().firstpaydate.value.month.tostring().padleft(2, '0'), total = item.sum(sub => sub.ordertotal), vat = item.sum(sub => sub.vattotal) }).orderby(item => item.month).toarray(); where ls_list list<int> containing orderorderlifecycles ids.
the generated sql query pulled log:
declare @p0 nvarchar(4000) = 'success' declare @p1 nvarchar(4000) = 'rembours' declare @p2 nvarchar(4000) = 'afterpay' declare @p3 decimal(31,2) = '0' declare @p4 nvarchar(4000) = '-' declare @p5 datetime = '2016-06-01' declare @p6 datetime = '2016-09-01' declare @p7 int = '4' declare @p8 int = '5' declare @p9 int = '8' declare @p10 nvarchar(4000) = 'success' declare @p11 nvarchar(4000) = 'rembours' declare @p12 nvarchar(4000) = 'afterpay' declare @p13 nvarchar(4000) = '-' declare @p14 nvarchar(4000) = '-' declare @p15 int = '2' declare @p16 nchar(1) = '0' select [t64].[value] [month], [t64].[value2] [count], [t64].[value22] [total], [t64].[value3] [vat] ( select ((convert(nvarchar,datepart(year, ( select [t23].[value] ( select top (1) [t18].[value] ( select ( select [t17].[datetime] ( select top (1) [t15].[datetime] [dbo].[orderorderpaymentstatus] [t15] inner join [dbo].[orderpaymentstatus] [t16] on [t16].[id] = [t15].[orderpaymentstatusid] ([t16].[code] in (@p0, @p1, @p2)) , ([t15].[orderid] = [t14].[id]) ) [t17] ) [value], [t14].[id] [dbo].[order] [t14] ) [t18] ((([t13].[value4] null) , ((((convert(nvarchar,datepart(year, [t18].[value]))) + @p13) + (convert(nvarchar,datepart(month, [t18].[value])))) null)) or (([t13].[value4] not null) , ((((convert(nvarchar,datepart(year, [t18].[value]))) + @p13) + (convert(nvarchar,datepart(month, [t18].[value])))) not null) , ([t13].[value4] = (((convert(nvarchar,datepart(year, [t18].[value]))) + @p13) + (convert(nvarchar,datepart(month, [t18].[value]))))))) , ([t18].[value] >= @p5) , ([t18].[value] < @p6) , ((( select [t20].[orderlifecycleid] ( select top (1) [t19].[orderlifecycleid] [dbo].[orderorderlifecycle] [t19] [t19].[orderid] = [t18].[id] order [t19].[id] desc ) [t20] )) in (@p7, @p8, @p9)) , (exists( select null [empty] [dbo].[orderorderpaymentstatus] [t21] inner join [dbo].[orderpaymentstatus] [t22] on [t22].[id] = [t21].[orderpaymentstatusid] ([t22].[code] in (@p10, @p11, @p12)) , ([t21].[orderid] = [t18].[id]) )) ) [t23] )))) + @p14) + ( (case when (convert(int,datalength(convert(nvarchar,datepart(month, ( select [t63].[value] ( select top (1) [t58].[value] ( select ( select [t57].[datetime] ( select top (1) [t55].[datetime] [dbo].[orderorderpaymentstatus] [t55] inner join [dbo].[orderpaymentstatus] [t56] on [t56].[id] = [t55].[orderpaymentstatusid] ([t56].[code] in (@p0, @p1, @p2)) , ([t55].[orderid] = [t54].[id]) ) [t57] ) [value], [t54].[id] [dbo].[order] [t54] ) [t58] ((([t13].[value4] null) , ((((convert(nvarchar,datepart(year, [t58].[value]))) + @p13) + (convert(nvarchar,datepart(month, [t58].[value])))) null)) or (([t13].[value4] not null) , ((((convert(nvarchar,datepart(year, [t58].[value]))) + @p13) + (convert(nvarchar,datepart(month, [t58].[value])))) not null) , ([t13].[value4] = (((convert(nvarchar,datepart(year, [t58].[value]))) + @p13) + (convert(nvarchar,datepart(month, [t58].[value]))))))) , ([t58].[value] >= @p5) , ([t58].[value] < @p6) , ((( select [t60].[orderlifecycleid] ( select top (1) [t59].[orderlifecycleid] [dbo].[orderorderlifecycle] [t59] [t59].[orderid] = [t58].[id] order [t59].[id] desc ) [t60] )) in (@p7, @p8, @p9)) , (exists( select null [empty] [dbo].[orderorderpaymentstatus] [t61] inner join [dbo].[orderpaymentstatus] [t62] on [t62].[id] = [t61].[orderpaymentstatusid] ([t62].[code] in (@p10, @p11, @p12)) , ([t61].[orderid] = [t58].[id]) )) ) [t63] )))) / 2)) >= @p15 convert(nvarchar,datepart(month, ( select [t63].[value] ( select top (1) [t58].[value] ( select ( select [t57].[datetime] ( select top (1) [t55].[datetime] [dbo].[orderorderpaymentstatus] [t55] inner join [dbo].[orderpaymentstatus] [t56] on [t56].[id] = [t55].[orderpaymentstatusid] ([t56].[code] in (@p0, @p1, @p2)) , ([t55].[orderid] = [t54].[id]) ) [t57] ) [value], [t54].[id] [dbo].[order] [t54] ) [t58] ((([t13].[value4] null) , ((((convert(nvarchar,datepart(year, [t58].[value]))) + @p13) + (convert(nvarchar,datepart(month, [t58].[value])))) null)) or (([t13].[value4] not null) , ((((convert(nvarchar,datepart(year, [t58].[value]))) + @p13) + (convert(nvarchar,datepart(month, [t58].[value])))) not null) , ([t13].[value4] = (((convert(nvarchar,datepart(year, [t58].[value]))) + @p13) + (convert(nvarchar,datepart(month, [t58].[value]))))))) , ([t58].[value] >= @p5) , ([t58].[value] < @p6) , ((( select [t60].[orderlifecycleid] ( select top (1) [t59].[orderlifecycleid] [dbo].[orderorderlifecycle] [t59] [t59].[orderid] = [t58].[id] order [t59].[id] desc ) [t60] )) in (@p7, @p8, @p9)) , (exists( select null [empty] [dbo].[orderorderpaymentstatus] [t61] inner join [dbo].[orderpaymentstatus] [t62] on [t62].[id] = [t61].[orderpaymentstatusid] ([t62].[code] in (@p10, @p11, @p12)) , ([t61].[orderid] = [t58].[id]) )) ) [t63] ))) else replicate(@p16, @p15 - (convert(int,datalength(convert(nvarchar,datepart(month, ( select [t63].[value] ( select top (1) [t58].[value] ( select ( select [t57].[datetime] ( select top (1) [t55].[datetime] [dbo].[orderorderpaymentstatus] [t55] inner join [dbo].[orderpaymentstatus] [t56] on [t56].[id] = [t55].[orderpaymentstatusid] ([t56].[code] in (@p0, @p1, @p2)) , ([t55].[orderid] = [t54].[id]) ) [t57] ) [value], [t54].[id] [dbo].[order] [t54] ) [t58] ((([t13].[value4] null) , ((((convert(nvarchar,datepart(year, [t58].[value]))) + @p13) + (convert(nvarchar,datepart(month, [t58].[value])))) null)) or (([t13].[value4] not null) , ((((convert(nvarchar,datepart(year, [t58].[value]))) + @p13) + (convert(nvarchar,datepart(month, [t58].[value])))) not null) , ([t13].[value4] = (((convert(nvarchar,datepart(year, [t58].[value]))) + @p13) + (convert(nvarchar,datepart(month, [t58].[value]))))))) , ([t58].[value] >= @p5) , ([t58].[value] < @p6) , ((( select [t60].[orderlifecycleid] ( select top (1) [t59].[orderlifecycleid] [dbo].[orderorderlifecycle] [t59] [t59].[orderid] = [t58].[id] order [t59].[id] desc ) [t60] )) in (@p7, @p8, @p9)) , (exists( select null [empty] [dbo].[orderorderpaymentstatus] [t61] inner join [dbo].[orderpaymentstatus] [t62] on [t62].[id] = [t61].[orderpaymentstatusid] ([t62].[code] in (@p10, @p11, @p12)) , ([t61].[orderid] = [t58].[id]) )) ) [t63] )))) / 2))) + (convert(nvarchar,datepart(month, ( select [t63].[value] ( select top (1) [t58].[value] ( select ( select [t57].[datetime] ( select top (1) [t55].[datetime] [dbo].[orderorderpaymentstatus] [t55] inner join [dbo].[orderpaymentstatus] [t56] on [t56].[id] = [t55].[orderpaymentstatusid] ([t56].[code] in (@p0, @p1, @p2)) , ([t55].[orderid] = [t54].[id]) ) [t57] ) [value], [t54].[id] [dbo].[order] [t54] ) [t58] ((([t13].[value4] null) , ((((convert(nvarchar,datepart(year, [t58].[value]))) + @p13) + (convert(nvarchar,datepart(month, [t58].[value])))) null)) or (([t13].[value4] not null) , ((((convert(nvarchar,datepart(year, [t58].[value]))) + @p13) + (convert(nvarchar,datepart(month, [t58].[value])))) not null) , ([t13].[value4] = (((convert(nvarchar,datepart(year, [t58].[value]))) + @p13) + (convert(nvarchar,datepart(month, [t58].[value]))))))) , ([t58].[value] >= @p5) , ([t58].[value] < @p6) , ((( select [t60].[orderlifecycleid] ( select top (1) [t59].[orderlifecycleid] [dbo].[orderorderlifecycle] [t59] [t59].[orderid] = [t58].[id] order [t59].[id] desc ) [t60] )) in (@p7, @p8, @p9)) , (exists( select null [empty] [dbo].[orderorderpaymentstatus] [t61] inner join [dbo].[orderpaymentstatus] [t62] on [t62].[id] = [t61].[orderpaymentstatusid] ([t62].[code] in (@p10, @p11, @p12)) , ([t61].[orderid] = [t58].[id]) )) ) [t63] )))) end)) [value], [t13].[value] [value2], [t13].[value2] [value22], [t13].[value3] ( select sum([t8].[value3]) [value], sum([t8].[totalamount]) [value2], sum([t8].[value22]) [value3], [t8].[value] [value4] ( select ((convert(nvarchar,datepart(year, [t7].[value]))) + @p4) + (convert(nvarchar,datepart(month, [t7].[value]))) [value], [t7].[value] [value2], [t7].[id], [t7].[value3], [t7].[totalamount], [t7].[value2] [value22] ( select ( select [t3].[datetime] ( select top (1) [t1].[datetime] [dbo].[orderorderpaymentstatus] [t1] inner join [dbo].[orderpaymentstatus] [t2] on [t2].[id] = [t1].[orderpaymentstatusid] ([t2].[code] in (@p0, @p1, @p2)) , ([t1].[orderid] = [t0].[id]) ) [t3] ) [value], [t0].[totalamount], ( select sum([t5].[value]) ( select coalesce([t4].[btwamount],@p3) [value], [t4].[orderid] [dbo].[orderitem] [t4] ) [t5] [t5].[orderid] = [t0].[id] ) [value2], ( select count(*) [dbo].[orderitem] [t6] [t6].[orderid] = [t0].[id] ) [value3], [t0].[id] [dbo].[order] [t0] ) [t7] ) [t8] ([t8].[value2] >= @p5) , ([t8].[value2] < @p6) , ((( select [t10].[orderlifecycleid] ( select top (1) [t9].[orderlifecycleid] [dbo].[orderorderlifecycle] [t9] [t9].[orderid] = [t8].[id] order [t9].[id] desc ) [t10] )) in (@p7, @p8, @p9)) , (exists( select null [empty] [dbo].[orderorderpaymentstatus] [t11] inner join [dbo].[orderpaymentstatus] [t12] on [t12].[id] = [t11].[orderpaymentstatusid] ([t12].[code] in (@p10, @p11, @p12)) , ([t11].[orderid] = [t8].[id]) )) group [t8].[value] ) [t13] ) [t64] order [t64].[value]
one obvious way improve query catches eye in first part:
var items = db.orders.where(item => (siteid == null || item.siteid == siteid) && (ls_list.contains(item.orderorderlifecycles.orderbydescending(it => it.id).first().orderlifecycleid)) && (item.orderorderpaymentstatus.any(ops => statuspaymentsuccess.contains(ops.orderpaymentstatus.code)) && (cycleid == null || item.orderorderlifecycles.first().orderlifecycleid == cycleid) && (locationid == null || item.salelocationid == locationid) && (string.isnullorempty(salesperson) || item.employeename.tolower() == salesperson.tolower())) ); remember whole linq statement translated sql, including these null checks. makes sql query needlessly complex , harder process query optimizer. (by way, show sql query belonging linq statement).
the recommended way deal nullable conditions compose query:
iqueryable<order> items = var items = db.orders; if(siteid != null) { items = items.where(item => item.siteid == siteid); } if (cycleid != null) { items = items.where(item => item.orderorderlifecycles.first().orderlifecycleid == cycleid); } // etc. another thing is
item.employeename.tolower() == salesperson.tolower() this converts employeename field values before search condition applied it. means index on employeename can't used (also know not sargable). think can remove tolower() calls. in sql query database collation of employeename field used , that's case-insensitive (ci) default.
finally, may consider execute grouping ...
groupby(item => item.firstpaydate.value.year + "-" + item.firstpaydate.value.month) ... in memory (linq objects) instead of in database. is:
return betweenorders.where(item => item.firstpaydate >= start && item.firstpaydate < stop) .asenumerable() // switch linq objects .groupby(... a grouping translated order by (not group by reasons beyond scope of answer) and, again, conversions of database field firstpaydate disables indexes. makes sql query less complex, , it's not heavy operation in memory.
Comments
Post a Comment