Mixing
SQL - Very slow retrieval of records
I have a [User] table which has 35k records. I also have another table [UserNote], which has about 1M records.
When I do a LEFT JOIN with the User and UserNote tables, it takes a long time to run a query.
I tried adding index to the tables but no luck!
Is there any ways to overcome this issue?
Query:
SELECT u.Name, un.Notes
FROM [User] u
LEFT JOIN [UserNote] un ON un.UserID = u.UserID
ORDER BY u.Name
Thanks
sql
edited Nov 21 '18 at 20:32
marc_s
577k12911141259
asked Nov 21 '18 at 17:00
AjithAjith
107
|
show 6 more comments
I have a [User] table which has 35k records. I also have another table [UserNote], which has about 1M records.
When I do a LEFT JOIN with the User and UserNote tables, it takes a long time to run a query.
I tried adding index to the tables but no luck!
Is there any ways to overcome this issue?
Query:
SELECT u.Name, un.Notes
FROM [User] u
LEFT JOIN [UserNote] un ON un.UserID = u.UserID
ORDER BY u.Name
Thanks
sql
edited Nov 21 '18 at 20:32
marc_s
577k12911141259
asked Nov 21 '18 at 17:00
AjithAjith
107
post your query
– nikhil sugandh
Nov 21 '18 at 17:04
Added, please check the edited question.
– Ajith
Nov 21 '18 at 17:07
could you add execution plan as well. In first instance I assume UserId is INT. Did you try to add INDEX to the User.name?
– Zeki Gumus
Nov 21 '18 at 17:21
which columns you have added as index on?
– Himanshu Ahuja
Nov 21 '18 at 17:52
I added INDEX to the notes column. UserID having clustered index since it is a primary key column!
– Ajith
Nov 21 '18 at 18:55
|
show 6 more comments
I have a [User] table which has 35k records. I also have another table [UserNote], which has about 1M records.
When I do a LEFT JOIN with the User and UserNote tables, it takes a long time to run a query.
I tried adding index to the tables but no luck!
Is there any ways to overcome this issue?
Query:
SELECT u.Name, un.Notes
FROM [User] u
LEFT JOIN [UserNote] un ON un.UserID = u.UserID
ORDER BY u.Name
Thanks
sql
edited Nov 21 '18 at 20:32
marc_s
577k12911141259
asked Nov 21 '18 at 17:00
AjithAjith
107
I have a [User] table which has 35k records. I also have another table [UserNote], which has about 1M records.
When I do a LEFT JOIN with the User and UserNote tables, it takes a long time to run a query.
I tried adding index to the tables but no luck!
Is there any ways to overcome this issue?
Query:
SELECT u.Name, un.Notes
FROM [User] u
LEFT JOIN [UserNote] un ON un.UserID = u.UserID
ORDER BY u.Name
Thanks
sql
sql
edited Nov 21 '18 at 20:32
marc_s
577k12911141259
asked Nov 21 '18 at 17:00
AjithAjith
107
edited Nov 21 '18 at 20:32
marc_s
577k12911141259
asked Nov 21 '18 at 17:00
AjithAjith
107
edited Nov 21 '18 at 20:32
marc_s
577k12911141259
edited Nov 21 '18 at 20:32
marc_s
577k12911141259
edited Nov 21 '18 at 20:32
marc_s
577k12911141259
577k12911141259
asked Nov 21 '18 at 17:00
AjithAjith
107
asked Nov 21 '18 at 17:00
AjithAjith
107
asked Nov 21 '18 at 17:00
AjithAjith
107
107
post your query
– nikhil sugandh
Nov 21 '18 at 17:04
Added, please check the edited question.
– Ajith
Nov 21 '18 at 17:07
could you add execution plan as well. In first instance I assume UserId is INT. Did you try to add INDEX to the User.name?
– Zeki Gumus
Nov 21 '18 at 17:21
which columns you have added as index on?
– Himanshu Ahuja
Nov 21 '18 at 17:52
I added INDEX to the notes column. UserID having clustered index since it is a primary key column!
– Ajith
Nov 21 '18 at 18:55
|
show 6 more comments
post your query
– nikhil sugandh
Nov 21 '18 at 17:04
Added, please check the edited question.
– Ajith
Nov 21 '18 at 17:07
could you add execution plan as well. In first instance I assume UserId is INT. Did you try to add INDEX to the User.name?
– Zeki Gumus
Nov 21 '18 at 17:21
which columns you have added as index on?
– Himanshu Ahuja
Nov 21 '18 at 17:52
I added INDEX to the notes column. UserID having clustered index since it is a primary key column!
– Ajith
Nov 21 '18 at 18:55
post your query
– nikhil sugandh
Nov 21 '18 at 17:04
post your query
– nikhil sugandh
Nov 21 '18 at 17:04
Added, please check the edited question.
– Ajith
Nov 21 '18 at 17:07
Added, please check the edited question.
– Ajith
Nov 21 '18 at 17:07
could you add execution plan as well. In first instance I assume UserId is INT. Did you try to add INDEX to the User.name?
– Zeki Gumus
Nov 21 '18 at 17:21
could you add execution plan as well. In first instance I assume UserId is INT. Did you try to add INDEX to the User.name?
– Zeki Gumus
Nov 21 '18 at 17:21
which columns you have added as index on?
– Himanshu Ahuja
Nov 21 '18 at 17:52
which columns you have added as index on?
– Himanshu Ahuja
Nov 21 '18 at 17:52
I added INDEX to the notes column. UserID having clustered index since it is a primary key column!
– Ajith
Nov 21 '18 at 18:55
I added INDEX to the notes column. UserID having clustered index since it is a primary key column!
– Ajith
Nov 21 '18 at 18:55
|
show 6 more comments
2 Answers
2
active
oldest
votes
ORDER BY u.Name
I'm willing to bet this is your culprit. You're producing a result of 35 million rows, and then you have to sort it on a column outside the join criteria. You don't say what a "long time" is, but I bet if you remove the order by, or restrict the output with a WHERE clause on u.Name, it will be much faster.
I assume u.UserID is unique, and un.UserID is not, because each user has many notes, else the notes would be in the user table. I assume un.UserID is the first column in some unique index on that table, perhaps implicitly defined by the table. If not, it probably should be.
You might have better luck with an inner join. But that advice, along with any speculation on how to formulate your query, depends on your implementation.
answered Nov 21 '18 at 22:55
James K. LowdenJames K. Lowden
5,34211025
Appropriate indexes would help. Acknowledging the indexes ability to fulfil both the sort and the join.
– MatBailie
Nov 21 '18 at 23:23
add a comment |
Add a Non Clustered index in UserNote table for UserID and include column Notes something like this
CREATE NONCLUSTERED INDEX UserId_Notes
ON UserNotes (UserId)
INCLUDE (Notes);
This is called as covering index, in this case, when you do a join and ask for notes, you query don't need to do a look up on the base table (User Notes), it have all the needed columns in non-clusetered index itself.
You can also think about adding UserId, Notes in the non-clustered index. But I would not suggest that. Please comment if it's not clear.
edited Nov 22 '18 at 7:34
MatBailie
59.6k1476111
answered Nov 21 '18 at 23:15
Muthaiah PLMuthaiah PL
469314
Also adding an index onUser(Name, UserID)may be more beneficial due to the OP sorting the output by Name.
– MatBailie
Nov 21 '18 at 23:20
Yeah agree, but that's in the users table, but this case definitely need the above index i specified. And I prefer writing relational queries (ORDER / SORT should be handled by the front end / application which displays the data) ,
– Muthaiah PL
Nov 21 '18 at 23:26
I disagree about sorting be application specific. Window / analytic functions, for example, depend on it. Also, in this case, if you assert the sorting shouldn't be done here, and it's likely that's the most expensive part of the query, you should state that in the answer.
– MatBailie
Nov 21 '18 at 23:29
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
});
}
});
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%2f53417141%2fsql-very-slow-retrieval-of-records%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
ORDER BY u.Name
I'm willing to bet this is your culprit. You're producing a result of 35 million rows, and then you have to sort it on a column outside the join criteria. You don't say what a "long time" is, but I bet if you remove the order by, or restrict the output with a WHERE clause on u.Name, it will be much faster.
I assume u.UserID is unique, and un.UserID is not, because each user has many notes, else the notes would be in the user table. I assume un.UserID is the first column in some unique index on that table, perhaps implicitly defined by the table. If not, it probably should be.
You might have better luck with an inner join. But that advice, along with any speculation on how to formulate your query, depends on your implementation.
answered Nov 21 '18 at 22:55
James K. LowdenJames K. Lowden
5,34211025
Appropriate indexes would help. Acknowledging the indexes ability to fulfil both the sort and the join.
– MatBailie
Nov 21 '18 at 23:23
add a comment |
ORDER BY u.Name
I'm willing to bet this is your culprit. You're producing a result of 35 million rows, and then you have to sort it on a column outside the join criteria. You don't say what a "long time" is, but I bet if you remove the order by, or restrict the output with a WHERE clause on u.Name, it will be much faster.
I assume u.UserID is unique, and un.UserID is not, because each user has many notes, else the notes would be in the user table. I assume un.UserID is the first column in some unique index on that table, perhaps implicitly defined by the table. If not, it probably should be.
You might have better luck with an inner join. But that advice, along with any speculation on how to formulate your query, depends on your implementation.
answered Nov 21 '18 at 22:55
James K. LowdenJames K. Lowden
5,34211025
Appropriate indexes would help. Acknowledging the indexes ability to fulfil both the sort and the join.
– MatBailie
Nov 21 '18 at 23:23
add a comment |
ORDER BY u.Name
I'm willing to bet this is your culprit. You're producing a result of 35 million rows, and then you have to sort it on a column outside the join criteria. You don't say what a "long time" is, but I bet if you remove the order by, or restrict the output with a WHERE clause on u.Name, it will be much faster.
I assume u.UserID is unique, and un.UserID is not, because each user has many notes, else the notes would be in the user table. I assume un.UserID is the first column in some unique index on that table, perhaps implicitly defined by the table. If not, it probably should be.
You might have better luck with an inner join. But that advice, along with any speculation on how to formulate your query, depends on your implementation.
answered Nov 21 '18 at 22:55
James K. LowdenJames K. Lowden
5,34211025
ORDER BY u.Name
I'm willing to bet this is your culprit. You're producing a result of 35 million rows, and then you have to sort it on a column outside the join criteria. You don't say what a "long time" is, but I bet if you remove the order by, or restrict the output with a WHERE clause on u.Name, it will be much faster.
I assume u.UserID is unique, and un.UserID is not, because each user has many notes, else the notes would be in the user table. I assume un.UserID is the first column in some unique index on that table, perhaps implicitly defined by the table. If not, it probably should be.
You might have better luck with an inner join. But that advice, along with any speculation on how to formulate your query, depends on your implementation.
answered Nov 21 '18 at 22:55
James K. LowdenJames K. Lowden
5,34211025
answered Nov 21 '18 at 22:55
James K. LowdenJames K. Lowden
5,34211025
answered Nov 21 '18 at 22:55
James K. LowdenJames K. Lowden
5,34211025
answered Nov 21 '18 at 22:55
James K. LowdenJames K. Lowden
5,34211025
5,34211025
Appropriate indexes would help. Acknowledging the indexes ability to fulfil both the sort and the join.
– MatBailie
Nov 21 '18 at 23:23
add a comment |
Appropriate indexes would help. Acknowledging the indexes ability to fulfil both the sort and the join.
– MatBailie
Nov 21 '18 at 23:23
Appropriate indexes would help. Acknowledging the indexes ability to fulfil both the sort and the join.
– MatBailie
Nov 21 '18 at 23:23
Appropriate indexes would help. Acknowledging the indexes ability to fulfil both the sort and the join.
– MatBailie
Nov 21 '18 at 23:23
add a comment |
Add a Non Clustered index in UserNote table for UserID and include column Notes something like this
CREATE NONCLUSTERED INDEX UserId_Notes
ON UserNotes (UserId)
INCLUDE (Notes);
This is called as covering index, in this case, when you do a join and ask for notes, you query don't need to do a look up on the base table (User Notes), it have all the needed columns in non-clusetered index itself.
You can also think about adding UserId, Notes in the non-clustered index. But I would not suggest that. Please comment if it's not clear.
edited Nov 22 '18 at 7:34
MatBailie
59.6k1476111
answered Nov 21 '18 at 23:15
Muthaiah PLMuthaiah PL
469314
Also adding an index onUser(Name, UserID)may be more beneficial due to the OP sorting the output by Name.
– MatBailie
Nov 21 '18 at 23:20
Yeah agree, but that's in the users table, but this case definitely need the above index i specified. And I prefer writing relational queries (ORDER / SORT should be handled by the front end / application which displays the data) ,
– Muthaiah PL
Nov 21 '18 at 23:26
I disagree about sorting be application specific. Window / analytic functions, for example, depend on it. Also, in this case, if you assert the sorting shouldn't be done here, and it's likely that's the most expensive part of the query, you should state that in the answer.
– MatBailie
Nov 21 '18 at 23:29
add a comment |
Add a Non Clustered index in UserNote table for UserID and include column Notes something like this
CREATE NONCLUSTERED INDEX UserId_Notes
ON UserNotes (UserId)
INCLUDE (Notes);
This is called as covering index, in this case, when you do a join and ask for notes, you query don't need to do a look up on the base table (User Notes), it have all the needed columns in non-clusetered index itself.
You can also think about adding UserId, Notes in the non-clustered index. But I would not suggest that. Please comment if it's not clear.
edited Nov 22 '18 at 7:34
MatBailie
59.6k1476111
answered Nov 21 '18 at 23:15
Muthaiah PLMuthaiah PL
469314
Also adding an index onUser(Name, UserID)may be more beneficial due to the OP sorting the output by Name.
– MatBailie
Nov 21 '18 at 23:20
Yeah agree, but that's in the users table, but this case definitely need the above index i specified. And I prefer writing relational queries (ORDER / SORT should be handled by the front end / application which displays the data) ,
– Muthaiah PL
Nov 21 '18 at 23:26
I disagree about sorting be application specific. Window / analytic functions, for example, depend on it. Also, in this case, if you assert the sorting shouldn't be done here, and it's likely that's the most expensive part of the query, you should state that in the answer.
– MatBailie
Nov 21 '18 at 23:29
add a comment |
Add a Non Clustered index in UserNote table for UserID and include column Notes something like this
CREATE NONCLUSTERED INDEX UserId_Notes
ON UserNotes (UserId)
INCLUDE (Notes);
This is called as covering index, in this case, when you do a join and ask for notes, you query don't need to do a look up on the base table (User Notes), it have all the needed columns in non-clusetered index itself.
You can also think about adding UserId, Notes in the non-clustered index. But I would not suggest that. Please comment if it's not clear.
edited Nov 22 '18 at 7:34
MatBailie
59.6k1476111
answered Nov 21 '18 at 23:15
Muthaiah PLMuthaiah PL
469314
Add a Non Clustered index in UserNote table for UserID and include column Notes something like this
CREATE NONCLUSTERED INDEX UserId_Notes
ON UserNotes (UserId)
INCLUDE (Notes);
This is called as covering index, in this case, when you do a join and ask for notes, you query don't need to do a look up on the base table (User Notes), it have all the needed columns in non-clusetered index itself.
You can also think about adding UserId, Notes in the non-clustered index. But I would not suggest that. Please comment if it's not clear.
edited Nov 22 '18 at 7:34
MatBailie
59.6k1476111
answered Nov 21 '18 at 23:15
Muthaiah PLMuthaiah PL
469314
edited Nov 22 '18 at 7:34
MatBailie
59.6k1476111
edited Nov 22 '18 at 7:34
MatBailie
59.6k1476111
edited Nov 22 '18 at 7:34
MatBailie
59.6k1476111
59.6k1476111
answered Nov 21 '18 at 23:15
Muthaiah PLMuthaiah PL
469314
answered Nov 21 '18 at 23:15
Muthaiah PLMuthaiah PL
469314
answered Nov 21 '18 at 23:15
Muthaiah PLMuthaiah PL
469314
469314
Also adding an index onUser(Name, UserID)may be more beneficial due to the OP sorting the output by Name.
– MatBailie
Nov 21 '18 at 23:20
Yeah agree, but that's in the users table, but this case definitely need the above index i specified. And I prefer writing relational queries (ORDER / SORT should be handled by the front end / application which displays the data) ,
– Muthaiah PL
Nov 21 '18 at 23:26
I disagree about sorting be application specific. Window / analytic functions, for example, depend on it. Also, in this case, if you assert the sorting shouldn't be done here, and it's likely that's the most expensive part of the query, you should state that in the answer.
– MatBailie
Nov 21 '18 at 23:29
add a comment |
Also adding an index onUser(Name, UserID)may be more beneficial due to the OP sorting the output by Name.
– MatBailie
Nov 21 '18 at 23:20
Yeah agree, but that's in the users table, but this case definitely need the above index i specified. And I prefer writing relational queries (ORDER / SORT should be handled by the front end / application which displays the data) ,
– Muthaiah PL
Nov 21 '18 at 23:26
I disagree about sorting be application specific. Window / analytic functions, for example, depend on it. Also, in this case, if you assert the sorting shouldn't be done here, and it's likely that's the most expensive part of the query, you should state that in the answer.
– MatBailie
Nov 21 '18 at 23:29
Also adding an index on
User(Name, UserID) may be more beneficial due to the OP sorting the output by Name.– MatBailie
Nov 21 '18 at 23:20
Also adding an index on
User(Name, UserID) may be more beneficial due to the OP sorting the output by Name.– MatBailie
Nov 21 '18 at 23:20
Yeah agree, but that's in the users table, but this case definitely need the above index i specified. And I prefer writing relational queries (ORDER / SORT should be handled by the front end / application which displays the data) ,
– Muthaiah PL
Nov 21 '18 at 23:26
Yeah agree, but that's in the users table, but this case definitely need the above index i specified. And I prefer writing relational queries (ORDER / SORT should be handled by the front end / application which displays the data) ,
– Muthaiah PL
Nov 21 '18 at 23:26
I disagree about sorting be application specific. Window / analytic functions, for example, depend on it. Also, in this case, if you assert the sorting shouldn't be done here, and it's likely that's the most expensive part of the query, you should state that in the answer.
– MatBailie
Nov 21 '18 at 23:29
I disagree about sorting be application specific. Window / analytic functions, for example, depend on it. Also, in this case, if you assert the sorting shouldn't be done here, and it's likely that's the most expensive part of the query, you should state that in the answer.
– MatBailie
Nov 21 '18 at 23:29
add a comment |
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.
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%2f53417141%2fsql-very-slow-retrieval-of-records%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
post your query
– nikhil sugandh
Nov 21 '18 at 17:04
Added, please check the edited question.
– Ajith
Nov 21 '18 at 17:07
could you add execution plan as well. In first instance I assume UserId is INT. Did you try to add INDEX to the User.name?
– Zeki Gumus
Nov 21 '18 at 17:21
which columns you have added as index on?
– Himanshu Ahuja
Nov 21 '18 at 17:52
I added INDEX to the notes column. UserID having clustered index since it is a primary key column!
– Ajith
Nov 21 '18 at 18:55