February 16, 2011 at 10:51 am
1. If you have one stored procedure nested in another, can you have the child procedure insert rows into a temp table and then the parent proc select those rows?
2. Ive heard that if code inside a stored procedure is independent of other code, you can put them in seperate child procedures, so they can execute paralely and improve performance. Is this true? If so, can the same child procedure with different input parameters execute paralely with itself?
February 16, 2011 at 11:02 am
if the temp table is declared in the parent procedure, the temp table exists for any processes or child procedure all as well.
each instance of the parent proc would create it's own temp tables, unique to the call of the parent procedure.
here's an example:
/*--results
tmpiddescrip
1Oranges
2Tangerines
3bananas
4apples
5bananas
6apples
*/
CREATE PROCEDURE Child
AS
BEGIN
--note it's ASSUMING the table exists.
insert into #tmp(descrip)
SELECT 'bananas' UNION ALL
SELECT 'apples'
END --PROC
GO
CREATE PROCEDURE Parent
AS
BEGIN
CREATE TABLE #tmp(
tmpid int identity(1,1) not null primary key,
descrip varchar(30))
insert into #tmp(descrip)
SELECT 'Oranges' UNION ALL
SELECT 'Tangerines '
EXEC Child
EXEC Child
select * from #tmp
END --PROC
GO
EXEC Parent
Lowell
February 16, 2011 at 5:17 pm
Oh Thanks, awesome!
What about question 2 though? Will having Child procs for the code to run parallel to eachother improve performance?
And can 1 proc run parallel to itself with different parameters?
February 16, 2011 at 5:24 pm
Khades (2/16/2011)
Oh Thanks, awesome!What about question 2 though? Will having Child procs for the code to run parallel to each other improve performance?
And can 1 proc run parallel to itself with different parameters?
not sure exactly what you mean...probably the answer is "no", if I'm reading it right.
if your parent proc had two child proc calls, and say, child1 added rows to the temp table based on values 1 thru 50, and a second call did the same but for values 51 thru 100, the process would run sequentially...one after the other.
so no, the calls to child procs wait for the previous to finish.
SQL might evaluate a single operation(select for example) and decide to split it into a parralell operation, but you have no control over that.
Lowell
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply