create procedure test1
@date_to smalldatetime
,@date_from smalldatetime
as


select 
d.id
,d.Q
,d.cash
,d.price

from

(
select
	count(1) as id
	,sum(OrderQty) as Q
	,sum(UnitPrice)/100 as cash
	,p_d.UnitPrice as price
from
	Purchasing.PurchaseOrderDetail as p_d
	join Purchasing.PurchaseOrderHeader as p
	on p_d.PurchaseOrderID = p.PurchaseOrderID
where 
	p_d.DueDate between @date_from and @date_to

group by 
	p_d.PurchaseOrderID
	,p_d.DueDate
	,p_d.UnitPrice
) as d

group by d.Q,d.id,d.price,d.cash

go

create procedure test2
@date_to smalldatetime
,@date_from smalldatetime
as

declare @date1 smalldatetime
declare @date2 smalldatetime

set @date1=@date_to
set @date2=@date_from

select 
d.id
,d.Q
,d.cash
,d.price

from

(
select
	count(1) as id
	,sum(OrderQty) as Q
	,sum(UnitPrice)/100 as cash
	,p_d.UnitPrice as price
from
	Purchasing.PurchaseOrderDetail as p_d
	join Purchasing.PurchaseOrderHeader as p
	on p_d.PurchaseOrderID = p.PurchaseOrderID
where 
	p_d.DueDate between @date1 and @date2

group by 
	p_d.PurchaseOrderID
	,p_d.DueDate
	,p_d.UnitPrice
) as d

group by d.Q,d.id,d.price,d.cash

go

quote:

---

Originally posted by slimt_slimt
the second procedure executes much faster than the first one (especially visible if you have huge database - which adventureworks is not).

---