Linq Group by is taking lot of time whereas the same query in sql takes .2 seconds
3
i am trying to join 4 tables and group them accordingly and calculate a value
var query = from sd in sdddd
join ei in eiii.Where(ei1 => ei1.bi == 110) on sd.sdi equals ei.sdi
join pch in pchhhh.Where(bpch => bpch.bi == 110) on sd.sdi equals pch.sdi
join ass in baaaa.Where(pc => pc.bi == 110) on pch.bi equals ass.bi
where ei.bi == 110
group new { sd,ei,pch, ass} by new { sd.dis,sd.dn } into x
select new bs
{
val1 = x.key.dn,
val2 = x.key.dis,
val3 = x.key.sdi,
val4 = x.key.di,
val5 = x.Sum(a=>a.pch.ab == "A" ? (a.pch.ans * a.ass.prm) : (a.pch.sb == "B" ? (a.pch.ans * a.ass.pim) : 0))
};
The issue i found was with group by function. If i remove the groupby it return data very fast, but takes lot of time when using groupby
select sd.[sdi],sd.[dnum] as 'Dept #',sd.[desc],
format(sum(case when pch.[ab]='A' then pch.[ans]*ass.prm
else pch.[ans]*.pim end),'N2')
FROM [database].[sdddd] as sd
JOIN [database].[eiii] as ei on ei.[sdi] = sd.[sdi]
join [database].[pchhh] as pch on pch.[sdi]=sd.[sdi]
join [database].[ass] as ass on ass.bi = pch.bi
WHERE ei.bi=110
GROUP BY sd.[desc],sd.[dnum],sd.[sdi]
ORDER BY sd.[dnum]
Original sql query which works fine. I was just trying to convert this query of joining three different tables and grouping them together and converted into linq
c# mysql linq
edited Nov 20 at 20:40
maccettura
8,26931426
asked Nov 20 at 19:45
sandeep
162
|
show 1 more comment
3
i am trying to join 4 tables and group them accordingly and calculate a value
var query = from sd in sdddd
join ei in eiii.Where(ei1 => ei1.bi == 110) on sd.sdi equals ei.sdi
join pch in pchhhh.Where(bpch => bpch.bi == 110) on sd.sdi equals pch.sdi
join ass in baaaa.Where(pc => pc.bi == 110) on pch.bi equals ass.bi
where ei.bi == 110
group new { sd,ei,pch, ass} by new { sd.dis,sd.dn } into x
select new bs
{
val1 = x.key.dn,
val2 = x.key.dis,
val3 = x.key.sdi,
val4 = x.key.di,
val5 = x.Sum(a=>a.pch.ab == "A" ? (a.pch.ans * a.ass.prm) : (a.pch.sb == "B" ? (a.pch.ans * a.ass.pim) : 0))
};
The issue i found was with group by function. If i remove the groupby it return data very fast, but takes lot of time when using groupby
select sd.[sdi],sd.[dnum] as 'Dept #',sd.[desc],
format(sum(case when pch.[ab]='A' then pch.[ans]*ass.prm
else pch.[ans]*.pim end),'N2')
FROM [database].[sdddd] as sd
JOIN [database].[eiii] as ei on ei.[sdi] = sd.[sdi]
join [database].[pchhh] as pch on pch.[sdi]=sd.[sdi]
join [database].[ass] as ass on ass.bi = pch.bi
WHERE ei.bi=110
GROUP BY sd.[desc],sd.[dnum],sd.[sdi]
ORDER BY sd.[dnum]
Original sql query which works fine. I was just trying to convert this query of joining three different tables and grouping them together and converted into linq
c# mysql linq
edited Nov 20 at 20:40
maccettura
8,26931426
asked Nov 20 at 19:45
sandeep
162
Welcome to StackOverflow! Please review the FAQ to make sure you include all of the information needed to help answer this question for you.
– Tim
Nov 20 at 19:52
@tim I am not sure what additional data is required for this post. Please let me know what kind of data is required.
– sandeep
Nov 20 at 19:56
From your post it is hard to figure out what your actual question is. Also, you could include the actual SQL query that you're trying to duplicate in your Linq query.
– Tim
Nov 20 at 20:00
Something is wrong with yourselectaftergroup by.val1,val2,val3andval4should be taken fromx, for the first tow definitelyx.Key.dis,x.Key.dn, don't know for the other two.
– Ivan Stoev
Nov 20 at 20:13
I know the pomelo provider for mysql has not been able to (properly) implemented the groupby functionallity yet and perhaps due to some limitations in the source code of ef core. Enable sensitive data logging and you might notice the query being executed in-memory. That means a lot of unnecessary data is being pulled into memory before the groupby is executed.
– Silvermind
Nov 20 at 22:09
|
show 1 more comment
3
3
3
0
i am trying to join 4 tables and group them accordingly and calculate a value
var query = from sd in sdddd
join ei in eiii.Where(ei1 => ei1.bi == 110) on sd.sdi equals ei.sdi
join pch in pchhhh.Where(bpch => bpch.bi == 110) on sd.sdi equals pch.sdi
join ass in baaaa.Where(pc => pc.bi == 110) on pch.bi equals ass.bi
where ei.bi == 110
group new { sd,ei,pch, ass} by new { sd.dis,sd.dn } into x
select new bs
{
val1 = x.key.dn,
val2 = x.key.dis,
val3 = x.key.sdi,
val4 = x.key.di,
val5 = x.Sum(a=>a.pch.ab == "A" ? (a.pch.ans * a.ass.prm) : (a.pch.sb == "B" ? (a.pch.ans * a.ass.pim) : 0))
};
The issue i found was with group by function. If i remove the groupby it return data very fast, but takes lot of time when using groupby
select sd.[sdi],sd.[dnum] as 'Dept #',sd.[desc],
format(sum(case when pch.[ab]='A' then pch.[ans]*ass.prm
else pch.[ans]*.pim end),'N2')
FROM [database].[sdddd] as sd
JOIN [database].[eiii] as ei on ei.[sdi] = sd.[sdi]
join [database].[pchhh] as pch on pch.[sdi]=sd.[sdi]
join [database].[ass] as ass on ass.bi = pch.bi
WHERE ei.bi=110
GROUP BY sd.[desc],sd.[dnum],sd.[sdi]
ORDER BY sd.[dnum]
Original sql query which works fine. I was just trying to convert this query of joining three different tables and grouping them together and converted into linq
c# mysql linq
edited Nov 20 at 20:40
maccettura
8,26931426
asked Nov 20 at 19:45
sandeep
162
i am trying to join 4 tables and group them accordingly and calculate a value
var query = from sd in sdddd
join ei in eiii.Where(ei1 => ei1.bi == 110) on sd.sdi equals ei.sdi
join pch in pchhhh.Where(bpch => bpch.bi == 110) on sd.sdi equals pch.sdi
join ass in baaaa.Where(pc => pc.bi == 110) on pch.bi equals ass.bi
where ei.bi == 110
group new { sd,ei,pch, ass} by new { sd.dis,sd.dn } into x
select new bs
{
val1 = x.key.dn,
val2 = x.key.dis,
val3 = x.key.sdi,
val4 = x.key.di,
val5 = x.Sum(a=>a.pch.ab == "A" ? (a.pch.ans * a.ass.prm) : (a.pch.sb == "B" ? (a.pch.ans * a.ass.pim) : 0))
};
The issue i found was with group by function. If i remove the groupby it return data very fast, but takes lot of time when using groupby
select sd.[sdi],sd.[dnum] as 'Dept #',sd.[desc],
format(sum(case when pch.[ab]='A' then pch.[ans]*ass.prm
else pch.[ans]*.pim end),'N2')
FROM [database].[sdddd] as sd
JOIN [database].[eiii] as ei on ei.[sdi] = sd.[sdi]
join [database].[pchhh] as pch on pch.[sdi]=sd.[sdi]
join [database].[ass] as ass on ass.bi = pch.bi
WHERE ei.bi=110
GROUP BY sd.[desc],sd.[dnum],sd.[sdi]
ORDER BY sd.[dnum]
Original sql query which works fine. I was just trying to convert this query of joining three different tables and grouping them together and converted into linq
c# mysql linq
c# mysql linq
edited Nov 20 at 20:40
maccettura
8,26931426
asked Nov 20 at 19:45
sandeep
162
edited Nov 20 at 20:40
maccettura
8,26931426
asked Nov 20 at 19:45
sandeep
162
edited Nov 20 at 20:40
maccettura
8,26931426
edited Nov 20 at 20:40
maccettura
8,26931426
edited Nov 20 at 20:40
maccettura
8,26931426
8,26931426
asked Nov 20 at 19:45
sandeep
162
asked Nov 20 at 19:45
sandeep
162
asked Nov 20 at 19:45
sandeep
162
162
Welcome to StackOverflow! Please review the FAQ to make sure you include all of the information needed to help answer this question for you.
– Tim
Nov 20 at 19:52
@tim I am not sure what additional data is required for this post. Please let me know what kind of data is required.
– sandeep
Nov 20 at 19:56
From your post it is hard to figure out what your actual question is. Also, you could include the actual SQL query that you're trying to duplicate in your Linq query.
– Tim
Nov 20 at 20:00
Something is wrong with yourselectaftergroup by.val1,val2,val3andval4should be taken fromx, for the first tow definitelyx.Key.dis,x.Key.dn, don't know for the other two.
– Ivan Stoev
Nov 20 at 20:13
I know the pomelo provider for mysql has not been able to (properly) implemented the groupby functionallity yet and perhaps due to some limitations in the source code of ef core. Enable sensitive data logging and you might notice the query being executed in-memory. That means a lot of unnecessary data is being pulled into memory before the groupby is executed.
– Silvermind
Nov 20 at 22:09
|
show 1 more comment
Welcome to StackOverflow! Please review the FAQ to make sure you include all of the information needed to help answer this question for you.
– Tim
Nov 20 at 19:52
@tim I am not sure what additional data is required for this post. Please let me know what kind of data is required.
– sandeep
Nov 20 at 19:56
From your post it is hard to figure out what your actual question is. Also, you could include the actual SQL query that you're trying to duplicate in your Linq query.
– Tim
Nov 20 at 20:00
Something is wrong with yourselectaftergroup by.val1,val2,val3andval4should be taken fromx, for the first tow definitelyx.Key.dis,x.Key.dn, don't know for the other two.
– Ivan Stoev
Nov 20 at 20:13
I know the pomelo provider for mysql has not been able to (properly) implemented the groupby functionallity yet and perhaps due to some limitations in the source code of ef core. Enable sensitive data logging and you might notice the query being executed in-memory. That means a lot of unnecessary data is being pulled into memory before the groupby is executed.
– Silvermind
Nov 20 at 22:09
Welcome to StackOverflow! Please review the FAQ to make sure you include all of the information needed to help answer this question for you.
– Tim
Nov 20 at 19:52
Welcome to StackOverflow! Please review the FAQ to make sure you include all of the information needed to help answer this question for you.
– Tim
Nov 20 at 19:52
@tim I am not sure what additional data is required for this post. Please let me know what kind of data is required.
– sandeep
Nov 20 at 19:56
@tim I am not sure what additional data is required for this post. Please let me know what kind of data is required.
– sandeep
Nov 20 at 19:56
From your post it is hard to figure out what your actual question is. Also, you could include the actual SQL query that you're trying to duplicate in your Linq query.
– Tim
Nov 20 at 20:00
From your post it is hard to figure out what your actual question is. Also, you could include the actual SQL query that you're trying to duplicate in your Linq query.
– Tim
Nov 20 at 20:00
Something is wrong with your
select after group by. val1, val2, val3 and val4 should be taken from x, for the first tow definitely x.Key.dis, x.Key.dn, don't know for the other two.– Ivan Stoev
Nov 20 at 20:13
Something is wrong with your
select after group by. val1, val2, val3 and val4 should be taken from x, for the first tow definitely x.Key.dis, x.Key.dn, don't know for the other two.– Ivan Stoev
Nov 20 at 20:13
I know the pomelo provider for mysql has not been able to (properly) implemented the groupby functionallity yet and perhaps due to some limitations in the source code of ef core. Enable sensitive data logging and you might notice the query being executed in-memory. That means a lot of unnecessary data is being pulled into memory before the groupby is executed.
– Silvermind
Nov 20 at 22:09
I know the pomelo provider for mysql has not been able to (properly) implemented the groupby functionallity yet and perhaps due to some limitations in the source code of ef core. Enable sensitive data logging and you might notice the query being executed in-memory. That means a lot of unnecessary data is being pulled into memory before the groupby is executed.
– Silvermind
Nov 20 at 22:09
|
show 1 more comment
active
oldest
votes
Your Answer
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%2f53400463%2flinq-group-by-is-taking-lot-of-time-whereas-the-same-query-in-sql-takes-2-secon%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
active
oldest
votes
active
oldest
votes
active
oldest
votes
active
oldest
votes
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.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- 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%2f53400463%2flinq-group-by-is-taking-lot-of-time-whereas-the-same-query-in-sql-takes-2-secon%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
Welcome to StackOverflow! Please review the FAQ to make sure you include all of the information needed to help answer this question for you.
– Tim
Nov 20 at 19:52
@tim I am not sure what additional data is required for this post. Please let me know what kind of data is required.
– sandeep
Nov 20 at 19:56
From your post it is hard to figure out what your actual question is. Also, you could include the actual SQL query that you're trying to duplicate in your Linq query.
– Tim
Nov 20 at 20:00
Something is wrong with your
selectaftergroup by.val1,val2,val3andval4should be taken fromx, for the first tow definitelyx.Key.dis,x.Key.dn, don't know for the other two.– Ivan Stoev
Nov 20 at 20:13
I know the pomelo provider for mysql has not been able to (properly) implemented the groupby functionallity yet and perhaps due to some limitations in the source code of ef core. Enable sensitive data logging and you might notice the query being executed in-memory. That means a lot of unnecessary data is being pulled into memory before the groupby is executed.
– Silvermind
Nov 20 at 22:09