SybaseBlog

I received a question on parallelism, and even after reviewing sections 24 and 25 of the Sybase Performance Tuning Guide, it still called for a test

http://sybooks.sybase.com/onlinebooks/group-as/asg1250e/ptallbk

I have a stored procedure that calls 20 others. I want to set the parallel degree to 1 for this stored procedure, and everything it calls. This works fine if I set it in isql before calling the stored procedure. However, normally this stored procedure is integrated into our application, so I set the parallel degree to 1 inside this stored procedure and called it normally. There was some question
whether that would affect only the main stored procedure and not propogate to the procedures that were called.

The answer is that yes, the parallel degree set by a main procedure does trickle to any other stored procedures that are called. I imagine that would be the same for all parameters. Have a look.

Here are the results of my test:

*** main procedure sp_testparadeg1 has parallel degree 0 ***
*** set parallel degree to 1 ***
*** After being set to 1, parallel degree is 1 ***
*** sub-procedure sp_testparadeg2 has parallel degree 1 ***
(return status = 0)
*** Deleting proc dbo.sp_testparadeg1 ***
*** Deleting proc dbo.sp_testparadeg2 ***

Here is the test:

dump tran sybsystemprocs with truncate_only
go

use sybsystemprocs
go

if exists (select * from sysobjects where name = "sp_testparadeg1" and type = 'P')
drop proc sp_testparadeg1
go
if exists (select * from sysobjects where name = "sp_testparadeg2" and type = 'P')
drop proc sp_testparadeg2
go

-- create test procedures
create procedure sp_testparadeg2
as
print '*** sub-procedure sp_testparadeg2 has parallel degree %1! ***', @@parallel_degree
go

create procedure sp_testparadeg1
as
print '*** main procedure sp_testparadeg1 has parallel degree %1! ***', @@parallel_degree

print '*** set parallel degree to 1 ***'
set parallel_degree 1

print '*** After being set to 1, parallel degree is %1! ***', @@parallel_degree

exec sp_testparadeg2
go

if object_id('sp_testparadeg1') is not null
begin
print '*** Created procedure dbo.sp_testparadeg1 ***'
grant execute on dbo.sp_testparadeg1 to public
end
else
begin
print '*** Failed creating proc dbo.sp_testparadeg1 ***'
end
go

if object_id('sp_testparadeg2') is not null
begin
print '*** Created procedure dbo.sp_testparadeg2 ***'
grant execute on dbo.sp_testparadeg2 to public
end
else
begin
print '*** Failed creating proc dbo.sp_testparadeg2 ***'
end
go

-- do your tests
sp_configure 'max parallel degree'
go
exec sp_testparadeg1
go

-- clean up
print '*** Deleting proc dbo.sp_testparadeg1 ***'
if exists (select * from sysobjects where name = "sp_testparadeg1" and type = 'P')
drop proc sp_testparadeg1
go
print '*** Deleting proc dbo.sp_testparadeg2 ***'
if exists (select * from sysobjects where name = "sp_testparadeg2" and type = 'P')
drop proc sp_testparadeg2
go