John

John

  • NA
  • 928
  • 125.7k

How to apply index or optimise the query in SQL server

Aug 19 2017 4:05 AM
select C,F,v1Q,v2Q,v2Q-v1Q as v2D,v3Q,v3Q-v1Q as v3D,v4Q,v4Q-v1Q as v4D from (
select C,F,
v1Q=(select count(distinct(I)) from Table t1 right join Tbl t2 on M=U
where t2.File=@File and C in (select item from dbo.Split(@C,',')) and version =@base and t1.F= m.F and t1.CId=@CId group by F)
,v2Q=(select count(distinct(I)) from Table t1 right join Tbl t2 on M=U
where t2.File=@File and C in (select item from dbo.Split(@C,',')) and version =@dcol1 and t1.F= m.F and t1.CId=@CId group by F)
,v3Q=(select count(distinct(I)) from Table t1 right join Tbl t2 on M=U
where t2.File=@File and C in (select item from dbo.Split(@C,',')) and version =@dcol2 and t1.F= m.F and t1.CId=@CId group by F)
,v4Q=(select count(distinct(I)) from Table t1 right join Tbl t2 on M=U
where t2.File=@File and C in (select item from dbo.Split(@C,',')) and version =@dcol3 and t1.F= m.F and t1.CId=@CId group by F)
from Table m right join Tbl d on M=U
where d.File=@File and C in (select item from dbo.Split(@C,',')) and m.CId=@CId group by F,C
) X order by C

Answers (2)