I a table with several columns, but the most importants are:
CREATE TABLE [DocumentInfo]( [DocumentId] [bigint] NOT NULL, [StatusId] [int] NOT NULL, [ReceiveDateTime] [datetimeoffset](3) NOT NULL, [IsDeleting] [bit] NOT NULL, [StorageId] [uniqueidentifier] NOT NULL, CONSTRAINT [PK_DocumentInfo] PRIMARY KEY CLUSTERED ( [DocumentId] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY]
I also have a table variable which contains several rows (4 for now):
DECLARE @Tresholds TABLE ( DocumentStatusId int NOT NULL, Treshold datetime2 NOT NULL );
I used indices on DocumentStatusId (is was a primary clustered key), and unique constraint (Treshold, DocumentStatusId), but query didn't use it, so I removed them.
My query looks like:
WITH TopOutdatedDocuments AS ( SELECT TOP (100000) di.DocumentId FROM [DocumentInfo] di WITH (NOLOCK) JOIN @Tresholds t ON di.StatusId = t.DocumentStatusId WHERE (ReceiveDateTime < @DocOldestTreshold OR (ReceiveDateTime < @DocNewestTreshold AND ReceiveDateTime < t.Treshold)) AND NOT EXISTS (SELECT * FROM [doc].[document] d (NOLOCK) WHERE d.ParentId = di.DocumentId) )
I tried to create an index for every permutation of these 3 columns, but it didn't use it anyway.
@DocNewestTreshold are used to speedup a query, becuase they can be computed without a join. But it seems that engine doesn't use this opportunity.
How can I speedup a query? Its logical sense is that we have some tresholds for every
StatusId, and if document is older than its treshold, it should be selected by this query.
Any help will be appreciated. I am now on SQL Server 2014. Order of
[DocumentInfo]'s size is hundred millions of rows, typical size of
@Tresholds is < 10.