Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Running Multiple instance of the same stored procedure


Running Multiple instance of the same stored procedure

Author
Message
loki1049
loki1049
SSC-Enthusiastic
SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)

Group: General Forum Members
Points: 111 Visits: 301
Hi all, I was wondering if you could help me clear something up. I have a stored procedure called "SP_FillTable" that first clears a table "AreaReadings" then looks through the database and fills that table with up to date data. Another stored procedure called "SP_CalculateResults" invokes the "SP_FillTable" procedure and uses values from the newly populated table to display results to the user. (Note: the parent procedure is called from a web application) The whole process takes about 3min to execute.

I am worried that if more than one instance of the "SP_CalculateResults" stored procedure is run at the same time, I will get missing data or even non at all, because in one instance SP could be reading from the table while another SP begins and truncates that table. Is there any way to keep this from happening? Should I use a temp table first and some how pass that along inside the stored procedures? If so, how do you get a stored procedure to load its results into a temp table and then pass that table as a return variable??
WayneS
WayneS
SSCertifiable
SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)

Group: General Forum Members
Points: 6234 Visits: 10401
Once you create a temp table, it is available to any code further down that procedure, and to any procedures that the initial procedure may call after the table has been created. So, this is the design you would be shooting for.

CREATE PROCEDURE SP_FillTable As
IF OBJECT_ID('tempdb..#AreaReadings') IS NULL CREATE TABLE #AreaReadings(col1 int)
INSERT INTO #AreaReadings
select ...
GO

CREATE PROCEDURE sp_CalculateResults AS
CREATE TABLE #AreaReadings (col1 int)
execute sp_FillTable
select * from #AreaReadings
GO



I'd be more worried about that
The whole process takes about 3min to execute.
part.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links: For better assistance in answering your questions, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings

LutzM
LutzM
SSCertifiable
SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)

Group: General Forum Members
Points: 7001 Visits: 13559
I'd also recommend you change the structure of your procedure names:
it's considered as bad practice to start your stored procedures with sp_ since SQL server will try to find those sp's in master DB first (unless the proc is fully qualified or in a schema other than dbo).
This may cause unintended results. If you ever decide to name a proc "sp_addtype" that is used to add a specific type of area you'll see what I mean... ;-)



Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
loki1049
loki1049
SSC-Enthusiastic
SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)

Group: General Forum Members
Points: 111 Visits: 301
The whole process is a lot more complicated then I have explained. 3 mins seems quite reasonable for what it actually does in the end. I do have a question about the code you supplied. Specifically this part:

IF OBJECT_ID('tempdb..#AreaReadings') IS NULL CREATE TABLE #AreaReadings(col1 int)



So back to the main question, what happens if the temp table #AreaReadings is being used by one instance of the stored procedure while another instance tries to start and create it again, does it just give an error?
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47239 Visits: 44377
Temp tables are specific to a connection. One session cannot affect a temp table created by another session.


Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


loki1049
loki1049
SSC-Enthusiastic
SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)

Group: General Forum Members
Points: 111 Visits: 301
lmu92 (6/14/2010)
I'd also recommend you change the structure of your procedure names:
it's considered as bad practice to start your stored procedures with sp_ since SQL server will try to find those sp's in master DB first (unless the proc is fully qualified or in a schema other than dbo).
This may cause unintended results. If you ever decide to name a proc "sp_addtype" that is used to add a specific type of area you'll see what I mean... ;-)


Thanks, I'm aware though. This is just for demostrative purposes.

I just want to know about using a real table or passing a temp table. And issues I'll run into when more than one instance of this SP is called.
WayneS
WayneS
SSCertifiable
SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)

Group: General Forum Members
Points: 6234 Visits: 10401
loki1049 (6/14/2010)
The whole process is a lot more complicated then I have explained. 3 mins seems quite reasonable for what it actually does in the end.

Does any of that code contain the word "WHILE"? Is so, I bet there are several people out here that can make it appreciable faster!

I do have a question about the code you supplied. Specifically this part:

IF OBJECT_ID('tempdb..#AreaReadings') IS NULL CREATE TABLE #AreaReadings(col1 int)



So back to the main question, what happens if the temp table #AreaReadings is being used by one instance of the stored procedure while another instance tries to start and create it again, does it just give an error?


See what Gail said. Also, take a look at this article.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links: For better assistance in answering your questions, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings

WayneS
WayneS
SSCertifiable
SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)

Group: General Forum Members
Points: 6234 Visits: 10401
GilaMonster (6/14/2010)
Temp tables are specific to a connection. One session cannot affect a local temp table created by another session.

See my clarification in bold. Sessions CAN affect GLOBAL temp tables created by another session.

@Gail: I know you know this... but I'm not sure about the OP, and I wanted to ensure this ambiguity wasn't left for any others that might follow.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links: For better assistance in answering your questions, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings

loki1049
loki1049
SSC-Enthusiastic
SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)

Group: General Forum Members
Points: 111 Visits: 301
Does any of that code contain the word "WHILE"? Is so, I bet there are several people out here that can make it appreciable faster


The code includes 1 while statement which is used to loop through the "SP_FillTable" for each year. Run alone, it takes about a minute to perform this task for each year. Therefore the 'slow' part is in this stored procedure which happens to cross join several large temp tables and derived tables. The task I am doing works on million-row tables and given the scenario is fast enough. I've tried to get people to look at it, heck people have even asked for the query, but its almost useless without understanding the rather complicated scope of the goal in mind. It used to take nearly 4hours to run, and I have got it down to 2-3min.

Anyhow, thanks for the help so far. I talked to the boss and apparently he wants me to keep the table along and update it as I go, not clear it and use it for storage. This brings up two questions.

1.) So now I suppose I have to find a way to check the table for records and update accordingly or insert if the daily records don't exist. Is there an easy way to do this, I once saw something having to do with UPDATE SET .. If @@rowcount=0 INSERT.

2.) Is there a way for SQL server to isolate processes on a table when one stored procedure is updating and another is reading.

My main concern through this all is how concurrent reads and updates are handled. Or are we just to assume every process is super fast and never has any overlap to worry about, but seriously I don't think that is a good assumption.
Randy Gzz
Randy Gzz
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 12
I feel sorry for you dude! While I don't personally have an answer for your question (I was searching for an answer to a similar question), as is typical with some IT folks....I see everybody want's to answer questions that you didn't ask (or want answers to!). :-D
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search