Performance issue with Top, Order By and Where condition with more than one million rows involved

Question

I have a query which looks something like below:

Declare @CompanyID as int = 10;

Select Top 50  UserID, AuditTypeID , [Action], ActionDate , [Description] , 
RecordID, S.ID as SiteUserID from AuditTrial A

inner Join SiteUser S on S.ID = A.UserID

where A.CompanyID = @CompanyID And A.AuditTypeID <> 1 
And 

UserID in 
(
    1,2,3 -- will be a subquery - will have upto 100+ rows.
)

Order by ID desc -- Latest rows

This is a part of a little larger query with some extra and conditions which I have removed temporarily and even this stripped query runs slower.

Key things are Top 50, Order By ID Latest and a where condition with User ID.

The table contains more than 1 million rows. UserID - can range from 100 to 20000.

• Had Primary key with auto generate

• Indexes on UserID, CompanyID etc

What I have tried so far

When the UserID where condition has less rows it runs good enough < 1 seconds but when the number of users grow it starts taking more time, i.e. greater than 10 seconds.

Thousands of rows are added in this table sometimes with SqlBulkCopy and regular normal inserts are common.

• I had replaced the Subquery to Join - Not much impact
• Put userIDs in temporary table first - again not much of a impact
• Tried some view with DataSchema (googled) - still slow

Not being a DBA - I am not much aware about clustered/Non clustered index.

Any help is appreciated.

Additional details

Apart from Primary key which already creates an Index others are as follows:

For all in columns that will be used in where columns like UserID, CompanyID, PermissionID and so on. I didn't have any non clustered index with multiple columns

CREATE NONCLUSTERED INDEX [IX_AuditTrial_1] ON [dbo].[AuditTrial]
(
[UserID] ASC
)

If I hard code the UserID with 50 values separated by comma in ,In' Clause than the subquery runs instantly.

Full Live Query

declare  @SiteUserID as int = 6484,
 @CompanyID as int = 34;


DECLARE @ColleageUsers TABLE(
ID int
)

Insert into @ColleageUsers
Select SUB.SiteUserID from SiteUserBroker SUB (nolock)
inner join (Select Count(*) as TotalBrokers , SiteUserID from SiteUserBroker group by SiteUserID)Nested on Sub.SiteUserID = Nested.SiteUserID
where BrokerID in (
Select BrokerID from SiteUserBroker (nolock) where SiteUserID = @SiteUserID)
group by SUB.SiteUserID
Having Count(*) - MIN(TotalBrokers) >= 0


Select Top 50  UserID, AuditTypeID , [Action], ActionDate , [Description] , RecordID, ActionedBy =  S.FirstName + ' ' + S.LastName , S.ID as SiteUserID from AuditTrial (nolock) A
inner Join SiteUser (nolock) S on S.ID = A.UserID
where A.CompanyID = @CompanyID And A.AuditTypeID  1 -- 1 Means Audit Type Login
And
(
184 = (Select Distinct Top 1 PermissionID from PermissionGroup (nolock) where GroupID in 
(Select GroupID from SiteUserGroup (nolock) where SiteUserID = @SiteUserID) and PermissionID = 184)
OR
A.AuditTypeID  20   - Not equal to
)
And (A.PermissionID is null Or A.PermissionID in ( Select PermissionID from PermissionGroup (nolock) where GroupID in 
(Select GroupID from SiteUserGroup (nolock) where SiteUserID = @SiteUserID)))
And 
(
( 
UserID in 
(
  Select Id from @ColleageUsers


)
)
)

Order by A.id desc

Query Plan

https://www.brentozar.com/pastetheplan/?id=SkQt9mGvr Plan Link

Answer 1

Below are a few things to try, either separately or altogether.

OPTION(RECOMPILE)

---

Since you are using variables declared in the query text itself, you could use OPTION(RECOMPILE) to recreate the query plan on each execution. This way the optimizer 'sees' the values in the variables at runtime, and you should get a query plan better suited for these values.

Disable row goals

---

Since you have a row goal set due to the TOP() operator, you could try disabling that rowgoal with the traceflag: 4138.

Row goal example in your plan on the concatenation operator:

Disable row goals at the query level with: OPTION(QUERYTRACEON 4138);

More on row goals here

Testing these together with your query:

SELECT TOP 50  UserID, AuditTypeID , [Action], ActionDate , [Description] , RecordID, ActionedBy =  S.FirstName + ' ' + S.LastName , S.ID as SiteUserID 
FROM AuditTrial (nolock) A
INNER JOIN SiteUser (nolock) S on S.ID = A.UserID
WHERE 
A.CompanyID = @CompanyID 
AND A.AuditTypeID <> 1 -- 1 Means Audit Type Login
AND
(
184 = (Select Distinct Top 1 PermissionID from PermissionGroup (nolock) where GroupID in 
(Select GroupID from SiteUserGroup (nolock) where SiteUserID = @SiteUserID) and PermissionID = 184)
OR
A.AuditTypeID <> 20
)
AND (A.PermissionID 
        IS NULL 
     OR  A.PermissionID IN 
                ( SELECT PermissionID 
                    FROM PermissionGroup (nolock) 
                    WHERE GroupID in 
                                    (SELECT GroupID FROM SiteUserGroup (nolock) 
                                     WHERE SiteUserID = @SiteUserID)
                 )
    )
AND 
(
( 
UserID IN 
(
  SELECT Id FROM @ColleageUsers
)
)
)
ORDER BY A.id desc
OPTION(RECOMPILE,QUERYTRACEON 4138 );

Removing the OR's and using UNION ALL

---

Something else, which does need you to rewrite the query, is separating the OR's with UNION ALL's.

This way indexing / operators could be used more efficiently at the cost of processing more data. This depends on the filters & indexes used.

An example for your query:

SELECT TOP 50  UserID, AuditTypeID , [Action], ActionDate , [Description] , RecordID, ActionedBy,SiteUserID 

FROM
( 
SELECT UserID, AuditTypeID , [Action], ActionDate , [Description] , RecordID, ActionedBy =  S.FirstName + ' ' + S.LastName , S.ID as SiteUserID , A.id
FROM AuditTrial (nolock) A
INNER JOIN SiteUser (nolock) S on S.ID = A.UserID
WHERE 
A.CompanyID = @CompanyID 
AND A.AuditTypeID <> 1 -- 1 Means Audit Type Login
AND
(
184 = (Select Distinct Top 1 PermissionID from PermissionGroup (nolock) where GroupID in 
(Select GroupID from SiteUserGroup (nolock) where SiteUserID = @SiteUserID) and PermissionID = 184)
OR
A.AuditTypeID <> 20
)
AND (A.PermissionID 
        IS NULL 
    )
AND 
(
( 
UserID IN 
(
  SELECT Id FROM @ColleageUsers
)
)
)
UNION ALL
SELECT TOP 50  UserID, AuditTypeID , [Action], ActionDate , [Description] , RecordID, ActionedBy =  S.FirstName + ' ' + S.LastName , S.ID as SiteUserID ,A.id
FROM AuditTrial (nolock) A
INNER JOIN SiteUser (nolock) S on S.ID = A.UserID
WHERE 
A.CompanyID = @CompanyID 
AND A.AuditTypeID <> 1 -- 1 Means Audit Type Login
AND
(
184 = (Select Distinct Top 1 PermissionID from PermissionGroup (nolock) where GroupID in 
(Select GroupID from SiteUserGroup (nolock) where SiteUserID = @SiteUserID) and PermissionID = 184)
OR
A.AuditTypeID <> 20
)
 AND  A.PermissionID IN 
                ( SELECT PermissionID 
                    FROM PermissionGroup (nolock) 
                    WHERE GroupID in 
                                    (SELECT GroupID FROM SiteUserGroup (nolock) 
                                     WHERE SiteUserID = @SiteUserID)
                 )

AND 
(
( 
UserID IN 
(
  SELECT Id FROM @ColleageUsers
)
)
)) AS A;

ORDER BY A.id desc
OPTION(RECOMPILE,QUERYTRACEON 4138 );

Table variables / temporary tables

---

Another thing I would try is changing the table variable to a temporary table

CREATE TABLE #ColleageUsers(
ID int
)

To get statistics on the temporary object, since table variables have no statistics but temporary tables do.

Splitting the query up into multiple pieces with the use of temporary tables

---

Another thing to try to get a different execution plan is doing some of the work beforehand, and storing these results into a temporary table. When many tables get touched, it is harder to get correct estimates, splitting up the work makes it easier to calculate the estimates for each part.

An example for your query:

declare  @SiteUserID as int = 6484,
 @CompanyID as int = 34;

SELECT PermissionID 
INTO #TEMP
FROM PermissionGroup (nolock) 
WHERE GroupID in 
(SELECT GroupID FROM SiteUserGroup (nolock) 
 WHERE SiteUserID = @SiteUserID)

DECLARE @PermissionID INT
SELECT DISTINCT Top 1 @PermissionID=PermissionID 
FROM PermissionGroup (nolock) where GroupID in 
(Select GroupID from SiteUserGroup (nolock) 
where SiteUserID = @SiteUserID) and PermissionID = 184


SELECT TOP 50  UserID, AuditTypeID , [Action], ActionDate , [Description] , RecordID, ActionedBy,SiteUserID 

FROM
( 
SELECT UserID, AuditTypeID , [Action], ActionDate , [Description] , RecordID, ActionedBy =  S.FirstName + ' ' + S.LastName , S.ID as SiteUserID , A.id
FROM AuditTrial (nolock) A
INNER JOIN SiteUser (nolock) S on S.ID = A.UserID
WHERE 
A.CompanyID = @CompanyID 
AND A.AuditTypeID <> 1 -- 1 Means Audit Type Login
AND
(
184 = (@PermissionID)
OR
A.AuditTypeID <> 20
)
AND (A.PermissionID 
        IS NULL 
    )
AND 
(
( 
UserID IN 
(
  SELECT Id FROM @ColleageUsers
)
)
)
UNION ALL
SELECT TOP 50  UserID, AuditTypeID , [Action], ActionDate , [Description] , RecordID, ActionedBy =  S.FirstName + ' ' + S.LastName , S.ID as SiteUserID ,A.id
FROM AuditTrial (nolock) A
INNER JOIN SiteUser (nolock) S on S.ID = A.UserID
WHERE 
A.CompanyID = @CompanyID 
AND A.AuditTypeID <> 1 -- 1 Means Audit Type Login
AND
(
184 = (Select Distinct Top 1 PermissionID from PermissionGroup (nolock) where GroupID in 
(Select GroupID from SiteUserGroup (nolock) where SiteUserID = @SiteUserID) and PermissionID = 184)
OR
A.AuditTypeID <> 20
)
 AND  A.PermissionID IN 
                ( SELECT PermissionID 
                FROM #TEMP
                 )

AND 
(
( 
UserID IN 
(
  SELECT Id FROM @ColleageUsers
)
)
)) AS A

ORDER BY A.id desc
OPTION(RECOMPILE,QUERYTRACEON 4138 );