Select distinct c.classID, co.fCourseName as CourseName, StreetAddress + ', ' + l.City as LocationAddress, s.SessionName, sh.fShift as shift, StartTime, EndTime, c.classname, s.SessionID,
c.StartDate,c.enddate
From dbo.vw_Class c
Inner Join dbo.lk_Session s
On (s.SessionID = c.sessionID)
Inner Join dbo.lk_Course co
On (co.CourseID = c.CourseID )
Inner Join dbo.vw_Location l
On (l.locationid = c.locationid)
Inner Join lk_District d
On (d.districtID = c.districtId)
Inner Join lk_Province p
On (p.provik = d.provik)
Inner Join lk_Shift sh
On (c.shiftid = sh.shiftid)
where
c.DistrictID = case when @Districtid is null then c.DistrictID else @Districtid end
and c.LocationID = case when @locationid is null then c.LocationID else @locationid end
and s.SessionID = case when @sessionid is null then s.SessionID else @sessionid end
and c.CourseID = case when @levelid is null then c.CourseID else @levelid end
and c.ShiftID = case when @shiftid is null then c.ShiftID else @shiftid end
and c.StartDate >= case when @startdate is null then c.StartDate else @startdate end
and c.EndDate <= case when @enddate is null then c.EndDate else @enddate end
and convert(time,c.StartTime) >= case when @starttime is null then convert(time,c.StartTime) else convert(time,@starttime) end
and convert(time,c.endtime) <= case when @endtime is null then convert(time,c.endtime) else convert(time,@endtime) end
and c.Monday = case when @day1 = 'N' then c.monday else @day1 end
and c.Tuesday = case when @day2 = 'N' then c.Tuesday else @day2 end
and c.Wednesday = case when @day3 = 'N' then c.Wednesday else @day3 end
and c.Thursday = case when @day4 = 'N' then c.Thursday else @day4 end
and c.Friday = case when @day5 = 'N' then c.Friday else @day5 end
and c.Saturday = case when @day6 = 'N'then c.Saturday else @day6 end
and c.Sunday = case when @day7 = 'N' then c.Sunday else @day7 end
and c.RowStatus = 'A'
ORDER BY co.fCourseName, s.SessionID ,c.ClassName
In the above code for which columns i need to create the index.(None of tables used in this query don't have either primary key or indexes created)
First of all, here is your sql query with format, definite in my company. I replaced your filters from case (not surgable) to "OR".
SELECT DISTINCT
[c].[classID]
, [co].[fCourseName] as [CourseName]
, [StreetAddress] + ', ' + [l].[City] as [LocationAddress]
, [s].[SessionName]
, [sh].[fShift] as [shift]
, [StartTime]
, [EndTime]
, [c].[classname]
, [s].[SessionID]
, [c].[StartDate]
, [c].[enddate]
FROM
[dbo].[vw_Class] as [c]
INNER JOIN
[dbo].[lk_Session] as [s] ON [s].[SessionID] = [c].[sessionID]
INNER JOIN
[dbo].[lk_Course] as [co] ON [co].[CourseID] = [c].[CourseID]
INNER JOIN
[dbo].[vw_Location] as [l] ON [l].[locationid] = [c].[locationid]
INNER JOIN
[lk_District] as [d] ON [d].[districtID] = [c].[districtId]
INNER JOIN
[lk_Province] as [p] ON [p].[provik] = [d].[provik]
INNER JOIN
[lk_Shift] as [sh] ON [c].[shiftid] = [sh].[shiftid]
WHERE
(
[c].[DistrictID] = @Districtid
OR
@Districtid IS NULL
)
AND
(
[c].[LocationID] = @locationid
OR
@locationid IS NULL
)
AND
(
[s].[SessionID] = @sessionid
OR
@sessionid IS NULL
)
AND
(
[c].[CourseID] = @levelid
OR
@levelid IS NULL
)
AND
(
[c].[ShiftID] = @shiftid
OR
@shiftid IS NULL
)
AND
(
[c].[StartDate] >= @startdate
OR
@startdate IS NULL
)
AND
(
[c].[EndDate] <= @enddate
OR
@enddate IS NULL
)
AND
(
convert(time, [c].[StartTime]) >= @starttime
OR
@starttime IS NULL
)
AND
(
convert(time, [c].[endtime]) <= @endtime
OR
@endtime IS NULL
)
AND
(
convert(time, [c].[endtime]) <= @endtime
OR
@endtime IS NULL
)
AND
(
[c].[Monday] = @day1
OR
@day1 = 'N'
)
AND
(
[c].[Tuesday] = @day2
OR
@day2 = 'N'
)
AND
(
[c].[Wednesday] = @day3
OR
@day3 = 'N'
)
AND
(
[c].[Thursday] = @day4
OR
@day4 = 'N'
)
AND
(
[c].[Friday] = @day5
OR
@day5 = 'N'
)
AND
(
[c].[Saturday] = @day6
OR
@day6 = 'N'
)
AND
(
[c].[Sunday] = @day7
OR
@day7 = 'N'
)
AND
[c].[RowStatus] = 'A'
ORDER BY
[co].[fCourseName]
, [s].[SessionID]
, [c].[ClassName]
In the answer of your question, you need index on all fields, wich one used in joins (like [sessionID], [CourseID] etc).
You can't always use index on view, but if you can, create it on field based on repeated values. I used next rule - "If value in some column repeated more then 10% and we can use it in filter - create index".