May 11, 2012 at 6:48 am
vinu512 (5/11/2012)
Lynn Pettis (5/11/2012)
Only suggestion I can give you:http://msdn.microsoft.com/en-us/library/ms131686(v=sql.100).aspx
Thanks for the link Lynn.
I'll see what can be done and get back to you.
Unless you plan on posting code, not sure what more anyone here can do.
May 11, 2012 at 10:59 am
If you're dealing with messaging and responses - you're really in the realm of talking about a SQL Service Broker application with a conversation set up. That said - we're talking about a bunch of things to set up, with multiple steps set up as separate procedures which get called during the interaction.
Native SQL wouldn't consider this long-running process a single procedure (since it will kill the locks open the entire time, etc...) In this case - while they might sound similar, process is NOT procedure.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
May 11, 2012 at 11:32 am
Matt Miller (#4) (5/11/2012)
If you're dealing with messaging and responses - you're really in the realm of talking about a SQL Service Broker application with a conversation set up. That said - we're talking about a bunch of things to set up, with multiple steps set up as separate procedures which get called during the interaction.Native SQL wouldn't consider this long-running process a single procedure (since it will kill the locks open the entire time, etc...) In this case - while they might sound similar, process is NOT procedure.
I thought about mentioning Service Broker, but since I couldn't get a straight answer as to what they were doing, I avoided it.
May 11, 2012 at 11:44 am
Lynn Pettis (5/11/2012)
Matt Miller (#4) (5/11/2012)
If you're dealing with messaging and responses - you're really in the realm of talking about a SQL Service Broker application with a conversation set up. That said - we're talking about a bunch of things to set up, with multiple steps set up as separate procedures which get called during the interaction.Native SQL wouldn't consider this long-running process a single procedure (since it will kill the locks open the entire time, etc...) In this case - while they might sound similar, process is NOT procedure.
I thought about mentioning Service Broker, but since I couldn't get a straight answer as to what they were doing, I avoided it.
And until we hear anything more, I wouldn't go any further than simply mentioning it:)
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
May 11, 2012 at 11:50 am
Matt Miller (#4) (5/11/2012)
Lynn Pettis (5/11/2012)
Matt Miller (#4) (5/11/2012)
If you're dealing with messaging and responses - you're really in the realm of talking about a SQL Service Broker application with a conversation set up. That said - we're talking about a bunch of things to set up, with multiple steps set up as separate procedures which get called during the interaction.Native SQL wouldn't consider this long-running process a single procedure (since it will kill the locks open the entire time, etc...) In this case - while they might sound similar, process is NOT procedure.
I thought about mentioning Service Broker, but since I couldn't get a straight answer as to what they were doing, I avoided it.
And until we hear anything more, I wouldn't go any further than simply mentioning it:)
My guess is they are looking for a way to use a status bar that will be updated periodically throughout a long process. That then begs the question, why is a stored proc taking so long that a status bar is needed to make the user experience better? It seems from the description that instead of making the process slower there should be some effort to make the process faster so this type of thing is no longer needed.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 11, 2012 at 11:52 am
Sean Lange (5/11/2012)
Matt Miller (#4) (5/11/2012)
Lynn Pettis (5/11/2012)
Matt Miller (#4) (5/11/2012)
If you're dealing with messaging and responses - you're really in the realm of talking about a SQL Service Broker application with a conversation set up. That said - we're talking about a bunch of things to set up, with multiple steps set up as separate procedures which get called during the interaction.Native SQL wouldn't consider this long-running process a single procedure (since it will kill the locks open the entire time, etc...) In this case - while they might sound similar, process is NOT procedure.
I thought about mentioning Service Broker, but since I couldn't get a straight answer as to what they were doing, I avoided it.
And until we hear anything more, I wouldn't go any further than simply mentioning it:)
My guess is they are looking for a way to use a status bar that will be updated periodically throughout a long process. That then begs the question, why is a stored proc taking so long that a status bar is needed to make the user experience better? It seems from the description that instead of making the process slower there should be some effort to make the process faster so this type of thing is no longer needed.
That's what I tried telling him, but apparently the .NET programmers are more knowledgeable than us.
May 11, 2012 at 12:16 pm
Lynn Pettis (5/11/2012)
Sean Lange (5/11/2012)
Matt Miller (#4) (5/11/2012)
Lynn Pettis (5/11/2012)
Matt Miller (#4) (5/11/2012)
If you're dealing with messaging and responses - you're really in the realm of talking about a SQL Service Broker application with a conversation set up. That said - we're talking about a bunch of things to set up, with multiple steps set up as separate procedures which get called during the interaction.Native SQL wouldn't consider this long-running process a single procedure (since it will kill the locks open the entire time, etc...) In this case - while they might sound similar, process is NOT procedure.
I thought about mentioning Service Broker, but since I couldn't get a straight answer as to what they were doing, I avoided it.
And until we hear anything more, I wouldn't go any further than simply mentioning it:)
My guess is they are looking for a way to use a status bar that will be updated periodically throughout a long process. That then begs the question, why is a stored proc taking so long that a status bar is needed to make the user experience better? It seems from the description that instead of making the process slower there should be some effort to make the process faster so this type of thing is no longer needed.
That's what I tried telling him, but apparently the .NET programmers are more knowledgeable than us.
Hey guys, there is a really slow .NET procedure. Can you please slow it down further so we can provide some visual feedback to the users?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 11, 2012 at 1:21 pm
This is reminding me of a script I saw from a colleague to call a slow stored procedure several times over with different parameters; so instead of optimising the procedure he included a wait of 30 seconds between each call to "give other users time to run their programs".
Follow me on twitter @EvoDBACheck out my blog Natural Selection DBA[/url]
May 11, 2012 at 1:31 pm
Yeah this certainly does have the feeling of fixing the symptom instead of the problem.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 11, 2012 at 11:43 pm
Matthew Darwin (5/11/2012)
This is reminding me of a script I saw from a colleague to call a slow stored procedure several times over with different parameters; so instead of optimising the procedure he included a wait of 30 seconds between each call to "give other users time to run their programs".
Yes Mathew. That is what they are trying to do.
The web app runs on a 3rd party server. There is a pre decided session time. If any processing takes more time than that to complete then the session is timed out.
So, instead of fixing some rubbish procedure they wrote, they want the database people in our company to give them a mechanism that would run keep executing the sproc in the background and keep producing some responses to the Client Application to keep the Session from timing out.
I completely agree with all you guys that the solution here is to fix the procedure. But, I can't help it....you know..how...some bosses think they are always right....I've got one who thinks that way. And right now he thinks that we should not fix the procedure and we should do it the way he says. That is why I am stuck with finding a workaround to it.
Thank you for all your time and all your valuable suggestions.
May 12, 2012 at 4:23 am
Divide and Conquer.
Split the SP’s logic in few steps, create new SPs for each step and call each SP from application. Each SP can provide its own status (success / failure).
May 12, 2012 at 4:50 am
Dev (5/12/2012)
Divide and Conquer.Split the SP’s logic in few steps, create new SPs for each step and call each SP from application. Each SP can provide its own status (success / failure).
yea thats the option I'm wrkin with now.....haven't had much luck with anything else yet...so was breaking it up.
May 12, 2012 at 5:12 am
That’s good. Also, collect the performance statistics of stored procedure executions and share it with .Net team. If it’s still not convincing enough, please share this discussion (SSC) with them and let them read SQL expert’s opinion on their weird requirement.
Viewing 13 posts - 16 through 27 (of 27 total)
You must be logged in to reply to this topic. Login to reply