SQL Server Performance Forum – Threads Archive

Improved performance advice for my SP

Hi, I’m a web developer with enough SQL skills to be dangerous (as evidenced below); I’ve written the following SP and I know it needs help but I’m not sure what help and where; lose the cursors for while statements? lose the temp table? Thanks for any tips!!! SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
ALTER PROCEDURE TT_get_project_view_MONTH
(@proj int,@month as varchar(5), @year as varchar(5))
as
create table #pso_view(id int, Name varchar(50))
declare @day2 as varchar(20)
declare @num as int
declare @col as varchar(20)
declare @hrs as varchar(10)
declare @hrs2 as varchar(10)
declare @date as varchar(25)
declare @project as int
declare @sql as nvarchar(250)
set @num=1
while @num <= 32
begin exec (‘ALTER TABLE #pso_view ADD [‘ + @NUM + ‘]decimal(4,2) DEFAULT 0’)
set @num = @num + 1
end
if (@proj=0)
begin
DECLARE new2 CURSOR
for
select userid, displayname from tt_users where username <> ” and username is not null order by displayname
–select userid, displayname from tt_users where left(lower(displayname),10)<> ‘unassigned’ order by displayname
open new2
FETCH NEXT FROM new2
INTO @num,@col
WHILE @@FETCH_STATUS = 0
BEGIN
insert into #pso_view
(id,Name) values(@num,@col)
FETCH NEXT FROM new2
INTO @num,@col
end
CLOSE new2
DEALLOCATE new2
end
else
begin
DECLARE new2 CURSOR
for
SELECT
u.UserID,u.displayname FROM
TT_ProjectMembers pm
INNER JOIN
TT_Users u
On
u.UserID = pm.UserID WHERE
ProjectID = @proj and username <> ” and username is not null
order by displayname
open new2
FETCH NEXT FROM new2
INTO @num,@col
WHILE @@FETCH_STATUS = 0
BEGIN
insert into #pso_view
(id,Name) values(@num,@col)
FETCH NEXT FROM new2
INTO @num,@col
end
CLOSE new2
DEALLOCATE new2
end
if (@proj=0)
begin
DECLARE abc CURSOR FOR
select userid,
durationforecast, entrydate, projectid
from tt_entrylog where entrydate is not null and month(entrydate)[email protected] and year(entrydate)[email protected] and userid<>0
OPEN abc
FETCH NEXT FROM abc
into @num,@hrs,@date,@project
set @num=cast(@num as varchar(10))
set @hrs2=cast(@hrs2 as decimal(4,2)) WHILE @@FETCH_STATUS = 0
BEGIN
set @day2=datepart( d,@date)
set nocount on
select @sql=(‘select @hrs2 = sum(‘+quotename(@day2)+’) from #pso_view where id = ‘)
select @sql= @sql+cast(@num as varchar(5))
EXEC sp_executesql @sql,N’@hrs2 decimal OUTPUT’,@hrs2 OutPut
set @hrs=(cast(@hrs as decimal(4,2))+cast(@hrs2 as decimal(4,2))) set @sql=(‘update #pso_view set ‘+ quotename(@day2)+ ‘= ‘[email protected])
set @[email protected]+’ where id = ‘+cast(@num as varchar(5)) execute(@sql)
FETCH NEXT FROM abc
into @num,@hrs,@date,@project
END
CLOSE abc
DEALLOCATE abc
end
else
begin
DECLARE abc CURSOR FOR
select userid,
durationforecast, entrydate, projectid
from tt_entrylog
where entrydate is not null
and month(entrydate)[email protected] and year(entrydate)[email protected] and userid<>0
and [email protected]
OPEN abc
FETCH NEXT FROM abc
into @num,@hrs,@date,@project
set @num=cast(@num as varchar(10))
set @hrs2=cast(@hrs2 as decimal(4,2)) WHILE @@FETCH_STATUS = 0
BEGIN
set @day2=datepart( d,@date)
set nocount on
select @sql=(‘select @hrs2 = sum(‘+quotename(@day2)+’) from #pso_view where id = ‘)
select @sql= @sql+cast(@num as varchar(5))
EXEC sp_executesql @sql,N’@hrs2 decimal OUTPUT’,@hrs2 OutPut
set @hrs=(cast(@hrs as decimal(4,2))+cast(@hrs2 as decimal(4,2))) set @sql=(‘update #pso_view set ‘+ quotename(@day2)+ ‘= ‘[email protected])
set @[email protected]+’ where id = ‘+cast(@num as varchar(5)) execute(@sql)
FETCH NEXT FROM abc
into @num,@hrs,@date,@project
END
CLOSE abc
DEALLOCATE abc
end
select * from #pso_view
drop table #pso_view GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

---

It is hard to tell from your code exactly what your goals are, but ideally, if you don’t need a cursor or temp table, then loose them. In most cases, there are alternatives to both of these performance hogs. Also, the use of not null is often a performance issue. Perhaps if you could write a short description of what you what to accomplish, then we might be able to offer better advice. —————————–
Brad M. McGehee, MVP
Webmaster
SQL-Server-Performance.Com

---

In general the recommendation is try to avoid using cursors and for tips review this articlehttp://www.databasejournal.com/features/mssql/article.php/1439731 and this articlehttp://www.sql-server-performance.com/visual_basic_performance.asp using VB & SQL> HTH Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.

---

]]>