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.
| Author |
Topic |
|
tatia7d
Starting Member
1 Post |
 Posted - 2010-12-08 : 15:21:49
|
| Hi everyone, I need help please!!! i'm trying to execute an Sp, and is taking a lot of time, almost 2 min!i found that there is a piece of code that is giving the problem and I don't know another way to do it.--I add columns dynamically to a temp Table (#RawData), (in this point the SP takes 6 sec)--then, update the table to set the values, and i get that values calling a function (but in this point it takes 1min 49 sec)Here is the piece of code of the Stored Procedure:.. ...Set @count = 1 Select @minId = Min(Id), @maxId= MAX(Id) from #test while(@minId <= @maxId) BEGIN Select @type = TypeId from #test where Id= @minId if(@type != 3 or @type != 4) begin set @ColumnName = 'Q' + CAST(@count as varchar(10)) exec('alter table #rawdata add ' + @ColumnName + ' varchar(10) NULL') exec ('UPDATE #rawdata SET ' + @ColumnName + ' = [dbo].[GetAnswer](' + @minId + ',' + @countmember + ',' + @countsection + ',' + @CustomerSurvey_Id + ',' + @IsIten + ',''' + @FromDate + ''',''' + @EndDate + ''', #rawdata.Survey_Id )') set @count = @count + 1 set @type = null end Set @minId = @minId + 1END..(more code..)..Thanks in advance |
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2010-12-08 : 16:50:32
|
| It will call the function for each row in the table and will always be slow.Make each call to the function faster i.e. make the function faster or (much better) remove the function in favour of in-line code.Does it take 6 secs to add the columns to the temp table? That is very long and makes me think you don't have a good approach. Maybe a single table with three columns id, column name, value would be beter - then you wouldn't need dynamic sql.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
|
|
|
|
|
Update Dynamically a temp Table...Plaeseeee HELP