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