SQL Server query very slow, how can I improve speed?

Question

I have three tables, regarding certain items which require regular inspection:

Collections  
-Id

CollectionItems (ie, the individual items within a group of items)  
-Id  
-Name  
-CollectionId  
-NotAvailable  
-LastInspectionDate

ItemReports (inspection reports on items)  
-Id  
-CollectionItemId  
-ReportDate

I want to display a list of all items within a collection. One of the columns shown should be the most recent inspection date of the item.

However, this date needs to be the last ACTUAL inspection date, not the date an inspection was last attempted.

• If the engineer goes to look at the collection and one item can't be found, the reason (ID) is put into the "NotAvailable" field. No report is created.

• If the item is found, NotAvailable will be 0 and a report will be created.

LastInspectionDate in the CollectionItems table is updated regardless of whether the item was found or not.

So I want the last ReportDate, and if no reports exist then I want the LastInspectionDate.

The query I have is basically:

SELECT 
    CollectionItems.*,
    ISNULL(MAX(CASE WHEN ItemReports.NotAvailable = 0 THEN ReportDate END), 
    CollectionItems.LastInspectionDate) AS LastActualDate
FROM 
    CollectionItems
LEFT JOIN 
    ItemReports ON ItemReports.CollectionItemID = CollectionItems.Id

This works fine, but is very slow. On a collection of 300+ items this can take well over 30 seconds.

How can I improve the performance of this, ideally in one query?

Answer 1

Without the query plan it's difficult to say, but you are probably going to want an index to make this efficient. Either a clustered or non-clustered index will do.

ItemReports (CollectionItemId, NotAvailable, ReportDate)

I would assume CollectionItems.Id is a clustered primary key, if not then you should make it seo