[SOLVED] Using SQL Server indexes to increase performance

Using SQL Server indexes to increase performance

We are using SQL Server and SSMS 18.4.

We have a TripAsset table with these columns:

TripAssetID
TripID
Attachment1
Attachment2
Attachment3

We have a grid view which lists trips and data from the trip file. Each line in the grid must indicate the trip has Attachment1, Attachment2, Attachment3, etc.

The SQL to do this was joining on the TripAsset table and retrieving the entire attachment (which could be very large) just to indicate that the attachment exists. This was, obviously very slow.

A programmer suggested creating a separate file which includes TripID, hasAttachment1, hasAttachment2, hasAttachment3, etc. Then some process would keep this table in sync with the TripAsset table.

I think there has to be a better way, so I created the index tripHasAttachment1 like this:

CREATE INDEX tripHasAttachment1
ON [dbo].[TripAsset] (TripID)
WHERE [TripAsset].[Attachment1] IS NOT NULL

Now I can run a query like below, where tripID is the trip for which I want to see if there is an attachment.

SELECT TripID 
FROM TripAsset WITH(INDEX(tripHasAttachment1)) 
WHERE TripID = [tripID]

This seems to run very quickly.

Is this the best way to attack this problem? Do I need the with clause?

I would appreciate any suggestions.

Solution

You don't need to retrieve the attachment to check whether it is there:

Select
  TripAssetID,
  TripID,
  HasAttachment1 = Cast((Case When Attachment1 Is Null Then 0 Else 1 End) As Bit),
  HasAttachment2 = Cast((Case When Attachment2 Is Null Then 0 Else 1 End) As Bit),
  HasAttachment3 = Cast((Case When Attachment3 Is Null Then 0 Else 1 End) As Bit)
From
  TripAsset