Advice on how to improve the efficiency of a stored procedure using lots of joins

I have a stored procedure that is returning search results for items in a database grouped by customers who own them.

The user performing the search is restricted as to what items they can see by the category permissions and entity permissions.

The user category permissions are passed in on the @CategoryPermissions parameter and are filtered against the tariff the customer is on.

Items are associated with entities (via a number of combinations of BusinessID and PIN, SubID, AccountNumber or GroupSubID)

Users have to attest to these entities and to be able to see the items. What they have attested to is passed into the stored procedure as the @EntityPermissions.

The results will also indicate if the user has already viewed the item before via the ReadStatus table

The stored procedure also has the option filter by 'read' flag which can be in 3 states

• return all items

• only unread items or

• only read items.

The read status table will only contain a row where the item has been read, or 'marked as unread' so I am using an EXISTS and NOT EXISTS to filter by the @Read flag.

We will only return the top number of rows specified in the @RecordLimit parameter, but we need to return the total number of items found. This is being done by a COUNT(*) OVER() as ResultCount, and by loading the results into a temporary table we can select that value and output it as 'TotalCount'

Is there any way to alter this stored procedure to be more efficient? If I could avoid using a temporary table, or the EXIST and NOT EXISTS extra query on the read status table it would improve the execution time. Any advice would be welcomed!

Here is the stored procedure source:

```
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[uspMetaDataSearchItems]
(
@TariffNames varchar(8000),
@Category varchar(8000),
@SearchText varchar(50) = null,
@SupervisorName varchar(50) = null,
@StartDate datetime = null,
@EndDate datetime = null

This is a "kitchen sink" query, for which SQL Server MVP Aaron Bertrand has a good video on how to optimize using dynamic SQL and a detailed post here.

A few points to get you started on the performance of your query:

• Use dynamic SQL to simplify the @Read criteria and subsequent lookup in tblReadStatus. I'm guessing that this is going to be your main performance gain.

• tblMetaData would benefit from having an index on (ItemType, CreatedDTM, ItemID) INCLUDE (TariffName, ItemCategory, SupervisorName, CustomerLastName, ItemExpiryDTM, CustomerMemberID, CustomerFirstName, CustomerLastName, CustomerDateOfBirth). If this table is large, a good index makes a huge performance difference. A clustered index (as opposed to non-clustered), obviously, won't need INCLUDE columns, just the indexed ones.

• If you haven't done so already, put clustered indexes on the key column(s) of your table-type variables (you can define PRIMARY KEY CLUSTERED when you're creating the table type).

• Unique index on tblEntityPermissionLink (ItemID) INCLUDE (BusinessID, PIN, SubID, AccountNumber, GroupSubID)

• Unique index on tblReadStatus (UserID, ItemID) INCLUDE (ReadIND)

• Really consider if those WITH (NOLOCK) are any good to you. This is not neccessarily performance-related, rather a best-practices note because they can cause you a lot of problems with regards to dirty reads.

StackExchange Database Administrators Q#128391, answer score: 2