Mixing
Procedure time executing counter
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ height:90px;width:728px;box-sizing:border-box;
}
I have got a procedure which inserts data from one table to other and one time it takes from example 5 minutes and next time for example 15 minutes.
I want to write code that create a log in my log table when procedure will take more then 10 minutes. Is exists any function or time counter in ms sql that I can use?
sql
sql-server asked Jan 3 at 8:16
adamek339adamek339
366
add a comment |
I have got a procedure which inserts data from one table to other and one time it takes from example 5 minutes and next time for example 15 minutes.
I want to write code that create a log in my log table when procedure will take more then 10 minutes. Is exists any function or time counter in ms sql that I can use?
sql
sql-server asked Jan 3 at 8:16
adamek339adamek339
366
1
Store the current time at the start of the SP and check at the end of the SP if the difference against the current time is higher than the amount you want to check, and log it if so. Can do inside or outside of SP, as you want.
– EzLo
Jan 3 at 8:19
add a comment |
I have got a procedure which inserts data from one table to other and one time it takes from example 5 minutes and next time for example 15 minutes.
I want to write code that create a log in my log table when procedure will take more then 10 minutes. Is exists any function or time counter in ms sql that I can use?
sql
sql-server asked Jan 3 at 8:16
adamek339adamek339
366
I have got a procedure which inserts data from one table to other and one time it takes from example 5 minutes and next time for example 15 minutes.
I want to write code that create a log in my log table when procedure will take more then 10 minutes. Is exists any function or time counter in ms sql that I can use?
sql
sql-server sql
sql-server asked Jan 3 at 8:16
adamek339adamek339
366
asked Jan 3 at 8:16
adamek339adamek339
366
asked Jan 3 at 8:16
adamek339adamek339
366
asked Jan 3 at 8:16
adamek339adamek339
366
asked Jan 3 at 8:16
adamek339adamek339
366
366
1
Store the current time at the start of the SP and check at the end of the SP if the difference against the current time is higher than the amount you want to check, and log it if so. Can do inside or outside of SP, as you want.
– EzLo
Jan 3 at 8:19
add a comment |
1
Store the current time at the start of the SP and check at the end of the SP if the difference against the current time is higher than the amount you want to check, and log it if so. Can do inside or outside of SP, as you want.
– EzLo
Jan 3 at 8:19
1
1
Store the current time at the start of the SP and check at the end of the SP if the difference against the current time is higher than the amount you want to check, and log it if so. Can do inside or outside of SP, as you want.
– EzLo
Jan 3 at 8:19
Store the current time at the start of the SP and check at the end of the SP if the difference against the current time is higher than the amount you want to check, and log it if so. Can do inside or outside of SP, as you want.
– EzLo
Jan 3 at 8:19
add a comment |
3 Answers
3
active
oldest
votes
Add the following lines into your SP and it should work:
ALTER PROCEDURE YourSP
AS
BEGIN
DECLARE @StartTime AS DATETIME = GETDATE();
... <Your current lines>
IF DATEDIFF(mi, @StartTime, GETDATE()) > 10
INSERT INTO LogTable <YourFields>, MinutesSpent
VALUES <YourValues>, DATEDIFF(mi, @StartTime, GETDATE())
END
answered Jan 3 at 8:54
Angel M.Angel M.
1,312415
1
It seems a suitable solution. Upvoted!
– Brijesh Kumar Tripathi
Jan 3 at 9:01
add a comment |
This is too long for a comment.
Why would you only log particular calls to the stored procedure? You should log all calls and filter out the ones that you want. This week you might be interesting in timings longer than 10 minutes. Next week, the data might grow and it might be 12 minutes.
Or you might change the code to make it more efficient, and it should finish in 2 minutes.
If you are only interested in timing, I would write a rather generic log table, something like this:
create table spTimeLog (
procedureName varchar(255),
startDateTime datetime
endDateTime datetime,
createdAt datetime default getdate()
);
create procedure usp_proc . . .
begin
declare @StartTime datetime = getdate();
. . .
insert into spTimeLog (procedureName, startDateTime, endDateTime)
values ('usp_proc', StartTime, getdate());
end;
Then you can get the information you want when you query the table:
select count(*)
from spTimeLog tl
where tl.procedureName = 'usp_proc' and
endDateTime > dateadd(minute, 10, startDateTime);
In general, when I write stored procedures for a real application, the stored procedures generate audit logs when they enter and exit -- both successfully and when they fail.
answered Jan 3 at 11:55
Gordon LinoffGordon Linoff
795k37318423
add a comment |
You can try this way
declare @start datetime = getdate()
-- your SQL statements
exec dbo.MyStoredProcedure
declare @executionTimeInMilliseconds int = datediff(ms, @start, getdate())
answered Jan 3 at 8:33
Suraj KumarSuraj Kumar
2,79141026
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%2f54018561%2fprocedure-time-executing-counter%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
3 Answers
3
active
oldest
votes
3 Answers
3
active
oldest
votes
active
oldest
votes
active
oldest
votes
Add the following lines into your SP and it should work:
ALTER PROCEDURE YourSP
AS
BEGIN
DECLARE @StartTime AS DATETIME = GETDATE();
... <Your current lines>
IF DATEDIFF(mi, @StartTime, GETDATE()) > 10
INSERT INTO LogTable <YourFields>, MinutesSpent
VALUES <YourValues>, DATEDIFF(mi, @StartTime, GETDATE())
END
answered Jan 3 at 8:54
Angel M.Angel M.
1,312415
1
It seems a suitable solution. Upvoted!
– Brijesh Kumar Tripathi
Jan 3 at 9:01
add a comment |
Add the following lines into your SP and it should work:
ALTER PROCEDURE YourSP
AS
BEGIN
DECLARE @StartTime AS DATETIME = GETDATE();
... <Your current lines>
IF DATEDIFF(mi, @StartTime, GETDATE()) > 10
INSERT INTO LogTable <YourFields>, MinutesSpent
VALUES <YourValues>, DATEDIFF(mi, @StartTime, GETDATE())
END
answered Jan 3 at 8:54
Angel M.Angel M.
1,312415
1
It seems a suitable solution. Upvoted!
– Brijesh Kumar Tripathi
Jan 3 at 9:01
add a comment |
Add the following lines into your SP and it should work:
ALTER PROCEDURE YourSP
AS
BEGIN
DECLARE @StartTime AS DATETIME = GETDATE();
... <Your current lines>
IF DATEDIFF(mi, @StartTime, GETDATE()) > 10
INSERT INTO LogTable <YourFields>, MinutesSpent
VALUES <YourValues>, DATEDIFF(mi, @StartTime, GETDATE())
END
answered Jan 3 at 8:54
Angel M.Angel M.
1,312415
Add the following lines into your SP and it should work:
ALTER PROCEDURE YourSP
AS
BEGIN
DECLARE @StartTime AS DATETIME = GETDATE();
... <Your current lines>
IF DATEDIFF(mi, @StartTime, GETDATE()) > 10
INSERT INTO LogTable <YourFields>, MinutesSpent
VALUES <YourValues>, DATEDIFF(mi, @StartTime, GETDATE())
END
answered Jan 3 at 8:54
Angel M.Angel M.
1,312415
answered Jan 3 at 8:54
Angel M.Angel M.
1,312415
answered Jan 3 at 8:54
Angel M.Angel M.
1,312415
answered Jan 3 at 8:54
Angel M.Angel M.
1,312415
1,312415
1
It seems a suitable solution. Upvoted!
– Brijesh Kumar Tripathi
Jan 3 at 9:01
add a comment |
1
It seems a suitable solution. Upvoted!
– Brijesh Kumar Tripathi
Jan 3 at 9:01
1
1
It seems a suitable solution. Upvoted!
– Brijesh Kumar Tripathi
Jan 3 at 9:01
It seems a suitable solution. Upvoted!
– Brijesh Kumar Tripathi
Jan 3 at 9:01
add a comment |
This is too long for a comment.
Why would you only log particular calls to the stored procedure? You should log all calls and filter out the ones that you want. This week you might be interesting in timings longer than 10 minutes. Next week, the data might grow and it might be 12 minutes.
Or you might change the code to make it more efficient, and it should finish in 2 minutes.
If you are only interested in timing, I would write a rather generic log table, something like this:
create table spTimeLog (
procedureName varchar(255),
startDateTime datetime
endDateTime datetime,
createdAt datetime default getdate()
);
create procedure usp_proc . . .
begin
declare @StartTime datetime = getdate();
. . .
insert into spTimeLog (procedureName, startDateTime, endDateTime)
values ('usp_proc', StartTime, getdate());
end;
Then you can get the information you want when you query the table:
select count(*)
from spTimeLog tl
where tl.procedureName = 'usp_proc' and
endDateTime > dateadd(minute, 10, startDateTime);
In general, when I write stored procedures for a real application, the stored procedures generate audit logs when they enter and exit -- both successfully and when they fail.
answered Jan 3 at 11:55
Gordon LinoffGordon Linoff
795k37318423
add a comment |
This is too long for a comment.
Why would you only log particular calls to the stored procedure? You should log all calls and filter out the ones that you want. This week you might be interesting in timings longer than 10 minutes. Next week, the data might grow and it might be 12 minutes.
Or you might change the code to make it more efficient, and it should finish in 2 minutes.
If you are only interested in timing, I would write a rather generic log table, something like this:
create table spTimeLog (
procedureName varchar(255),
startDateTime datetime
endDateTime datetime,
createdAt datetime default getdate()
);
create procedure usp_proc . . .
begin
declare @StartTime datetime = getdate();
. . .
insert into spTimeLog (procedureName, startDateTime, endDateTime)
values ('usp_proc', StartTime, getdate());
end;
Then you can get the information you want when you query the table:
select count(*)
from spTimeLog tl
where tl.procedureName = 'usp_proc' and
endDateTime > dateadd(minute, 10, startDateTime);
In general, when I write stored procedures for a real application, the stored procedures generate audit logs when they enter and exit -- both successfully and when they fail.
answered Jan 3 at 11:55
Gordon LinoffGordon Linoff
795k37318423
add a comment |
This is too long for a comment.
Why would you only log particular calls to the stored procedure? You should log all calls and filter out the ones that you want. This week you might be interesting in timings longer than 10 minutes. Next week, the data might grow and it might be 12 minutes.
Or you might change the code to make it more efficient, and it should finish in 2 minutes.
If you are only interested in timing, I would write a rather generic log table, something like this:
create table spTimeLog (
procedureName varchar(255),
startDateTime datetime
endDateTime datetime,
createdAt datetime default getdate()
);
create procedure usp_proc . . .
begin
declare @StartTime datetime = getdate();
. . .
insert into spTimeLog (procedureName, startDateTime, endDateTime)
values ('usp_proc', StartTime, getdate());
end;
Then you can get the information you want when you query the table:
select count(*)
from spTimeLog tl
where tl.procedureName = 'usp_proc' and
endDateTime > dateadd(minute, 10, startDateTime);
In general, when I write stored procedures for a real application, the stored procedures generate audit logs when they enter and exit -- both successfully and when they fail.
answered Jan 3 at 11:55
Gordon LinoffGordon Linoff
795k37318423
This is too long for a comment.
Why would you only log particular calls to the stored procedure? You should log all calls and filter out the ones that you want. This week you might be interesting in timings longer than 10 minutes. Next week, the data might grow and it might be 12 minutes.
Or you might change the code to make it more efficient, and it should finish in 2 minutes.
If you are only interested in timing, I would write a rather generic log table, something like this:
create table spTimeLog (
procedureName varchar(255),
startDateTime datetime
endDateTime datetime,
createdAt datetime default getdate()
);
create procedure usp_proc . . .
begin
declare @StartTime datetime = getdate();
. . .
insert into spTimeLog (procedureName, startDateTime, endDateTime)
values ('usp_proc', StartTime, getdate());
end;
Then you can get the information you want when you query the table:
select count(*)
from spTimeLog tl
where tl.procedureName = 'usp_proc' and
endDateTime > dateadd(minute, 10, startDateTime);
In general, when I write stored procedures for a real application, the stored procedures generate audit logs when they enter and exit -- both successfully and when they fail.
answered Jan 3 at 11:55
Gordon LinoffGordon Linoff
795k37318423
answered Jan 3 at 11:55
Gordon LinoffGordon Linoff
795k37318423
answered Jan 3 at 11:55
Gordon LinoffGordon Linoff
795k37318423
answered Jan 3 at 11:55
Gordon LinoffGordon Linoff
795k37318423
795k37318423
add a comment |
add a comment |
You can try this way
declare @start datetime = getdate()
-- your SQL statements
exec dbo.MyStoredProcedure
declare @executionTimeInMilliseconds int = datediff(ms, @start, getdate())
answered Jan 3 at 8:33
Suraj KumarSuraj Kumar
2,79141026
add a comment |
You can try this way
declare @start datetime = getdate()
-- your SQL statements
exec dbo.MyStoredProcedure
declare @executionTimeInMilliseconds int = datediff(ms, @start, getdate())
answered Jan 3 at 8:33
Suraj KumarSuraj Kumar
2,79141026
add a comment |
You can try this way
declare @start datetime = getdate()
-- your SQL statements
exec dbo.MyStoredProcedure
declare @executionTimeInMilliseconds int = datediff(ms, @start, getdate())
answered Jan 3 at 8:33
Suraj KumarSuraj Kumar
2,79141026
You can try this way
declare @start datetime = getdate()
-- your SQL statements
exec dbo.MyStoredProcedure
declare @executionTimeInMilliseconds int = datediff(ms, @start, getdate())
answered Jan 3 at 8:33
Suraj KumarSuraj Kumar
2,79141026
answered Jan 3 at 8:33
Suraj KumarSuraj Kumar
2,79141026
answered Jan 3 at 8:33
Suraj KumarSuraj Kumar
2,79141026
answered Jan 3 at 8:33
Suraj KumarSuraj Kumar
2,79141026
2,79141026
add a comment |
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%2f54018561%2fprocedure-time-executing-counter%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
Store the current time at the start of the SP and check at the end of the SP if the difference against the current time is higher than the amount you want to check, and log it if so. Can do inside or outside of SP, as you want.
– EzLo
Jan 3 at 8:19