2 Separate Tasks - Related but first needs to finish completely
Right now I have an app that will be inserting thousands of rows with a click of a button. They populate a table within the database (or many tables but lets keep it simple) and anything that is inserted or updated needs to be calculated/recalculated using a stored procedure. Initially I had a trigger there that looked for anything modified and passed the parameters to the stored procedure but it's taking too long. Then I had the thought to just insert the parameter values into a "queue" table and have the stored proc read from there. I placed a trigger on this queue table initially thinking once an insert happens, we can run the proc and then have the record removed from the queue table... but a trigger that inserts into another table with a trigger is still linked and is taking a while for the process to finish. I want to keep these tasks related but separate. I want the first trigger to run through, dump all the values into the queue table and completely finish before the SP is kicked off. Is my best bet to have the values in this queue table and set up a job that runs every 10 seconds to see if anything is in there or is there another approach you can recommend?
Thanks!
sql
sql-server stored-procedures triggers asked Nov 20 at 20:36
JMG
93
add a comment |
Right now I have an app that will be inserting thousands of rows with a click of a button. They populate a table within the database (or many tables but lets keep it simple) and anything that is inserted or updated needs to be calculated/recalculated using a stored procedure. Initially I had a trigger there that looked for anything modified and passed the parameters to the stored procedure but it's taking too long. Then I had the thought to just insert the parameter values into a "queue" table and have the stored proc read from there. I placed a trigger on this queue table initially thinking once an insert happens, we can run the proc and then have the record removed from the queue table... but a trigger that inserts into another table with a trigger is still linked and is taking a while for the process to finish. I want to keep these tasks related but separate. I want the first trigger to run through, dump all the values into the queue table and completely finish before the SP is kicked off. Is my best bet to have the values in this queue table and set up a job that runs every 10 seconds to see if anything is in there or is there another approach you can recommend?
Thanks!
sql
sql-server stored-procedures triggers asked Nov 20 at 20:36
JMG
93
add a comment |
Right now I have an app that will be inserting thousands of rows with a click of a button. They populate a table within the database (or many tables but lets keep it simple) and anything that is inserted or updated needs to be calculated/recalculated using a stored procedure. Initially I had a trigger there that looked for anything modified and passed the parameters to the stored procedure but it's taking too long. Then I had the thought to just insert the parameter values into a "queue" table and have the stored proc read from there. I placed a trigger on this queue table initially thinking once an insert happens, we can run the proc and then have the record removed from the queue table... but a trigger that inserts into another table with a trigger is still linked and is taking a while for the process to finish. I want to keep these tasks related but separate. I want the first trigger to run through, dump all the values into the queue table and completely finish before the SP is kicked off. Is my best bet to have the values in this queue table and set up a job that runs every 10 seconds to see if anything is in there or is there another approach you can recommend?
Thanks!
sql
sql-server stored-procedures triggers asked Nov 20 at 20:36
JMG
93
Right now I have an app that will be inserting thousands of rows with a click of a button. They populate a table within the database (or many tables but lets keep it simple) and anything that is inserted or updated needs to be calculated/recalculated using a stored procedure. Initially I had a trigger there that looked for anything modified and passed the parameters to the stored procedure but it's taking too long. Then I had the thought to just insert the parameter values into a "queue" table and have the stored proc read from there. I placed a trigger on this queue table initially thinking once an insert happens, we can run the proc and then have the record removed from the queue table... but a trigger that inserts into another table with a trigger is still linked and is taking a while for the process to finish. I want to keep these tasks related but separate. I want the first trigger to run through, dump all the values into the queue table and completely finish before the SP is kicked off. Is my best bet to have the values in this queue table and set up a job that runs every 10 seconds to see if anything is in there or is there another approach you can recommend?
Thanks!
sql
sql-server stored-procedures triggers sql
sql-server stored-procedures triggers asked Nov 20 at 20:36
JMG
93
asked Nov 20 at 20:36
JMG
93
asked Nov 20 at 20:36
JMG
93
asked Nov 20 at 20:36
JMG
93
asked Nov 20 at 20:36
JMG
93
93
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
I think what I'm going to do after some thinking is have the trigger on the queue table still but instead of it triggering the stored proc, it will kick off a job. That way it can go quickly through instead of waiting for the process. The job itself will have a stored proc that looks back at the queue table and performs the necessary steps before removing the record.
answered Nov 20 at 21:12
JMG
93
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%2f53401120%2f2-separate-tasks-related-but-first-needs-to-finish-completely%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
I think what I'm going to do after some thinking is have the trigger on the queue table still but instead of it triggering the stored proc, it will kick off a job. That way it can go quickly through instead of waiting for the process. The job itself will have a stored proc that looks back at the queue table and performs the necessary steps before removing the record.
answered Nov 20 at 21:12
JMG
93
add a comment |
I think what I'm going to do after some thinking is have the trigger on the queue table still but instead of it triggering the stored proc, it will kick off a job. That way it can go quickly through instead of waiting for the process. The job itself will have a stored proc that looks back at the queue table and performs the necessary steps before removing the record.
answered Nov 20 at 21:12
JMG
93
add a comment |
I think what I'm going to do after some thinking is have the trigger on the queue table still but instead of it triggering the stored proc, it will kick off a job. That way it can go quickly through instead of waiting for the process. The job itself will have a stored proc that looks back at the queue table and performs the necessary steps before removing the record.
answered Nov 20 at 21:12
JMG
93
I think what I'm going to do after some thinking is have the trigger on the queue table still but instead of it triggering the stored proc, it will kick off a job. That way it can go quickly through instead of waiting for the process. The job itself will have a stored proc that looks back at the queue table and performs the necessary steps before removing the record.
answered Nov 20 at 21:12
JMG
93
answered Nov 20 at 21:12
JMG
93
answered Nov 20 at 21:12
JMG
93
answered Nov 20 at 21:12
JMG
93
93
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.
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%2fstackoverflow.com%2fquestions%2f53401120%2f2-separate-tasks-related-but-first-needs-to-finish-completely%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
