Performance of the procedure

[krotha]

I have a procedure it takes long time to run this procedure

alter procedure test as
declare @Id int,@Type char(10),@total_created int,@total_orders_created int,@total_completed int,@log_total int,@log_aging int

declare c1 cursor for
select Id,Type
from temp1
open c1
fetch next from c1 into @id,@type
WHILE (@@FETCH_STATUS <> -1)
begin
IF (@@FETCH_STATUS <> -2)

select @total_created = count(tempfinal.id)
from tempfinal,temp1
where tempfinal.id = @id
and tempfinal.type = @type
and tempfinal.id = temp1.id
and tempfinal.type = temp1.type

select @total_orders_created = count(tempfinal.id)
from tempfinal,temp1
where tempfinal.Id = @Id
and tempfinal.type = @type
and tempfinal.id = temp1.id
and tempfinal.type = temp1.type
and tempfinal.status ='TD'


select @total_completed = count(tempfinal.id)
from tempfinal,temp1
where tempfinal.Id = @Id
and tempfinal.Type = @Type
and tempfinal.Id = temp1.Id
and tempfinal.Type = temp1.Type
and tempfinal.status ='TO'

select @log_total = count(tempfinal.Id)
from tempfinal,temp1
where tempfinal.Id = @Id
and tempfinal.Type = @Type
and tempfinal.Id = temp1.Id
and tempfinal.Type = temp1.Type
and tempfinal.status NOT IN('TO')


select @log_aging = count(tempfinal.Id)
from tempfinal,temp1
where tempfinal.Id = @Id
and tempfinal.Type = @Type
and tempfinal.Id = temp1.Id
and tempfinal.Type = temp1.Type
and tempfinal.wo_status NOT IN('TO')
and Datediff(day,tempfinal.PDate,getdate()) <=45



update temp1
set total_records = @total_created,
created =@total_orders_created,completed =@total_completed,
log_Total = @log_total, log_aging = @log_aging
where Id = @Id
and Type = @Type


fetch next from c1 into @Id,@Type

end
close c1
deallocate c1


Each variable in the cursor taking time, is there any way I can write this query bettter for performance point of view

 

[vongrunt]

Let's get educational .

For starters, take a look at this:

select @total_created = count(tempfinal.id)
from tempfinal,temp1 
where tempfinal.id = @id
and tempfinal.type = @type
and tempfinal.id = temp1.id
and tempfinal.type = temp1.type

If @id and @type are constants during query call, what is the purpose of temp1 table anyway? All you need (@id, @type) is already fetched from cursor, right?

This simplified version should do the same:

select @total_created = count(tempfinal.id)
from tempfinal
where tempfinal.id = @id
and tempfinal.type = @type

All five (5) SELECTs within a loop look very similar. The only difference is WHERE filter for COUNTing. Write all-in-one query a la:

select 
	@total_created = count(tempfinal.id),
	@total_orders_created = sum(case when tempfinal.status='TD' then 1 else 0 end),
	@total_completed = sum(case when tempfinal.status='TO' then 1 else 0 end),
	@log_total = sum(case when tempfinal.status NOT IN ('TO') then 1 else 0 end),
	@log_aging = sum(case when tempfinal.status NOT IN ('TO') and Datediff(day,tempfinal.PDate,getdate()) <=45 then 1 else 0 end)
from tempfinal
where tempfinal.id = @id
and tempfinal.type = @type

This will reduce number of SELECTs by 80% (1 vs 5).

Next step is about eliminating the cursor. You can get all counts in one noncorrelated query (only tempfinal table is used):

select 	Id, Type,
	count(Id) as total_created,
	sum(case when status='TD' then 1 else 0 end) as total_orders_created,
	sum(case when status='TO' then 1 else 0 end) as total_completed,
	sum(case when status NOT IN ('TO') then 1 else 0 end) as log_total,
	sum(case when status NOT IN ('TO') and Datediff(day, PDate,getdate()) <=45 then 1 else 0 end) as log_aging
from tempfinal
group by Id, Type

At this point cursor can be easily eliminated:

update T
set	total_records = isnull(X.total_created, 0),
	created = isnull(X.total_orders_created, 0),
	completed = isnull(X.total_completed, 0),
	log_Total = isnull(X.log_total,  0),
	log_aging =  isnull(X.log_aging, 0)
from temp1 T
left outer join
(	<query from above>
) X on T.id=X.id and T.Type=X.Type

Btw I have no sample data for testing, therefore expect some bugs & features :X

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)