Worst Query performance issue on date calculation
up vote
1
down vote
favorite
I have a stored procedure that is taking more than 2 hours to complete and I noticed the below SQL is taking close to 25 mins for updating a million+ values.There are multiple steps like this and all added up takes around 2+ hours to finish.
All it is doing is taking a column value i.e. birth_year and replacing it with age i.e. getdate() - birth_year.
SELECT id,
birth_month,
birth_year
INTO #birth_date
FROM table_name
WHERE birth_year IS NOT NULL
UPDATE table_name
SET birth_year = ( YEAR(GETDATE()) - bd.birth_year )
FROM #birth_date bd
INNER JOIN table_name ap ON ap.id= bd.id
WHERE bd.birth_month <= MONTH(GETDATE())
OR bd.birth_month IS NULL
UPDATE table_name
SET birth_year = ( YEAR(GETDATE()) - bd.birth_year - 1 )
FROM #birth_date bd
INNER JOIN table_name ap ON ap.id= bd.id
WHERE bd.birth_month > MONTH(GETDATE())
I replaced the above SQL with the below SQL and now instead of 25 mins, the below SQL takes less than a minute. I've verified the results and all seems good.Is there anything that I am missing? Is there a better or alternate way?
UPDATE table_name
SET birth_year = case
when birth_month <= MONTH(GETDATE()) OR birth_month IS NULL then (YEAR(GETDATE()) - birth_year)
when birth_month > MONTH(GETDATE()) then ( YEAR(GETDATE()) - birth_year - 1 )
else null
end
FROM table_name
performance sql sql-server
edited Aug 10 at 3:21
Stephen Rauch
3,76061530
asked Jul 27 at 3:14
VK_217
1063
add a comment |
up vote
1
down vote
favorite
I have a stored procedure that is taking more than 2 hours to complete and I noticed the below SQL is taking close to 25 mins for updating a million+ values.There are multiple steps like this and all added up takes around 2+ hours to finish.
All it is doing is taking a column value i.e. birth_year and replacing it with age i.e. getdate() - birth_year.
SELECT id,
birth_month,
birth_year
INTO #birth_date
FROM table_name
WHERE birth_year IS NOT NULL
UPDATE table_name
SET birth_year = ( YEAR(GETDATE()) - bd.birth_year )
FROM #birth_date bd
INNER JOIN table_name ap ON ap.id= bd.id
WHERE bd.birth_month <= MONTH(GETDATE())
OR bd.birth_month IS NULL
UPDATE table_name
SET birth_year = ( YEAR(GETDATE()) - bd.birth_year - 1 )
FROM #birth_date bd
INNER JOIN table_name ap ON ap.id= bd.id
WHERE bd.birth_month > MONTH(GETDATE())
I replaced the above SQL with the below SQL and now instead of 25 mins, the below SQL takes less than a minute. I've verified the results and all seems good.Is there anything that I am missing? Is there a better or alternate way?
UPDATE table_name
SET birth_year = case
when birth_month <= MONTH(GETDATE()) OR birth_month IS NULL then (YEAR(GETDATE()) - birth_year)
when birth_month > MONTH(GETDATE()) then ( YEAR(GETDATE()) - birth_year - 1 )
else null
end
FROM table_name
performance sql sql-server
edited Aug 10 at 3:21
Stephen Rauch
3,76061530
asked Jul 27 at 3:14
VK_217
1063
Please include your DB schema.
– Mast
Jul 27 at 7:44
1
Why do you update the age every month instead of calculating it on-the-fly either as calculated column or in a view?
– dnoeth
Aug 12 at 14:13
Why would you want to replace abirth_yearcolumn with the age, without renaming it?
– 200_success
Oct 9 at 5:18
add a comment |
up vote
1
down vote
favorite
up vote
1
down vote
favorite
I have a stored procedure that is taking more than 2 hours to complete and I noticed the below SQL is taking close to 25 mins for updating a million+ values.There are multiple steps like this and all added up takes around 2+ hours to finish.
All it is doing is taking a column value i.e. birth_year and replacing it with age i.e. getdate() - birth_year.
SELECT id,
birth_month,
birth_year
INTO #birth_date
FROM table_name
WHERE birth_year IS NOT NULL
UPDATE table_name
SET birth_year = ( YEAR(GETDATE()) - bd.birth_year )
FROM #birth_date bd
INNER JOIN table_name ap ON ap.id= bd.id
WHERE bd.birth_month <= MONTH(GETDATE())
OR bd.birth_month IS NULL
UPDATE table_name
SET birth_year = ( YEAR(GETDATE()) - bd.birth_year - 1 )
FROM #birth_date bd
INNER JOIN table_name ap ON ap.id= bd.id
WHERE bd.birth_month > MONTH(GETDATE())
I replaced the above SQL with the below SQL and now instead of 25 mins, the below SQL takes less than a minute. I've verified the results and all seems good.Is there anything that I am missing? Is there a better or alternate way?
UPDATE table_name
SET birth_year = case
when birth_month <= MONTH(GETDATE()) OR birth_month IS NULL then (YEAR(GETDATE()) - birth_year)
when birth_month > MONTH(GETDATE()) then ( YEAR(GETDATE()) - birth_year - 1 )
else null
end
FROM table_name
performance sql sql-server
edited Aug 10 at 3:21
Stephen Rauch
3,76061530
asked Jul 27 at 3:14
VK_217
1063
I have a stored procedure that is taking more than 2 hours to complete and I noticed the below SQL is taking close to 25 mins for updating a million+ values.There are multiple steps like this and all added up takes around 2+ hours to finish.
All it is doing is taking a column value i.e. birth_year and replacing it with age i.e. getdate() - birth_year.
SELECT id,
birth_month,
birth_year
INTO #birth_date
FROM table_name
WHERE birth_year IS NOT NULL
UPDATE table_name
SET birth_year = ( YEAR(GETDATE()) - bd.birth_year )
FROM #birth_date bd
INNER JOIN table_name ap ON ap.id= bd.id
WHERE bd.birth_month <= MONTH(GETDATE())
OR bd.birth_month IS NULL
UPDATE table_name
SET birth_year = ( YEAR(GETDATE()) - bd.birth_year - 1 )
FROM #birth_date bd
INNER JOIN table_name ap ON ap.id= bd.id
WHERE bd.birth_month > MONTH(GETDATE())
I replaced the above SQL with the below SQL and now instead of 25 mins, the below SQL takes less than a minute. I've verified the results and all seems good.Is there anything that I am missing? Is there a better or alternate way?
UPDATE table_name
SET birth_year = case
when birth_month <= MONTH(GETDATE()) OR birth_month IS NULL then (YEAR(GETDATE()) - birth_year)
when birth_month > MONTH(GETDATE()) then ( YEAR(GETDATE()) - birth_year - 1 )
else null
end
FROM table_name
performance sql sql-server
performance sql sql-server
edited Aug 10 at 3:21
Stephen Rauch
3,76061530
asked Jul 27 at 3:14
VK_217
1063
edited Aug 10 at 3:21
Stephen Rauch
3,76061530
asked Jul 27 at 3:14
VK_217
1063
edited Aug 10 at 3:21
Stephen Rauch
3,76061530
edited Aug 10 at 3:21
Stephen Rauch
3,76061530
edited Aug 10 at 3:21
Stephen Rauch
3,76061530
3,76061530
asked Jul 27 at 3:14
VK_217
1063
asked Jul 27 at 3:14
VK_217
1063
asked Jul 27 at 3:14
VK_217
1063
1063
Please include your DB schema.
– Mast
Jul 27 at 7:44
1
Why do you update the age every month instead of calculating it on-the-fly either as calculated column or in a view?
– dnoeth
Aug 12 at 14:13
Why would you want to replace abirth_yearcolumn with the age, without renaming it?
– 200_success
Oct 9 at 5:18
add a comment |
Please include your DB schema.
– Mast
Jul 27 at 7:44
1
Why do you update the age every month instead of calculating it on-the-fly either as calculated column or in a view?
– dnoeth
Aug 12 at 14:13
Why would you want to replace abirth_yearcolumn with the age, without renaming it?
– 200_success
Oct 9 at 5:18
Please include your DB schema.
– Mast
Jul 27 at 7:44
Please include your DB schema.
– Mast
Jul 27 at 7:44
1
1
Why do you update the age every month instead of calculating it on-the-fly either as calculated column or in a view?
– dnoeth
Aug 12 at 14:13
Why do you update the age every month instead of calculating it on-the-fly either as calculated column or in a view?
– dnoeth
Aug 12 at 14:13
Why would you want to replace a
birth_year column with the age, without renaming it?– 200_success
Oct 9 at 5:18
Why would you want to replace a
birth_year column with the age, without renaming it?– 200_success
Oct 9 at 5:18
add a comment |
1 Answer
1
active
oldest
votes
up vote
0
down vote
As per my understanding, you are looking for current age for each records. But for that, you should go ahead with datedifference function by year. no need to calculate this much operation.
Make sure you should have index on below columns.
birth_month , birth_year
After all, please check and let us know the execution time readings.
answered Aug 10 at 3:15
JERRY
1305
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
return StackExchange.using("mathjaxEditing", function () {
StackExchange.MarkdownEditor.creationCallbacks.add(function (editor, postfix) {
StackExchange.mathjaxEditing.prepareWmdForMathJax(editor, postfix, [["\$", "\$"]]);
});
});
}, "mathjax-editing");
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: "196"
};
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',
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%2fcodereview.stackexchange.com%2fquestions%2f200390%2fworst-query-performance-issue-on-date-calculation%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
up vote
0
down vote
As per my understanding, you are looking for current age for each records. But for that, you should go ahead with datedifference function by year. no need to calculate this much operation.
Make sure you should have index on below columns.
birth_month , birth_year
After all, please check and let us know the execution time readings.
answered Aug 10 at 3:15
JERRY
1305
add a comment |
up vote
0
down vote
As per my understanding, you are looking for current age for each records. But for that, you should go ahead with datedifference function by year. no need to calculate this much operation.
Make sure you should have index on below columns.
birth_month , birth_year
After all, please check and let us know the execution time readings.
answered Aug 10 at 3:15
JERRY
1305
add a comment |
up vote
0
down vote
up vote
0
down vote
As per my understanding, you are looking for current age for each records. But for that, you should go ahead with datedifference function by year. no need to calculate this much operation.
Make sure you should have index on below columns.
birth_month , birth_year
After all, please check and let us know the execution time readings.
answered Aug 10 at 3:15
JERRY
1305
As per my understanding, you are looking for current age for each records. But for that, you should go ahead with datedifference function by year. no need to calculate this much operation.
Make sure you should have index on below columns.
birth_month , birth_year
After all, please check and let us know the execution time readings.
answered Aug 10 at 3:15
JERRY
1305
answered Aug 10 at 3:15
JERRY
1305
answered Aug 10 at 3:15
JERRY
1305
answered Aug 10 at 3:15
JERRY
1305
1305
add a comment |
add a comment |
Thanks for contributing an answer to Code Review 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.
Use MathJax to format equations. MathJax reference.
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.
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%2fcodereview.stackexchange.com%2fquestions%2f200390%2fworst-query-performance-issue-on-date-calculation%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
Please include your DB schema.
– Mast
Jul 27 at 7:44
1
Why do you update the age every month instead of calculating it on-the-fly either as calculated column or in a view?
– dnoeth
Aug 12 at 14:13
Why would you want to replace a
birth_yearcolumn with the age, without renaming it?– 200_success
Oct 9 at 5:18