sql server - Is there a more efficient way to write this SQL query? -

i have table couple million records in stores product views sessionguid (visitor), table structure is:

id (pk) sessionguid (uniqueidentifier) productid (int) datetime (datetime)

the purpose of query return list of product id's viewed same people viewed specific product id (ie, customers looked @ x looked @ y)

the query i'm using follows:

select a.productid visitor_sessions_productviews a.sessionguid in (select distinct sessionguid visitor_sessions_productviews productid = @productid) group a.productid order count(a.productid) desc 

is there more efficient way write using group by/having/partiton or other more optimized method of getting data way need it?

i running sql 2008 on dev server, running sql azure when goes live.

often, exists more efficient in:

select a.productid visitor_sessions_productviews exists (select 1               visitor_sessions_productviews b               b.productid = @productid ,                     a.sessionguid = b.sessionguid              ) group a.productid order count(a.productid) desc; 

for best performance, want index on visitor_sessions_productviews(sessionguid, productid) , perhaps visitor_sessions_productviews(productid).

edit:

you try writing using window functions, i'm not sure performance better:

select productid (select pv.*,              sum(case when productid = @productid 1 else 0 end) on (partition sessionguid) cnt       visitor_sessions_productviews       ) pv cnt > 0 group productid order count(*) desc; 

i'm not sure performance better exists method.