Cursor taking time

Question

Post reply

Cursor taking time

samsql

Hall of Fame

Points: 3044
More actions
March 31, 2010 at 11:22 pm

#139068

hi all,
Can i write this cusor with other query
DECLARE DYM CURSOR FOR
SELECT ISNULL(T_SYSMG.FLD,''),ISNULL(T_SYSMG.ENTRYTYPE,''),
ISNULL(T_SYSMG.IsDisplay,0), ISNULL(T_SYSMG.DEFAULTVALUE,''),T_SYSSD.FD
FROM T_SYSMG
Left Outer Join T_SYSSD On T_SYSMG.FLD=T_SYSSD.FLD
And T_SYSSD.BRCH = @BRCH
Where T_SYSMG.FLD NOT IN (SELECT NAME FROM SYSCOLUMNS WHERE ID=OBJECT_ID('T_S'))
OPEN DYM
FETCH DYM INTO @fld,@ENTRYTYPE,@GRIDITEM,@DEFAULTVALUE,@FD
WHILE @@FETCH_STATUS = 0
BEGIN
IF @ENTRYTYPE='B' AND @fld<>'' AND @GRIDITEM=1
BEGIN
SET @EXECSTRING='ALTER TABLE #STG ADD '+ @fld + ' BIT'
EXEC(@EXECSTRING)
SET @EXECSTRING=''
END
IF (@ENTRYTYPE='T' AND @fld<>'' AND @GRIDITEM=1)
BEGIN
SET @EXECSTRING='ALTER TABLE #STG ADD '+ @fld + ' VARCHAR(200)'
EXEC(@EXECSTRING)
SET @EXECSTRING=''
END
----UPDATE FIELDS WITH DEFAULT VALUES STORED IN FIELD [FD]
----**************************************************************
IF @ENTRYTYPE='B' AND @fld<>'' AND @GRIDITEM=1
BEGIN
--Check there is a FD from database. If not, just use the default Value
SET @FD = ISNULL(@FD,ISNULL(@DEFAULTVALUE,0))
SET @EXECSTRING='UPDATE #STG SET '+ @fld + ' = ' + ISNULL(@FD,0)
EXEC(@EXECSTRING)
SET @EXECSTRING=''
END
IF @ENTRYTYPE='T' AND @fld<>'' AND @GRIDITEM=1
BEGIN
--Check there is a FD from database. If not, just use the default Value
SET @FD = ISNULL(@FD,ISNULL(@DEFAULTVALUE,''))
--SET @EXECSTRING='UPDATE #STG SET '+ @fld + ' = ''' + ISNULL(@FD,'') + ''''
SET @EXECSTRING='UPDATE #STG SET '+ @fld + ' = ''' + @FD + ''''
EXEC(@EXECSTRING)
SET @EXECSTRING=''
END
FETCH NEXT FROM DYM INTO @fld,@ENTRYTYPE,@GRIDITEM, @DEFAULTVALUE,@FD
END
CLOSE DYM
DEALLOCATE DYM

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 1

What exactly are you trying to achieve ?

Whenever i see a dynamic alter table statement my number 1 thought is bad design.

Clear Sky SQL
My Blog[/url]

samsql Hall of Fame Points: 3044 More actions · Answer 2

Dave this cursor is taking time can i use fast forword only option in this cursor...

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 3

I would guess that the time is being taken by the ALTER TABLE and UPDATE Statements not the cursor itself.

Try proving this use the Statement Trace in SQLProfiler.

My original point still stands, what exactly are you attempting to achieve ?

Clear Sky SQL
My Blog[/url]

samsql Hall of Fame Points: 3044 More actions · Answer 4

Dave the proc cant be muched changed due to some activities and the procs has been written by some other dev and i have to tune as no one knows wat exactly the proc is doing but the only thing i know is tune this proc ...as per my manager .......

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 5

Looking at this loop in isolation , you should be able to build together two string.

One to Alter the table to add all the columns required in one hit :

ie

alter table #x1 add col1 integer , col2 integer

and a similar one to update the columns setting them all to their defaults

You can remove the cursor completely by using XML string concatenation as detailed here http://www.simple-talk.com/sql/t-sql-programming/concatenating-row-values-in-transact-sql/

Clear Sky SQL
My Blog[/url]