SQLTeam.com | Weblogs | Forums

Way to optimize this sql

tsql

#1

Top and sort kills this ?

select top 1000 CourseAssetId, CourseInitialId , CourseSize , [filename] from CourseAsset
where CourseID = 21 and Status = 'Published' and FolderId = 8765 and CreateDate < '2016-02-20 23:25:36.000' and
CourseAssetId not in (select CourseAssetId from CourseImage where CourseAssetId
is not null ) Order by CourseAssetSize desc

All indexes are in place . Is there a way to optimize this?


#2

Hi,
It might be easier if you can post the execution plan, so that we can look into where can optimize and make suggestion.


#3

DECLARE @Date datetime = '2016-02-20 23:25:36.000'

SELECT  TOP 1000 
	ca.CourseAssetId
,	ca.CourseInitialId 
,	ca.CourseSize 
,	ca.[filename] 
FROM	CourseAsset ca
	LEFT OUTER JOIN CourseImage ci ON ca.CourseAssetId = ci.CourseAssetId AND ISNULL(ci.CourseAssetId,0) <> 0
WHERE	ca.CourseID = 21 
AND	ca.[Status] = 'Published' 
AND	ca.FolderId = 8765 
AND	ca.CreateDate < @Date 
AND	ci.CourseAssetId IS NULL
Order by ca.CourseAssetSize desc

#4

Covering the WHERE clause AND the Order By?


#5

So you can index on CourseAsset on ( FolderId, CourseID, CreateDate ) INCLUDE ( CourseAssetId, Status )?