SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 SPID in cursor
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

hiteshp27
Starting Member

USA
3 Posts

Posted - 12/10/2012 :  11:26:28  Show Profile  Reply with Quote
Hi,

I have a question related to SPID used within a cursor in a trigger in SQL Server 2008 R2.

I have a trigger for update on a table. This trigger is using cursor to iterate over the inserted rows. In the cursor there is a statement that calls a stored procedure that creates a global temp table using SPID if the one does not exists already.

Now, since this entire thing is happening for each row in the cursor, I think it is affecting overall trigger performance. I was thinking to move the call to stored procedure out of the cursor. Here is the sample trigger code.


create trigger POSUPD_TRNCHRGS
on TRANSACTIONCHARGES
for update
as
	declare @trnCode	nvarchar(30)
	declare @trnDesc	nvarchar(50)
	declare @nReturn	nvarchar(50)
	declare @sTransactionid	nvarchar(30)
	
	if @@ROWCOUNT > 0
	begin
		SET NOCOUNT ON
		
		-- MOVE HERE
		
		declare c_updated cursor local for select trn_code, trn_desc from inserted
		open c_inserted
		fetch next from c_inserted into @trnCode, @trnDesc
		while @@FETCH_STATUS = 0
		begin
			-- This is the call that executes GETSESSIONVARIABLE for each row of the cursor
			-- The GETSESSIONVARIABLE stored procedure creates the global temp table based on SPID if the one does not exists
			-- I wanted to move this call out of the while loop at comment MOVE HERE
			EXECUTE @nReturn = GETSESSIONVARIABLE @sTransactionid OUTPUT
			if @@ERROR <> 0 or @nReturn <> 0
			begin
				close c_updated
				deallocate c_updated
				rollback transaction
				return
			end
			
			-- Take necessary action here...
		end
	end
GO


I am not sure of one thing while moving the call to stored procedure GETSESSIONVARIABLE out of the cursor.

Do we have a chance that for a single trigger execution we receive different SPIDs for each row iteration? Is there such chance if there are multiple updates from multiple users happening?

Regards,
Hitesh

robvolk
Most Valuable Yak

USA
15659 Posts

Posted - 12/10/2012 :  13:19:39  Show Profile  Visit robvolk's Homepage  Reply with Quote
quote:
Now, since this entire thing is happening for each row in the cursor, I think it is affecting overall trigger performance
That is the understatement of the week.

I'm not clear on why you need a "global temp table based on SPID". What's wrong with a plain ol' temp table? Why would other spids need to access that data? What "session" data are you creating/storing in that stored procedure? (posting that code would help us troubleshoot better)
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 12/10/2012 :  13:56:31  Show Profile  Reply with Quote
Any chance that you can tell us what you are trying to do? We might be able to come up with a set-based solution which will, more than likely, be faster. Using a cursor, especially in a trigger, is probably not the best approach.
Go to Top of Page

hiteshp27
Starting Member

USA
3 Posts

Posted - 12/11/2012 :  17:22:40  Show Profile  Reply with Quote
Thanks robvolk and Lamprey for your replies.

robvolk, I am not sure why the stored procedure is using global temp table, because that is the legacy stored procedure we have and I really do not want to touch it. I can however tell you that the purpose of creating that global temp table based on SPID is because it tries to achieve the concept similar to packages in oracle.

Since the original stored procedure is big, I am pasting here only the required number of lines. The trigger code having call to this stored procedure is already given in my previous example.


create procedure GETSESSIONVARIABLE 	@sPackage	nvarchar(32), 
										@sVariable	nvarchar(32), 
										@sValue		nvarchar(2000)	OUTPUT
as
	SET NOCOUNT ON
	declare @sSQL	nvarchar(500)
	declare @sTable	nvarchar(32)
	declare @nCount	int
	
	SET @sValue = N'@@Error@@'

	SET @sTable = N'##GTMP_PACKAGE_VARIABLES' + CAST( @@SPID as nvarchar(10) )

	--create temp table if table does not already exist
	if object_id('tempdb..' + @sTable ) is null
	BEGIN
		SET @sSQL = N'select * into ' + @sTable + N' from PACKAGEVARIABLES'
		EXEC (@sSQL)
	END
	
	--verify its existance
	if object_id('tempdb..' + @sTable ) is null		
		RAISERROR ('Unable to create temporary package variable table.', 16, 1 )
	
	--get the package variable value
	SET @sSQL = N'select @sValue = VARIABLE_VALUE from ' + @sTable
	SET @sSQL = @sSQL + N' where PACKAGE_NAME	= ''' + @sPackage + N''' and '
	SET @sSQL = @sSQL + N'		VARIABLE_NAME	= ''' + @sVariable + N''''
			
	EXEC sp_executesql @sSQL, N'@sValue nvarchar(2000) OUTPUT', @sValue OUTPUT
go


The stored procedure above may not compile as a whole, but should be sufficient to give you better idea.

Since this stored procedure is called from a trigger within cursor, I was looking to move it out for performance reason. My question is, if I move the call to GETSESSIONVARIABLE out of the cursor, do we have chance to break the original purpose of calling the stored proc within cursor? I am mainly confused because it is using SPID. I personally do not see that the value of SPID would differ for single trigger execution, but I wanted to confirm it with advanced users.
Go to Top of Page

robvolk
Most Valuable Yak

USA
15659 Posts

Posted - 12/11/2012 :  17:51:31  Show Profile  Visit robvolk's Homepage  Reply with Quote
That makes sense, this was almost certainly generated by the SQL Server Migration Wizard Assistant. I wouldn't recommend changing it either, even though it sucks. Rewriting this would require looking at the packages involved and seeing how/if the package variables are modified, and unless you have a very small migrated codebase is probably not worthwhile.

From what I can tell in this code though, the dumbass Microsoft programmer that wrote this could have used a single table for ##GTMP_PACKAGE_VARIABLES and included a SPID column to managed each SPID's values from PACKAGEVARIABLES, and adding a WHERE condition for SPID=@@SPID. That would have eliminated the need for dynamic SQL and multiple global temp tables. That's the only avenue I'd recommend re: rewriting this code, you'd also have to look for and modify a SETSESSIONVARIABLE procedure to match.

One thing I should point out, the trigger defines @sTransactionid as nvarchar(30), but the GETSESSIONVARIABLE @sValue output parameter is declared nvarchar(2000). This could cause data truncation.
Go to Top of Page

hiteshp27
Starting Member

USA
3 Posts

Posted - 12/13/2012 :  12:03:33  Show Profile  Reply with Quote
Thanks robvolk for ruling out the option of rewriting the code to avoid multiple global temp tables.

My original question still remains unanswered though. Can I at least for performance reason move the call to GETSESSIONVARIABLE out of the while loop of a cursor in trigger? Do you see any issue here if I move the call out at MOVE HERE comment? That way I can at least save myself from calling GETSESSIONVARIABLE multiple times.
Go to Top of Page

robvolk
Most Valuable Yak

USA
15659 Posts

Posted - 12/13/2012 :  12:33:28  Show Profile  Visit robvolk's Homepage  Reply with Quote
I can't answer that without seeing what the trigger actually does. The code you posted doesn't even reference the cursor, and the call to GETSESSIONVARIABLE doesn't look complete either, it's not passing the package or variable names, it should throw an error in that state.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000