Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Running Multiple instance of the same stored procedure Expand / Collapse
Author
Message
Posted Monday, June 14, 2010 12:54 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, May 10, 2012 12:19 PM
Points: 103, 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??
Post #937071
Posted Monday, June 14, 2010 1:30 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, October 17, 2014 7:46 AM
Points: 5,364, Visits: 8,948
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
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
Post #937090
Posted Monday, June 14, 2010 1:43 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 4:31 PM
Points: 6,841, Visits: 13,358
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
Post #937097
Posted Monday, June 14, 2010 1:52 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, May 10, 2012 12:19 PM
Points: 103, 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?

Post #937102
Posted Monday, June 14, 2010 1:54 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 4:08 PM
Points: 40,177, Visits: 36,580
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 2008, MVP
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

Post #937103
Posted Monday, June 14, 2010 1:54 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, May 10, 2012 12:19 PM
Points: 103, 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.
Post #937104
Posted Monday, June 14, 2010 2:00 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, October 17, 2014 7:46 AM
Points: 5,364, Visits: 8,948
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
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
Post #937111
Posted Monday, June 14, 2010 2:02 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, October 17, 2014 7:46 AM
Points: 5,364, Visits: 8,948
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
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
Post #937114
Posted Monday, June 14, 2010 3:58 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, May 10, 2012 12:19 PM
Points: 103, 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.
Post #937169
Posted Monday, August 23, 2010 6:46 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, July 18, 2012 12:50 PM
Points: 1, Visits: 11
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!).
Post #973799
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse