LINQ performance question when joining IEnumerable with IQueryable
1
I had some serious speed issues with the LINQ in this code (variable names have been changed)
var A = _service.GetA(param1, param2); // Returns Enumerable results
var results = (from b in _B.All() // _B.All() returns IQueryable
join c in _C.All() on b.Id equals c.Id // _C.All() returns IQueryable
join a in A on a.Id equals c.Id
where b.someId == id && a.boolVariable // A bool value
select new
{
...
}).ToList();
This LINQ took over 10 seconds to execute even though the number of rows in B and C tables were less than 100k.
I looked into this and by trial and error I managed to get the LINQ execution time to 200ms by changing the code to this:
var A = _service.GetA(param1, param2).Where(a => a.boolVariable); // Returns Enumerable results
var results = (from b in _B.All() // _B.All() returns IQueryable
join c in _C.All() on b.Id equals c.Id // _C.All() returns IQueryable
join a in A on a.Id equals c.Id
where b.someId == id
select new
{
...
}).ToList();
So my question is, why does this simple change have such drastic effects on the LINQ performance? The only change is that I filter the Enumerable list beforehand, the A enumerable has about 30 items before filtering and 15 after filtering.
c# .net performance linq
edited Nov 15 '18 at 21:48
David Tansey
4,46832242
asked Nov 15 '18 at 19:37
DadossDadoss
122
|
show 4 more comments
1
I had some serious speed issues with the LINQ in this code (variable names have been changed)
var A = _service.GetA(param1, param2); // Returns Enumerable results
var results = (from b in _B.All() // _B.All() returns IQueryable
join c in _C.All() on b.Id equals c.Id // _C.All() returns IQueryable
join a in A on a.Id equals c.Id
where b.someId == id && a.boolVariable // A bool value
select new
{
...
}).ToList();
This LINQ took over 10 seconds to execute even though the number of rows in B and C tables were less than 100k.
I looked into this and by trial and error I managed to get the LINQ execution time to 200ms by changing the code to this:
var A = _service.GetA(param1, param2).Where(a => a.boolVariable); // Returns Enumerable results
var results = (from b in _B.All() // _B.All() returns IQueryable
join c in _C.All() on b.Id equals c.Id // _C.All() returns IQueryable
join a in A on a.Id equals c.Id
where b.someId == id
select new
{
...
}).ToList();
So my question is, why does this simple change have such drastic effects on the LINQ performance? The only change is that I filter the Enumerable list beforehand, the A enumerable has about 30 items before filtering and 15 after filtering.
c# .net performance linq
edited Nov 15 '18 at 21:48
David Tansey
4,46832242
asked Nov 15 '18 at 19:37
DadossDadoss
122
1
When you ran a SQL Trace for the original code, what was being submitted to the database? When you ran a SQL Trace for the final code, what was being submitted to the database?
– mjwills
Nov 15 '18 at 20:28
I'm guessing that the order ofa.boolVariableandb.someId == idmight be reversed or something.
– Ringil
Nov 15 '18 at 20:33
Please update your question to show exactly what was submitted to the database in both instances. I think you forget to edit your question to include it.
– mjwills
Nov 15 '18 at 20:47
Please also show us the source code forGetA.
– mjwills
Nov 15 '18 at 20:48
1
@Dadoss If you aren't willing to show the source code, your question is much more likely to be closed. It is crucial that you show a Minimal, Complete, and Verifiable example. Many people asking questions think certain things are irrelevant. And they are often incorrect. It is crucially relevant. You are tying our hands behind our back when we are trying to help you. The mistake is almost certainly because that method returnsIEnumerable.
– mjwills
Nov 15 '18 at 20:51
|
show 4 more comments
1
1
1
1
I had some serious speed issues with the LINQ in this code (variable names have been changed)
var A = _service.GetA(param1, param2); // Returns Enumerable results
var results = (from b in _B.All() // _B.All() returns IQueryable
join c in _C.All() on b.Id equals c.Id // _C.All() returns IQueryable
join a in A on a.Id equals c.Id
where b.someId == id && a.boolVariable // A bool value
select new
{
...
}).ToList();
This LINQ took over 10 seconds to execute even though the number of rows in B and C tables were less than 100k.
I looked into this and by trial and error I managed to get the LINQ execution time to 200ms by changing the code to this:
var A = _service.GetA(param1, param2).Where(a => a.boolVariable); // Returns Enumerable results
var results = (from b in _B.All() // _B.All() returns IQueryable
join c in _C.All() on b.Id equals c.Id // _C.All() returns IQueryable
join a in A on a.Id equals c.Id
where b.someId == id
select new
{
...
}).ToList();
So my question is, why does this simple change have such drastic effects on the LINQ performance? The only change is that I filter the Enumerable list beforehand, the A enumerable has about 30 items before filtering and 15 after filtering.
c# .net performance linq
edited Nov 15 '18 at 21:48
David Tansey
4,46832242
asked Nov 15 '18 at 19:37
DadossDadoss
122
I had some serious speed issues with the LINQ in this code (variable names have been changed)
var A = _service.GetA(param1, param2); // Returns Enumerable results
var results = (from b in _B.All() // _B.All() returns IQueryable
join c in _C.All() on b.Id equals c.Id // _C.All() returns IQueryable
join a in A on a.Id equals c.Id
where b.someId == id && a.boolVariable // A bool value
select new
{
...
}).ToList();
This LINQ took over 10 seconds to execute even though the number of rows in B and C tables were less than 100k.
I looked into this and by trial and error I managed to get the LINQ execution time to 200ms by changing the code to this:
var A = _service.GetA(param1, param2).Where(a => a.boolVariable); // Returns Enumerable results
var results = (from b in _B.All() // _B.All() returns IQueryable
join c in _C.All() on b.Id equals c.Id // _C.All() returns IQueryable
join a in A on a.Id equals c.Id
where b.someId == id
select new
{
...
}).ToList();
So my question is, why does this simple change have such drastic effects on the LINQ performance? The only change is that I filter the Enumerable list beforehand, the A enumerable has about 30 items before filtering and 15 after filtering.
c# .net performance linq
c# .net performance linq
edited Nov 15 '18 at 21:48
David Tansey
4,46832242
asked Nov 15 '18 at 19:37
DadossDadoss
122
edited Nov 15 '18 at 21:48
David Tansey
4,46832242
asked Nov 15 '18 at 19:37
DadossDadoss
122
edited Nov 15 '18 at 21:48
David Tansey
4,46832242
edited Nov 15 '18 at 21:48
David Tansey
4,46832242
edited Nov 15 '18 at 21:48
David Tansey
4,46832242
4,46832242
asked Nov 15 '18 at 19:37
DadossDadoss
122
asked Nov 15 '18 at 19:37
DadossDadoss
122
asked Nov 15 '18 at 19:37
DadossDadoss
122
122
1
When you ran a SQL Trace for the original code, what was being submitted to the database? When you ran a SQL Trace for the final code, what was being submitted to the database?
– mjwills
Nov 15 '18 at 20:28
I'm guessing that the order ofa.boolVariableandb.someId == idmight be reversed or something.
– Ringil
Nov 15 '18 at 20:33
Please update your question to show exactly what was submitted to the database in both instances. I think you forget to edit your question to include it.
– mjwills
Nov 15 '18 at 20:47
Please also show us the source code forGetA.
– mjwills
Nov 15 '18 at 20:48
1
@Dadoss If you aren't willing to show the source code, your question is much more likely to be closed. It is crucial that you show a Minimal, Complete, and Verifiable example. Many people asking questions think certain things are irrelevant. And they are often incorrect. It is crucially relevant. You are tying our hands behind our back when we are trying to help you. The mistake is almost certainly because that method returnsIEnumerable.
– mjwills
Nov 15 '18 at 20:51
|
show 4 more comments
1
When you ran a SQL Trace for the original code, what was being submitted to the database? When you ran a SQL Trace for the final code, what was being submitted to the database?
– mjwills
Nov 15 '18 at 20:28
I'm guessing that the order ofa.boolVariableandb.someId == idmight be reversed or something.
– Ringil
Nov 15 '18 at 20:33
Please update your question to show exactly what was submitted to the database in both instances. I think you forget to edit your question to include it.
– mjwills
Nov 15 '18 at 20:47
Please also show us the source code forGetA.
– mjwills
Nov 15 '18 at 20:48
1
@Dadoss If you aren't willing to show the source code, your question is much more likely to be closed. It is crucial that you show a Minimal, Complete, and Verifiable example. Many people asking questions think certain things are irrelevant. And they are often incorrect. It is crucially relevant. You are tying our hands behind our back when we are trying to help you. The mistake is almost certainly because that method returnsIEnumerable.
– mjwills
Nov 15 '18 at 20:51
1
1
When you ran a SQL Trace for the original code, what was being submitted to the database? When you ran a SQL Trace for the final code, what was being submitted to the database?
– mjwills
Nov 15 '18 at 20:28
When you ran a SQL Trace for the original code, what was being submitted to the database? When you ran a SQL Trace for the final code, what was being submitted to the database?
– mjwills
Nov 15 '18 at 20:28
I'm guessing that the order of
a.boolVariable and b.someId == id might be reversed or something.– Ringil
Nov 15 '18 at 20:33
I'm guessing that the order of
a.boolVariable and b.someId == id might be reversed or something.– Ringil
Nov 15 '18 at 20:33
Please update your question to show exactly what was submitted to the database in both instances. I think you forget to edit your question to include it.
– mjwills
Nov 15 '18 at 20:47
Please update your question to show exactly what was submitted to the database in both instances. I think you forget to edit your question to include it.
– mjwills
Nov 15 '18 at 20:47
Please also show us the source code for
GetA.– mjwills
Nov 15 '18 at 20:48
Please also show us the source code for
GetA.– mjwills
Nov 15 '18 at 20:48
1
1
@Dadoss If you aren't willing to show the source code, your question is much more likely to be closed. It is crucial that you show a Minimal, Complete, and Verifiable example. Many people asking questions think certain things are irrelevant. And they are often incorrect. It is crucially relevant. You are tying our hands behind our back when we are trying to help you. The mistake is almost certainly because that method returns
IEnumerable.– mjwills
Nov 15 '18 at 20:51
@Dadoss If you aren't willing to show the source code, your question is much more likely to be closed. It is crucial that you show a Minimal, Complete, and Verifiable example. Many people asking questions think certain things are irrelevant. And they are often incorrect. It is crucially relevant. You are tying our hands behind our back when we are trying to help you. The mistake is almost certainly because that method returns
IEnumerable.– mjwills
Nov 15 '18 at 20:51
|
show 4 more comments
1 Answer
1
active
oldest
votes
2
In your first scenario: first it joins all the records in A which would take long time to join, then filters out for a.boolVariable.
In your second scenario you have a smaller subset of records for A prior to joining - of course this would take less time to join.
answered Nov 15 '18 at 20:01
Richard MneyanRichard Mneyan
456189
2
Even if you have 10 records vs 5 that would mean 10 times all other 100K records in those B & C vs 5 times 100K. Not to mention that all those records afterwards need to be filtered out (filtering 10 records vs 10000 records).
– Richard Mneyan
Nov 15 '18 at 20:06
@Dadoss So basically when you mix both IQueryable and IEnumerable there is a gray line on what will and it will not execute on the sql server. Now by using the IENumerable in the where clause (first case) it will force part of the query to be done into the memory. But in the second case the Entity Framework will make the sql a little bit better by making the last join into a virtual select or completely taking it off and adding it as where (in). All these though is just speculations taken out of various linq queries that i tried over the years
– Nick Polyderopoulos
Nov 15 '18 at 23:11
add a comment |
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%2f53326797%2flinq-performance-question-when-joining-ienumerable-with-iqueryable%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
2
In your first scenario: first it joins all the records in A which would take long time to join, then filters out for a.boolVariable.
In your second scenario you have a smaller subset of records for A prior to joining - of course this would take less time to join.
answered Nov 15 '18 at 20:01
Richard MneyanRichard Mneyan
456189
2
Even if you have 10 records vs 5 that would mean 10 times all other 100K records in those B & C vs 5 times 100K. Not to mention that all those records afterwards need to be filtered out (filtering 10 records vs 10000 records).
– Richard Mneyan
Nov 15 '18 at 20:06
@Dadoss So basically when you mix both IQueryable and IEnumerable there is a gray line on what will and it will not execute on the sql server. Now by using the IENumerable in the where clause (first case) it will force part of the query to be done into the memory. But in the second case the Entity Framework will make the sql a little bit better by making the last join into a virtual select or completely taking it off and adding it as where (in). All these though is just speculations taken out of various linq queries that i tried over the years
– Nick Polyderopoulos
Nov 15 '18 at 23:11
add a comment |
2
In your first scenario: first it joins all the records in A which would take long time to join, then filters out for a.boolVariable.
In your second scenario you have a smaller subset of records for A prior to joining - of course this would take less time to join.
answered Nov 15 '18 at 20:01
Richard MneyanRichard Mneyan
456189
2
Even if you have 10 records vs 5 that would mean 10 times all other 100K records in those B & C vs 5 times 100K. Not to mention that all those records afterwards need to be filtered out (filtering 10 records vs 10000 records).
– Richard Mneyan
Nov 15 '18 at 20:06
@Dadoss So basically when you mix both IQueryable and IEnumerable there is a gray line on what will and it will not execute on the sql server. Now by using the IENumerable in the where clause (first case) it will force part of the query to be done into the memory. But in the second case the Entity Framework will make the sql a little bit better by making the last join into a virtual select or completely taking it off and adding it as where (in). All these though is just speculations taken out of various linq queries that i tried over the years
– Nick Polyderopoulos
Nov 15 '18 at 23:11
add a comment |
2
2
2
In your first scenario: first it joins all the records in A which would take long time to join, then filters out for a.boolVariable.
In your second scenario you have a smaller subset of records for A prior to joining - of course this would take less time to join.
answered Nov 15 '18 at 20:01
Richard MneyanRichard Mneyan
456189
In your first scenario: first it joins all the records in A which would take long time to join, then filters out for a.boolVariable.
In your second scenario you have a smaller subset of records for A prior to joining - of course this would take less time to join.
answered Nov 15 '18 at 20:01
Richard MneyanRichard Mneyan
456189
answered Nov 15 '18 at 20:01
Richard MneyanRichard Mneyan
456189
answered Nov 15 '18 at 20:01
Richard MneyanRichard Mneyan
456189
answered Nov 15 '18 at 20:01
Richard MneyanRichard Mneyan
456189
456189
2
Even if you have 10 records vs 5 that would mean 10 times all other 100K records in those B & C vs 5 times 100K. Not to mention that all those records afterwards need to be filtered out (filtering 10 records vs 10000 records).
– Richard Mneyan
Nov 15 '18 at 20:06
@Dadoss So basically when you mix both IQueryable and IEnumerable there is a gray line on what will and it will not execute on the sql server. Now by using the IENumerable in the where clause (first case) it will force part of the query to be done into the memory. But in the second case the Entity Framework will make the sql a little bit better by making the last join into a virtual select or completely taking it off and adding it as where (in). All these though is just speculations taken out of various linq queries that i tried over the years
– Nick Polyderopoulos
Nov 15 '18 at 23:11
add a comment |
2
Even if you have 10 records vs 5 that would mean 10 times all other 100K records in those B & C vs 5 times 100K. Not to mention that all those records afterwards need to be filtered out (filtering 10 records vs 10000 records).
– Richard Mneyan
Nov 15 '18 at 20:06
@Dadoss So basically when you mix both IQueryable and IEnumerable there is a gray line on what will and it will not execute on the sql server. Now by using the IENumerable in the where clause (first case) it will force part of the query to be done into the memory. But in the second case the Entity Framework will make the sql a little bit better by making the last join into a virtual select or completely taking it off and adding it as where (in). All these though is just speculations taken out of various linq queries that i tried over the years
– Nick Polyderopoulos
Nov 15 '18 at 23:11
2
2
Even if you have 10 records vs 5 that would mean 10 times all other 100K records in those B & C vs 5 times 100K. Not to mention that all those records afterwards need to be filtered out (filtering 10 records vs 10000 records).
– Richard Mneyan
Nov 15 '18 at 20:06
Even if you have 10 records vs 5 that would mean 10 times all other 100K records in those B & C vs 5 times 100K. Not to mention that all those records afterwards need to be filtered out (filtering 10 records vs 10000 records).
– Richard Mneyan
Nov 15 '18 at 20:06
@Dadoss So basically when you mix both IQueryable and IEnumerable there is a gray line on what will and it will not execute on the sql server. Now by using the IENumerable in the where clause (first case) it will force part of the query to be done into the memory. But in the second case the Entity Framework will make the sql a little bit better by making the last join into a virtual select or completely taking it off and adding it as where (in). All these though is just speculations taken out of various linq queries that i tried over the years
– Nick Polyderopoulos
Nov 15 '18 at 23:11
@Dadoss So basically when you mix both IQueryable and IEnumerable there is a gray line on what will and it will not execute on the sql server. Now by using the IENumerable in the where clause (first case) it will force part of the query to be done into the memory. But in the second case the Entity Framework will make the sql a little bit better by making the last join into a virtual select or completely taking it off and adding it as where (in). All these though is just speculations taken out of various linq queries that i tried over the years
– Nick Polyderopoulos
Nov 15 '18 at 23:11
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%2f53326797%2flinq-performance-question-when-joining-ienumerable-with-iqueryable%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
When you ran a SQL Trace for the original code, what was being submitted to the database? When you ran a SQL Trace for the final code, what was being submitted to the database?
– mjwills
Nov 15 '18 at 20:28
I'm guessing that the order of
a.boolVariableandb.someId == idmight be reversed or something.– Ringil
Nov 15 '18 at 20:33
Please update your question to show exactly what was submitted to the database in both instances. I think you forget to edit your question to include it.
– mjwills
Nov 15 '18 at 20:47
Please also show us the source code for
GetA.– mjwills
Nov 15 '18 at 20:48
1
@Dadoss If you aren't willing to show the source code, your question is much more likely to be closed. It is crucial that you show a Minimal, Complete, and Verifiable example. Many people asking questions think certain things are irrelevant. And they are often incorrect. It is crucially relevant. You are tying our hands behind our back when we are trying to help you. The mistake is almost certainly because that method returns
IEnumerable.– mjwills
Nov 15 '18 at 20:51