Sql select slow on filter for key, but not for primary key, how can I hint the priority
I have 2 select, the first is superfast, and the second is 10x more slower. It seems to be first join everything from the table, than running the startdate filter, and the first case it is filtering for the id first and only than running the select.
And yes got it, first he is running the subselect, sees that it is only 400 record, and doing the rest. So how can I hint that without this workaround to run that start date filter first?
Tourneys table: 1M records (has index on StartDate)
TourneyPlayers table: 2M records (has index on TourneyId ofc)
Tourneys after the start date filter: 400 records
fast:
select *
from Tourneys t right join TourneyPlayers tp on t.Id = tp.TourneyId
where 1=1
and t.id in (select id from Tourneys where StartDate > DATEADD(hour,-1,GETDATE()))
slow:
select *
from Tourneys t, TourneyPlayers tp
where 1=1
and t.StartDate > DATEADD(hour,-1,GETDATE())
and t.Id = tp.TourneyId
sql-server
asked 10 mins ago
Visky MátéVisky Máté
115
add a comment |
I have 2 select, the first is superfast, and the second is 10x more slower. It seems to be first join everything from the table, than running the startdate filter, and the first case it is filtering for the id first and only than running the select.
And yes got it, first he is running the subselect, sees that it is only 400 record, and doing the rest. So how can I hint that without this workaround to run that start date filter first?
Tourneys table: 1M records (has index on StartDate)
TourneyPlayers table: 2M records (has index on TourneyId ofc)
Tourneys after the start date filter: 400 records
fast:
select *
from Tourneys t right join TourneyPlayers tp on t.Id = tp.TourneyId
where 1=1
and t.id in (select id from Tourneys where StartDate > DATEADD(hour,-1,GETDATE()))
slow:
select *
from Tourneys t, TourneyPlayers tp
where 1=1
and t.StartDate > DATEADD(hour,-1,GETDATE())
and t.Id = tp.TourneyId
sql-server
asked 10 mins ago
Visky MátéVisky Máté
115
add a comment |
I have 2 select, the first is superfast, and the second is 10x more slower. It seems to be first join everything from the table, than running the startdate filter, and the first case it is filtering for the id first and only than running the select.
And yes got it, first he is running the subselect, sees that it is only 400 record, and doing the rest. So how can I hint that without this workaround to run that start date filter first?
Tourneys table: 1M records (has index on StartDate)
TourneyPlayers table: 2M records (has index on TourneyId ofc)
Tourneys after the start date filter: 400 records
fast:
select *
from Tourneys t right join TourneyPlayers tp on t.Id = tp.TourneyId
where 1=1
and t.id in (select id from Tourneys where StartDate > DATEADD(hour,-1,GETDATE()))
slow:
select *
from Tourneys t, TourneyPlayers tp
where 1=1
and t.StartDate > DATEADD(hour,-1,GETDATE())
and t.Id = tp.TourneyId
sql-server
asked 10 mins ago
Visky MátéVisky Máté
115
I have 2 select, the first is superfast, and the second is 10x more slower. It seems to be first join everything from the table, than running the startdate filter, and the first case it is filtering for the id first and only than running the select.
And yes got it, first he is running the subselect, sees that it is only 400 record, and doing the rest. So how can I hint that without this workaround to run that start date filter first?
Tourneys table: 1M records (has index on StartDate)
TourneyPlayers table: 2M records (has index on TourneyId ofc)
Tourneys after the start date filter: 400 records
fast:
select *
from Tourneys t right join TourneyPlayers tp on t.Id = tp.TourneyId
where 1=1
and t.id in (select id from Tourneys where StartDate > DATEADD(hour,-1,GETDATE()))
slow:
select *
from Tourneys t, TourneyPlayers tp
where 1=1
and t.StartDate > DATEADD(hour,-1,GETDATE())
and t.Id = tp.TourneyId
sql-server
sql-server
asked 10 mins ago
Visky MátéVisky Máté
115
asked 10 mins ago
Visky MátéVisky Máté
115
asked 10 mins ago
Visky MátéVisky Máté
115
asked 10 mins ago
Visky MátéVisky Máté
115
asked 10 mins ago
Visky MátéVisky Máté
115
115
add a comment |
add a comment |
0
active
oldest
votes
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
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: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
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%2fdba.stackexchange.com%2fquestions%2f228738%2fsql-select-slow-on-filter-for-key-but-not-for-primary-key-how-can-i-hint-the-p%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
0
active
oldest
votes
0
active
oldest
votes
active
oldest
votes
active
oldest
votes
Thanks for contributing an answer to Database Administrators Stack Exchange!
- 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%2fdba.stackexchange.com%2fquestions%2f228738%2fsql-select-slow-on-filter-for-key-but-not-for-primary-key-how-can-i-hint-the-p%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
