Running Multiple instance of the same stored procedure

Question

Running Multiple instance of the same stored procedure

loki1049

SSCrazy

Points: 2065
More actions
June 14, 2010 at 12:55 pm

#97384

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??

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

WayneS SSC Guru Points: 95461 More actions · Answer 1

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
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

LutzM SSC Guru Points: 107049 More actions · Answer 2

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[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

loki1049 SSCrazy Points: 2065 More actions · Answer 3

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?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

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 SSCrazy Points: 2065 More actions · Answer 5

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 SSC Guru Points: 95461 More actions · Answer 6

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[/url].

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
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

WayneS SSC Guru Points: 95461 More actions · Answer 7

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
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

loki1049 SSCrazy Points: 2065 More actions · Answer 8

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 SSC Journeyman Points: 81 More actions · Answer 9

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!). 😀