Stored Procedure Issue - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Other SQL Server 2008 Topics

Stored Procedure Issue

Author

Topic

megha123
Starting Member

4 Posts

Posted - 2011-03-02 : 16:32:53

Hello,

I have 2 procedures (same code just different names) running on same database(same server)

Strange thing is happening, one procedure takes 2 minutes to execute and another takes just 1 Sec to execute.

How is this even possible.

Can anyone please help.

Thanks

X002548
Not Just a Number

15586 Posts

Posted - 2011-03-02 : 16:36:54

How do you know they are the same?

Post the Code

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

megha123
Starting Member

4 Posts

Posted - 2011-03-02 : 16:41:10

Hello,

I created the first procedure. Click to modify and changed just changed the name and created the same procedure with different name.

Here is the code:

ALTER PROCEDURE [dbo].[BPOL_Grant_Permissions_new]
@user_id int,
@doc_id int,
@p_mask int,
@created_by_id int
AS
BEGIN

SET NOCOUNT ON;
DECLARE @category_id INT
SET @category_id = 0

-- Get project id for the document selected
DECLARE @project_id DOM_SID
DECLARE @view_mask int
DECLARE @client_folder_id int

SET @view_mask = 64 -- just view access

SELECT @category_id = (SELECT category_id
FROM Document
WHERE id = @doc_id)

DECLARE @fill_table TABLE(doc_id int)

-- If the permissions are set as BPOLAdmin, set it to 2094 and delete all permissions on other folders
IF (@p_mask = 4194303)
BEGIN
DELETE FROM UserPermissions WHERE user_id = @user_id AND doc_id <> 0

INSERT INTO UserPermissions(user_id, doc_id, p_mask)
VALUES (@user_id, 2094, 4194303)
END
ELSE IF @doc_id = 2094
BEGIN
DELETE FROM UserPermissions WHERE user_id = @user_id AND doc_id <> 0

INSERT INTO UserPermissions(user_id, doc_id, p_mask)
VALUES (@user_id, 2094, @p_mask)
END
ELSE
BEGIN

--Delete permission for root folder
DELETE FROM UserPermissions
WHERE user_id = @user_id AND doc_id = 2094

--Insert view permission to root folder
INSERT INTO UserPermissions(user_id, doc_id, p_mask)
VALUES (@user_id, 2094, @view_mask)

-- If client folder is selected
IF @category_id = 257
BEGIN
DELETE FROM UserPermissions
WHERE user_id = @user_id AND doc_id = @doc_id

INSERT INTO UserPermissions(user_id, doc_id, p_mask)
VALUES (@user_id, @doc_id, @p_mask)
END
-- If Project is selected
ELSE
BEGIN
SELECT @project_id = dbo.getprojectid(@doc_id)
SELECT @client_folder_id = parent_id FROM BPView WHERE view_id = 1 AND id = @project_id
--If "Add project" permission is checked, assign it to Client folder
IF (POWER(2,0) & @p_mask > 0) OR (POWER(2,1) & @p_mask > 0) OR (POWER(2,2) & @p_mask > 0)
BEGIN

DELETE FROM @fill_table

--Get all the subfolder for the selected document
; WITH RootCTE1 (id, parent_id, idx) AS
(SELECT id, parent_id, idx
FROM dbo.BPView
WHERE id = @client_folder_id and view_id = 1
UNION ALL
SELECT bp.id, bp.parent_id, bp.idx
FROM dbo.BPView bp
INNER JOIN RootCTE1
ON RootCTE1.id = bp.parent_id and view_id = 1)

INSERT INTO @fill_table(doc_id)
SELECT D.id
FROM RootCTE1 R INNER JOIN
Document D ON D.id=R.id
where D.media_id = 256
order by id

--Delete permissions for subfolders
DELETE FROM UserPermissions
WHERE user_id = @user_id AND doc_id IN (SELECT doc_id FROM @fill_table)

INSERT INTO UserPermissions(user_id, doc_id, p_mask)
VALUES (@user_id, @client_folder_id, @p_mask)
END
ELSE
BEGIN
DELETE FROM UserPermissions
WHERE user_id = @user_id AND doc_id = @client_folder_id

INSERT INTO UserPermissions(user_id, doc_id, p_mask)
VALUES (@user_id, @client_folder_id, @view_mask)

--Get all the subfolder for the selected document
; WITH RootCTE (id, parent_id, idx) AS
(SELECT id, parent_id, idx
FROM dbo.BPView
WHERE id = @doc_id and view_id = 1
UNION ALL
SELECT bp.id, bp.parent_id, bp.idx
FROM dbo.BPView bp
INNER JOIN RootCTE
ON RootCTE.id = bp.parent_id and view_id = 1)

INSERT INTO @fill_table(doc_id)
SELECT D.id
FROM RootCTE R INNER JOIN
Document D ON D.id=R.id
where D.media_id = 256
order by id

--Delete permissions for subfolders
DELETE FROM UserPermissions
WHERE user_id = @user_id AND doc_id IN (SELECT doc_id FROM @fill_table)

IF @category_id = 258
BEGIN
INSERT INTO UserPermissions(user_id, doc_id, p_mask)
VALUES (@user_id, @doc_id, @p_mask)
END
-- If Folder is selected and Project Permissions are set, then set permission to the Folder's project
ELSE IF @category_id = 260 AND ((POWER(2,0) & @p_mask > 0) OR (POWER(2,1) & @p_mask > 0) OR (POWER(2,2) & @p_mask > 0) OR (POWER(2,13) & @p_mask > 0) OR (POWER(2,14) & @p_mask > 0) OR (POWER(2,15) & @p_mask > 0) OR (POWER(2,16) & @p_mask > 0) OR (POWER(2,17) & @p_mask > 0) OR (POWER(2,18) & @p_mask > 0) OR (POWER(2,19) & @p_mask > 0) OR (POWER(2,20) & @p_mask > 0) OR (POWER(2,21) & @p_mask > 0) OR (POWER(2,22) & @p_mask > 0) OR (POWER(2,23) & @p_mask > 0))
BEGIN
DELETE FROM UserPermissions
WHERE user_id = @user_id AND doc_id = @project_id

INSERT INTO UserPermissions(user_id, doc_id, p_mask)
VALUES (@user_id, @project_id, @p_mask)
END
-- If Folder is selected and Project permissions are off, then set permission to the selected folder and all its subfolders
ELSE
BEGIN
DELETE FROM UserPermissions
WHERE user_id = @user_id AND doc_id = @project_id

INSERT INTO UserPermissions(user_id, doc_id, p_mask)
SELECT @user_id, doc_id, @p_mask
FROM @fill_table

INSERT INTO UserPermissions(user_id, doc_id, p_mask)
VALUES (@user_id, @project_id, @view_mask)
END
END
END
END

--DROP TABLE #fill_child_docs

--Log the Persmission Changes
INSERT INTO UserPermissionsHistory( UserId, DocId, Mask, CreatedById, CreatedDate)
VALUES (@user_id, @doc_id, @p_mask, @created_by_id, GETDATE())

END

ALTER PROCEDURE [dbo].[BPOL_Grant_Permissions]
@user_id int,
@doc_id int,
@p_mask int,
@created_by_id int
AS
BEGIN

SET NOCOUNT ON;
DECLARE @category_id INT
SET @category_id = 0

-- Get project id for the document selected
DECLARE @project_id DOM_SID
DECLARE @view_mask int
DECLARE @client_folder_id int

SET @view_mask = 64 -- just view access

SELECT @category_id = (SELECT category_id
FROM Document
WHERE id = @doc_id)

DECLARE @fill_table TABLE(doc_id int)

-- If the permissions are set as BPOLAdmin, set it to 2094 and delete all permissions on other folders
IF (@p_mask = 4194303)
BEGIN
DELETE FROM UserPermissions WHERE user_id = @user_id AND doc_id <> 0

INSERT INTO UserPermissions(user_id, doc_id, p_mask)
VALUES (@user_id, 2094, 4194303)
END
ELSE IF @doc_id = 2094
BEGIN
DELETE FROM UserPermissions WHERE user_id = @user_id AND doc_id <> 0

INSERT INTO UserPermissions(user_id, doc_id, p_mask)
VALUES (@user_id, 2094, @p_mask)
END
ELSE
BEGIN

--Delete permission for root folder
DELETE FROM UserPermissions
WHERE user_id = @user_id AND doc_id = 2094

--Insert view permission to root folder
INSERT INTO UserPermissions(user_id, doc_id, p_mask)
VALUES (@user_id, 2094, @view_mask)

-- If client folder is selected
IF @category_id = 257
BEGIN
DELETE FROM UserPermissions
WHERE user_id = @user_id AND doc_id = @doc_id

INSERT INTO UserPermissions(user_id, doc_id, p_mask)
VALUES (@user_id, @doc_id, @p_mask)
END
-- If Project is selected
ELSE
BEGIN
SELECT @project_id = dbo.getprojectid(@doc_id)
SELECT @client_folder_id = parent_id FROM BPView WHERE view_id = 1 AND id = @project_id
--If "Add project" permission is checked, assign it to Client folder
IF (POWER(2,0) & @p_mask > 0) OR (POWER(2,1) & @p_mask > 0) OR (POWER(2,2) & @p_mask > 0)
BEGIN

DELETE FROM @fill_table

--Get all the subfolder for the selected document
; WITH RootCTE1 (id, parent_id, idx) AS
(SELECT id, parent_id, idx
FROM dbo.BPView
WHERE id = @client_folder_id and view_id = 1
UNION ALL
SELECT bp.id, bp.parent_id, bp.idx
FROM dbo.BPView bp
INNER JOIN RootCTE1
ON RootCTE1.id = bp.parent_id and view_id = 1)

INSERT INTO @fill_table(doc_id)
SELECT D.id
FROM RootCTE1 R INNER JOIN
Document D ON D.id=R.id
where D.media_id = 256
order by id

--Delete permissions for subfolders
DELETE FROM UserPermissions
WHERE user_id = @user_id AND doc_id IN (SELECT doc_id FROM @fill_table)

INSERT INTO UserPermissions(user_id, doc_id, p_mask)
VALUES (@user_id, @client_folder_id, @p_mask)
END
ELSE
BEGIN
DELETE FROM UserPermissions
WHERE user_id = @user_id AND doc_id = @client_folder_id

INSERT INTO UserPermissions(user_id, doc_id, p_mask)
VALUES (@user_id, @client_folder_id, @view_mask)

--Get all the subfolder for the selected document
; WITH RootCTE (id, parent_id, idx) AS
(SELECT id, parent_id, idx
FROM dbo.BPView
WHERE id = @doc_id and view_id = 1
UNION ALL
SELECT bp.id, bp.parent_id, bp.idx
FROM dbo.BPView bp
INNER JOIN RootCTE
ON RootCTE.id = bp.parent_id and view_id = 1)

INSERT INTO @fill_table(doc_id)
SELECT D.id
FROM RootCTE R INNER JOIN
Document D ON D.id=R.id
where D.media_id = 256
order by id

--Delete permissions for subfolders
DELETE FROM UserPermissions
WHERE user_id = @user_id AND doc_id IN (SELECT doc_id FROM @fill_table)

IF @category_id = 258
BEGIN
INSERT INTO UserPermissions(user_id, doc_id, p_mask)
VALUES (@user_id, @doc_id, @p_mask)
END
-- If Folder is selected and Project Permissions are set, then set permission to the Folder's project
ELSE IF @category_id = 260 AND ((POWER(2,0) & @p_mask > 0) OR (POWER(2,1) & @p_mask > 0) OR (POWER(2,2) & @p_mask > 0) OR (POWER(2,13) & @p_mask > 0) OR (POWER(2,14) & @p_mask > 0) OR (POWER(2,15) & @p_mask > 0) OR (POWER(2,16) & @p_mask > 0) OR (POWER(2,17) & @p_mask > 0) OR (POWER(2,18) & @p_mask > 0) OR (POWER(2,19) & @p_mask > 0) OR (POWER(2,20) & @p_mask > 0) OR (POWER(2,21) & @p_mask > 0) OR (POWER(2,22) & @p_mask > 0) OR (POWER(2,23) & @p_mask > 0))
BEGIN
DELETE FROM UserPermissions
WHERE user_id = @user_id AND doc_id = @project_id

INSERT INTO UserPermissions(user_id, doc_id, p_mask)
VALUES (@user_id, @project_id, @p_mask)
END
-- If Folder is selected and Project permissions are off, then set permission to the selected folder and all its subfolders
ELSE
BEGIN
DELETE FROM UserPermissions
WHERE user_id = @user_id AND doc_id = @project_id

INSERT INTO UserPermissions(user_id, doc_id, p_mask)
SELECT @user_id, doc_id, @p_mask
FROM @fill_table

INSERT INTO UserPermissions(user_id, doc_id, p_mask)
VALUES (@user_id, @project_id, @view_mask)
END
END
END
END

--DROP TABLE #fill_child_docs

--Log the Persmission Changes
INSERT INTO UserPermissionsHistory( UserId, DocId, Mask, CreatedById, CreatedDate)
VALUES (@user_id, @doc_id, @p_mask, @created_by_id, GETDATE())

END

Thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-03-02 : 17:19:35

Take a look at blocking and execution plans.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

megha123
Starting Member

4 Posts

Posted - 2011-03-03 : 09:49:24

I see no blocking and execution plan is same for both. Can execution plan be different in this scenario(same procedure just different names)

Thanks,
Megha

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources