How to apply index or optimise the query in SQL server

John

9y808

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

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Manish Kumar9y ago
Accepted answer
How much time it is taking. Check the execution plan for this query where is the table scan / index scan or key look up.
Basic rule for optimization is:
--Avoid subquery written in your query.
--Order by is also making slow.

Other causes...
--Statistics is not updated
--plan cache i.e whether query is getting data from cache means doing logical read or doing physical read from disk
--Index fragmentation.

Thanks,
Manish
+1
Mukil Vendhan9y ago
kindly refer this link:http://searchsqlserver.techtarget.com/tip/How-to-maintain-SQL-Server-indexes-for-query-optimization
+1