Linq query taking too long time to execute query than sql
0
We are moving our back end sql logic to front end and we are handling back end sql as Linq query in front end C#. It working fine but it take too long time to execute the query than sql. The below my code for your reference,
objTypeDefLst = (from t1 in objTypeDefLst
join t2 in objTypeDefLst
on t1.TypeDefid equals TUtil.CheckInt(t2.ParentId, 0)
where t1.TypeDefGroup.ToUpper().Trim() == strTypeDefGrp.ToUpper().Trim()
orderby (t1.TypeDefDesc == "Successful" && t1.TypeDefGroup == "ResponseType" ? 1 :
(t1.TypeDefDesc == "Failed" && t1.TypeDefGroup == "ResponseType" ? 2 :
(t1.TypeDefDesc == "Failed Attempt" && t1.TypeDefGroup == "ResponseType" ? 3 : 4)))
select new TypeDefinition
{
ResponseTypeReason = (t1.TypeDefDesc + ":" + t2.TypeDefDesc),
ResponseTypeCode = t1.TypeDefid + "~" + t2.TypeDefcode
}).ToList();
c# linq
asked Nov 23 '18 at 11:19
Sivaraj GanesanSivaraj Ganesan
169
|
show 5 more comments
0
We are moving our back end sql logic to front end and we are handling back end sql as Linq query in front end C#. It working fine but it take too long time to execute the query than sql. The below my code for your reference,
objTypeDefLst = (from t1 in objTypeDefLst
join t2 in objTypeDefLst
on t1.TypeDefid equals TUtil.CheckInt(t2.ParentId, 0)
where t1.TypeDefGroup.ToUpper().Trim() == strTypeDefGrp.ToUpper().Trim()
orderby (t1.TypeDefDesc == "Successful" && t1.TypeDefGroup == "ResponseType" ? 1 :
(t1.TypeDefDesc == "Failed" && t1.TypeDefGroup == "ResponseType" ? 2 :
(t1.TypeDefDesc == "Failed Attempt" && t1.TypeDefGroup == "ResponseType" ? 3 : 4)))
select new TypeDefinition
{
ResponseTypeReason = (t1.TypeDefDesc + ":" + t2.TypeDefDesc),
ResponseTypeCode = t1.TypeDefid + "~" + t2.TypeDefcode
}).ToList();
c# linq
asked Nov 23 '18 at 11:19
Sivaraj GanesanSivaraj Ganesan
169
1
What'sTUtil.CheckInt? Why does it work if it's an entity framework query, is it? If it's not a EF-query you loaded all records into memory which is inefficient.
– Rango
Nov 23 '18 at 11:20
hi @Tim, Tutil.CheckInt is helper funtion to covert the object to integer.
– Sivaraj Ganesan
Nov 23 '18 at 12:04
So then the question remains why that works at all. Entity framework should not be able to convert this function to a SQL-query. So i'm thinking that this is actually a Linq-To-Objects query which explains why it's so slow. You are loading your database table into memory, intoobjTypeDefLstwhich seems to be a list. Instead you should use a pure Linq-To-Entities query without an intermediate list.
– Rango
Nov 23 '18 at 12:09
Table 'TypeDefinition' having more hit on database side. Also the table having 2000 rows only. So that we are planning memcached all the table records and and retrieve it while needed by Linq in the above.
– Sivaraj Ganesan
Nov 23 '18 at 12:10
Ok, but then don't expect database performance. A database will always win when it comes to complex sorting and joins.
– Rango
Nov 23 '18 at 12:12
|
show 5 more comments
0
0
0
We are moving our back end sql logic to front end and we are handling back end sql as Linq query in front end C#. It working fine but it take too long time to execute the query than sql. The below my code for your reference,
objTypeDefLst = (from t1 in objTypeDefLst
join t2 in objTypeDefLst
on t1.TypeDefid equals TUtil.CheckInt(t2.ParentId, 0)
where t1.TypeDefGroup.ToUpper().Trim() == strTypeDefGrp.ToUpper().Trim()
orderby (t1.TypeDefDesc == "Successful" && t1.TypeDefGroup == "ResponseType" ? 1 :
(t1.TypeDefDesc == "Failed" && t1.TypeDefGroup == "ResponseType" ? 2 :
(t1.TypeDefDesc == "Failed Attempt" && t1.TypeDefGroup == "ResponseType" ? 3 : 4)))
select new TypeDefinition
{
ResponseTypeReason = (t1.TypeDefDesc + ":" + t2.TypeDefDesc),
ResponseTypeCode = t1.TypeDefid + "~" + t2.TypeDefcode
}).ToList();
c# linq
asked Nov 23 '18 at 11:19
Sivaraj GanesanSivaraj Ganesan
169
We are moving our back end sql logic to front end and we are handling back end sql as Linq query in front end C#. It working fine but it take too long time to execute the query than sql. The below my code for your reference,
objTypeDefLst = (from t1 in objTypeDefLst
join t2 in objTypeDefLst
on t1.TypeDefid equals TUtil.CheckInt(t2.ParentId, 0)
where t1.TypeDefGroup.ToUpper().Trim() == strTypeDefGrp.ToUpper().Trim()
orderby (t1.TypeDefDesc == "Successful" && t1.TypeDefGroup == "ResponseType" ? 1 :
(t1.TypeDefDesc == "Failed" && t1.TypeDefGroup == "ResponseType" ? 2 :
(t1.TypeDefDesc == "Failed Attempt" && t1.TypeDefGroup == "ResponseType" ? 3 : 4)))
select new TypeDefinition
{
ResponseTypeReason = (t1.TypeDefDesc + ":" + t2.TypeDefDesc),
ResponseTypeCode = t1.TypeDefid + "~" + t2.TypeDefcode
}).ToList();
c# linq
c# linq
asked Nov 23 '18 at 11:19
Sivaraj GanesanSivaraj Ganesan
169
asked Nov 23 '18 at 11:19
Sivaraj GanesanSivaraj Ganesan
169
asked Nov 23 '18 at 11:19
Sivaraj GanesanSivaraj Ganesan
169
asked Nov 23 '18 at 11:19
Sivaraj GanesanSivaraj Ganesan
169
asked Nov 23 '18 at 11:19
Sivaraj GanesanSivaraj Ganesan
169
169
1
What'sTUtil.CheckInt? Why does it work if it's an entity framework query, is it? If it's not a EF-query you loaded all records into memory which is inefficient.
– Rango
Nov 23 '18 at 11:20
hi @Tim, Tutil.CheckInt is helper funtion to covert the object to integer.
– Sivaraj Ganesan
Nov 23 '18 at 12:04
So then the question remains why that works at all. Entity framework should not be able to convert this function to a SQL-query. So i'm thinking that this is actually a Linq-To-Objects query which explains why it's so slow. You are loading your database table into memory, intoobjTypeDefLstwhich seems to be a list. Instead you should use a pure Linq-To-Entities query without an intermediate list.
– Rango
Nov 23 '18 at 12:09
Table 'TypeDefinition' having more hit on database side. Also the table having 2000 rows only. So that we are planning memcached all the table records and and retrieve it while needed by Linq in the above.
– Sivaraj Ganesan
Nov 23 '18 at 12:10
Ok, but then don't expect database performance. A database will always win when it comes to complex sorting and joins.
– Rango
Nov 23 '18 at 12:12
|
show 5 more comments
1
What'sTUtil.CheckInt? Why does it work if it's an entity framework query, is it? If it's not a EF-query you loaded all records into memory which is inefficient.
– Rango
Nov 23 '18 at 11:20
hi @Tim, Tutil.CheckInt is helper funtion to covert the object to integer.
– Sivaraj Ganesan
Nov 23 '18 at 12:04
So then the question remains why that works at all. Entity framework should not be able to convert this function to a SQL-query. So i'm thinking that this is actually a Linq-To-Objects query which explains why it's so slow. You are loading your database table into memory, intoobjTypeDefLstwhich seems to be a list. Instead you should use a pure Linq-To-Entities query without an intermediate list.
– Rango
Nov 23 '18 at 12:09
Table 'TypeDefinition' having more hit on database side. Also the table having 2000 rows only. So that we are planning memcached all the table records and and retrieve it while needed by Linq in the above.
– Sivaraj Ganesan
Nov 23 '18 at 12:10
Ok, but then don't expect database performance. A database will always win when it comes to complex sorting and joins.
– Rango
Nov 23 '18 at 12:12
1
1
What's
TUtil.CheckInt? Why does it work if it's an entity framework query, is it? If it's not a EF-query you loaded all records into memory which is inefficient.– Rango
Nov 23 '18 at 11:20
What's
TUtil.CheckInt? Why does it work if it's an entity framework query, is it? If it's not a EF-query you loaded all records into memory which is inefficient.– Rango
Nov 23 '18 at 11:20
hi @Tim, Tutil.CheckInt is helper funtion to covert the object to integer.
– Sivaraj Ganesan
Nov 23 '18 at 12:04
hi @Tim, Tutil.CheckInt is helper funtion to covert the object to integer.
– Sivaraj Ganesan
Nov 23 '18 at 12:04
So then the question remains why that works at all. Entity framework should not be able to convert this function to a SQL-query. So i'm thinking that this is actually a Linq-To-Objects query which explains why it's so slow. You are loading your database table into memory, into
objTypeDefLst which seems to be a list. Instead you should use a pure Linq-To-Entities query without an intermediate list.– Rango
Nov 23 '18 at 12:09
So then the question remains why that works at all. Entity framework should not be able to convert this function to a SQL-query. So i'm thinking that this is actually a Linq-To-Objects query which explains why it's so slow. You are loading your database table into memory, into
objTypeDefLst which seems to be a list. Instead you should use a pure Linq-To-Entities query without an intermediate list.– Rango
Nov 23 '18 at 12:09
Table 'TypeDefinition' having more hit on database side. Also the table having 2000 rows only. So that we are planning memcached all the table records and and retrieve it while needed by Linq in the above.
– Sivaraj Ganesan
Nov 23 '18 at 12:10
Table 'TypeDefinition' having more hit on database side. Also the table having 2000 rows only. So that we are planning memcached all the table records and and retrieve it while needed by Linq in the above.
– Sivaraj Ganesan
Nov 23 '18 at 12:10
Ok, but then don't expect database performance. A database will always win when it comes to complex sorting and joins.
– Rango
Nov 23 '18 at 12:12
Ok, but then don't expect database performance. A database will always win when it comes to complex sorting and joins.
– Rango
Nov 23 '18 at 12:12
|
show 5 more comments
1 Answer
1
active
oldest
votes
0
After altering the helper function TUtil.CheckInt(t2.ParentId, 0) with string.IsNullOrEmpty(t2.ParentId) ? 0 : Convert.ToInt32(t2.ParentId)it is working faster.
answered Nov 23 '18 at 12:40
Sivaraj GanesanSivaraj Ganesan
169
add a comment |
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53445728%2flinq-query-taking-too-long-time-to-execute-query-than-sql%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
0
After altering the helper function TUtil.CheckInt(t2.ParentId, 0) with string.IsNullOrEmpty(t2.ParentId) ? 0 : Convert.ToInt32(t2.ParentId)it is working faster.
answered Nov 23 '18 at 12:40
Sivaraj GanesanSivaraj Ganesan
169
add a comment |
0
After altering the helper function TUtil.CheckInt(t2.ParentId, 0) with string.IsNullOrEmpty(t2.ParentId) ? 0 : Convert.ToInt32(t2.ParentId)it is working faster.
answered Nov 23 '18 at 12:40
Sivaraj GanesanSivaraj Ganesan
169
add a comment |
0
0
0
After altering the helper function TUtil.CheckInt(t2.ParentId, 0) with string.IsNullOrEmpty(t2.ParentId) ? 0 : Convert.ToInt32(t2.ParentId)it is working faster.
answered Nov 23 '18 at 12:40
Sivaraj GanesanSivaraj Ganesan
169
After altering the helper function TUtil.CheckInt(t2.ParentId, 0) with string.IsNullOrEmpty(t2.ParentId) ? 0 : Convert.ToInt32(t2.ParentId)it is working faster.
answered Nov 23 '18 at 12:40
Sivaraj GanesanSivaraj Ganesan
169
answered Nov 23 '18 at 12:40
Sivaraj GanesanSivaraj Ganesan
169
answered Nov 23 '18 at 12:40
Sivaraj GanesanSivaraj Ganesan
169
answered Nov 23 '18 at 12:40
Sivaraj GanesanSivaraj Ganesan
169
169
add a comment |
add a comment |
draft saved
draft discarded
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53445728%2flinq-query-taking-too-long-time-to-execute-query-than-sql%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
1
What's
TUtil.CheckInt? Why does it work if it's an entity framework query, is it? If it's not a EF-query you loaded all records into memory which is inefficient.– Rango
Nov 23 '18 at 11:20
hi @Tim, Tutil.CheckInt is helper funtion to covert the object to integer.
– Sivaraj Ganesan
Nov 23 '18 at 12:04
So then the question remains why that works at all. Entity framework should not be able to convert this function to a SQL-query. So i'm thinking that this is actually a Linq-To-Objects query which explains why it's so slow. You are loading your database table into memory, into
objTypeDefLstwhich seems to be a list. Instead you should use a pure Linq-To-Entities query without an intermediate list.– Rango
Nov 23 '18 at 12:09
Table 'TypeDefinition' having more hit on database side. Also the table having 2000 rows only. So that we are planning memcached all the table records and and retrieve it while needed by Linq in the above.
– Sivaraj Ganesan
Nov 23 '18 at 12:10
Ok, but then don't expect database performance. A database will always win when it comes to complex sorting and joins.
– Rango
Nov 23 '18 at 12:12