Simple condition break down query optimizer and its performance

Question

I have a simple query:

select top 10 * 
FROM Revision2UploadLocations r2l
inner join Revisions r on r2l.RevisionId = r.Id
INNER JOIN [Databases] [D] on [R].[DatabaseId] = [D].[Id]
INNER JOIN [SqlServers] [S] on [D].[InstanceId] = [S].[Id]
where --r.ValidationStatus in (2, 3) and 
r2l.[ChecksumWasSent] = 0 AND r2l.Status = 2

This query is usually executed for 0.5s:

But the same query with uncommented condition is executed for 5s (!!!) and have a very strange execution plan (Revisions and SqlServers are joined although they have no linked columns and the most selective condition "r2l.[ChecksumWasSent] = 0 AND r2l.Status = 2" is executed at the end of query processing:

ValidationStatus is ordinary int not null column. Columns Revision2UploadLocations.RevisionId, Revisions.DatabaseId, Databases.InstanceId are indexed. Here is description of tables:

CREATE TABLE [SqlServers]
(
    [Id] int identity(1,1) NOT NULL CONSTRAINT PK_SqlServers PRIMARY KEY,
...
)

CREATE TABLE [Databases](
    [Id] int identity(1,1) NOT NULL CONSTRAINT PK_Databases PRIMARY KEY,
    [InstanceId] int NOT NULL,
    [Name] nvarchar(128) NOT NULL,
...
    CONSTRAINT FK_Databases_SqlServers FOREIGN KEY ([InstanceId]) REFERENCES [SqlServers]([Id])
)

CREATE INDEX [IX_Databases_DatabaseId] ON [Databases] ([InstanceId] ASC)

CREATE TABLE [Revisions]
(
    [Id] int identity(1, 1) NOT NULL,
    [DatabaseId] int NOT NULL,
    [BackupStatus] tinyint NOT NULL,
    [ValidationStatus] tinyint NOT NULL,
...
    CONSTRAINT PK_Revisions PRIMARY KEY([Id]),
    CONSTRAINT FK_Revisions_Databases FOREIGN KEY ([DatabaseId]) REFERENCES [Databases]([Id])
)

CREATE INDEX [IX_Revisions_DatabaseId] ON [Revisions] ([DatabaseId] ASC)

CREATE TABLE [Revision2UploadLocations]
(
    [Id] int NOT NULL IDENTITY (1, 1) CONSTRAINT PK_Revision2UploadLocations PRIMARY KEY,
    [Status] int NOT NULL,
    RevisionId int NOT NULL,
    [ChecksumWasSent] bit NOT NULL,
    CONSTRAINT FK_r2l_Revisions FOREIGN KEY ([RevisionId]) REFERENCES [Revisions]([Id])
)

CREATE INDEX [IX_Revision2UploadLocations_RevisionId] ON [Revision2UploadLocations] ([RevisionId] ASC)

How I can improve performance of this query?

EDIT Now I have some more details: Some tables (SqlServers and Databases) have 1-10 records, but Revisions and Revision2UploadLocations) have 500K+ records, so query optimize decide to use full scan instead index search for small tables and take it first. Query Performance Tuning (SQL Server Compact):

A small table is one whose contents fit in one or just a few data pages. Avoid indexing very small tables because it is typically more efficient to do a table scan.

As a temprary solution I tried to use query hint FORCE ORDER: Query Hint (SQL Server Compact) and response time decreased from 5sec to 0.5sec.

But I don't think that it's a good solution.

Answer 1

I have found in the past if I insert first to a temp table the first part of your query, with the field you want to further filter on ("ValidationStatus"], then query your temp table the performance/speed is much better. So the initial query would be this:

select * 
into #tmp
FROM Revision2UploadLocations r2l
inner join Revisions r on r2l.RevisionId = r.Id
INNER JOIN [Databases] [D] on [R].[DatabaseId] = [D].[Id]
INNER JOIN [SqlServers] [S] on [D].[InstanceId] = [S].[Id]
where --r.ValidationStatus in (2, 3) and 
r2l.[ChecksumWasSent] = 0 AND r2l.Status = 2

then the final select would be:

select * from #tmp 
where ValidationStatus in (2,3)

No need for indexes, and I know its weird how the optimizer doesn't always work but this approach has been useful to me several times in the past.

Answer 2

The Geoffrey's solution doesn't give you the expected result. The first statement selects 10 rows without garanties that their r.ValidationStatus are 2 or 3. So finaly, you can get less than 10 rows (or even no rows at all). I think you can rewrite you query as this:

SELECT top 10 *     
FROM Revisions r
  INNER JOIN Revision2UploadLocations r2l 
    ON r2l.RevisionId = r.Id
      AND r2l.[ChecksumWasSent] = 0 
      AND r2l.Status = 2
  INNER JOIN [Databases] [D] on [D].[Id] = [R].[DatabaseId]
  INNER JOIN [SqlServers] [S] on [S].[Id] = [D].[InstanceId]
WHERE r.ValidationStatus in (2, 3)

And if r2l.[ChecksumWasSent] datatype is bit (boolean) with :

• more 0 than 1, you can create an index on RevisionId + Status
• very much more 1 than 0, you can create and inde RevisionId + ChecksumWasSent + Status