Sql-server – Bad performance using “NOT IN”

performancequery-performancesql servert-sql

In my application, I have a query which performs a search in "files" table.

The table files is partitioned by f.created (see the table definition) and has ~100 million rows for the client 19 (f.cid = 19).

I'm using this query from the answer to my previous question, Slow order by SQL Server:

WITH PartitionNumbers AS
(
    -- Each partition of the table
    SELECT P.partition_number
    FROM sys.partitions AS P
    WHERE P.[object_id] = OBJECT_ID(N'dbo.files', N'U')
    AND P.index_id = 1
)
SELECT
    FF.id,
    FF.[name],
    FF.[year],
    FF.cid,
    FF.created,
    vnVE0.keywordValueCol0_numeric
FROM PartitionNumbers AS PN
CROSS APPLY
(
    SELECT
        F100.*
    FROM 
    (
        -- 50 rows in order for year 2013
        SELECT
            F.id,
            F.[name],
            F.[year],
            F.cid,
            F.created
        FROM dbo.files AS F
        WHERE
            F.grapado IS NULL
            AND F.masterversion IS NULL
            AND F.[year] = 2013
            AND F.cid = 19
            AND F.eid = 8
            AND $PARTITION.PF_files_partitioning(F.created) = PN.partition_number
        ORDER BY
            F.[name]
            OFFSET 0 ROWS
            FETCH FIRST 50 ROWS ONLY

        UNION ALL

        -- 50 rows in order for year 0
        SELECT
            F.id,
            F.[name],
            F.[year],
            F.cid,
            F.created
        FROM dbo.files AS F
        WHERE
            F.grapado IS NULL
            AND F.masterversion IS NULL
            AND F.[year] = 0
            AND F.cid = 19
            AND F.eid = 8
            AND $PARTITION.PF_files_partitioning(F.created) = PN.partition_number
        ORDER BY
            F.[name]
            OFFSET 0 ROWS
            FETCH FIRST 50 ROWS ONLY
    ) AS F100
) AS FF
OUTER APPLY
(
    -- Lookup distinct values
    SELECT
        keywordValueCol0_numeric = 
            CASE
                WHEN VN.[value] IS NOT NULL AND VN.[value] <> ''
                THEN CONVERT(decimal(28, 2), VN.[value])
                ELSE CONVERT(decimal(28, 2), 0)
            END
    FROM dbo.value_number AS VN
    WHERE
        VN.id_file = FF.id
        AND VN.id_field = 260
    GROUP BY
        VN.[value]
) AS vnVE0
ORDER BY
    FF.[name]
    OFFSET 0 ROWS
    FETCH FIRST 50 ROWS ONLY;

The point here is this query is performing well, but if I change f.eid = 8 in WHERE to f.eid NOT IN (10,12), the query becomes too slow (more than 10 minutes).

Execution plan using f.eid = 8: https://www.brentozar.com/pastetheplan/?id=HJ_Fbb2qM

Simple Condition

Execution plan using f.eid NOT IN (8,10): https://www.brentozar.com/pastetheplan/?id=B1-zmbnqz

NOT IN

I've tried doing a JOIN with the entidades table, whose id is referenced by eid in the files table.

Execution plan doing a JOIN with entidades: https://www.brentozar.com/pastetheplan/?id=rJarHZh5M

JOIN entidades

How could I improve the performance here?

Additional info

Partition function PF_files_partitioning:

CREATE PARTITION FUNCTION PF_files_partitioning (DATETIME2(7))
AS
RANGE LEFT FOR VALUES ( '2013-03-31 23:59:59', 
                        '2013-06-30 23:59:59',
                        '2013-09-30 23:59:59',
                        '2013-12-31 23:59:59',
                        '2014-03-31 23:59:59', 
                        '2014-06-30 23:59:59',
                        '2014-09-30 23:59:59',
                        '2014-12-31 23:59:59',
                        '2015-03-31 23:59:59', 
                        '2015-06-30 23:59:59',
                        '2015-09-30 23:59:59',
                        '2015-12-31 23:59:59',
                        '2016-03-31 23:59:59', 
                        '2016-06-30 23:59:59',
                        '2016-09-30 23:59:59',
                        '2016-12-31 23:59:59',
                        '2017-03-31 23:59:59', 
                        '2017-06-30 23:59:59',
                        '2017-09-30 23:59:59',
                        '2017-12-31 23:59:59',
                        '2018-03-31 23:59:59')

Partition scheme PS_files_partitioning:

CREATE PARTITION SCHEME PS_files_partitioning AS PARTITION PF_files_partitioning ALL TO ([PRIMARY]);

Note: I will have around 15 million rows in each partition.

Table files:

CREATE TABLE [dbo].[files](
    [id] [bigint] IDENTITY(1,1) NOT NULL,
    [cid] [tinyint] NOT NULL,
    [eid] [bigint] NOT NULL,
    [cat_id] [bigint] NOT NULL,
    [tip_id] [bigint] NULL,
    [sub_id] [bigint] NULL,
    [year] [smallint] NOT NULL,
    [caducidad] [smallint] NULL,
    [grapadopri] [int] NOT NULL,
    [grapado] [bigint] NULL,
    [name] [nvarchar](255) NOT NULL,
    [extension] [tinyint] NOT NULL,
    [size] [bigint] NOT NULL,
    [id_doc] [bit] NOT NULL,
    [observaciones] [nvarchar](255) NOT NULL,
    [indexed] [bit] NOT NULL,
    [signed] [bit] NOT NULL,
    [created] [datetime2](7) NOT NULL,
    [name_lower] [nvarchar](255) NOT NULL,
    [modified] [datetime2](7) NULL,
    [related] [bit] NOT NULL,
    [masterversion] [bigint] NULL,
    [versioned] [bit] NOT NULL,
    [hwsignature] [tinyint] NOT NULL,
    [blockedUserId] [smallint] NULL,
 CONSTRAINT [PK_files_id] PRIMARY KEY CLUSTERED 
(
    [id] ASC,
    [created] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
ON PS_files_partitioning([created]),
 CONSTRAINT [files$estructure_unique] UNIQUE NONCLUSTERED 
(
    [cat_id] ASC,
    [tip_id] ASC,
    [sub_id] ASC,
    [year] ASC,
    [name] ASC,
    [grapado] ASC,
    [created] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)

ALTER TABLE [dbo].[files]  WITH NOCHECK ADD  CONSTRAINT [FK_files_entidad] FOREIGN KEY([eid])
REFERENCES [dbo].[entidades] ([id])
ON UPDATE CASCADE
ON DELETE CASCADE

ALTER TABLE [dbo].[files] CHECK CONSTRAINT [FK_files_entidad]

Table value_number:

CREATE TABLE [dbo].[value_number](
    [id] [bigint] IDENTITY(1,1) NOT NULL,
    [id_file] [bigint] NOT NULL DEFAULT ((0)),
    [id_field] [bigint] NOT NULL DEFAULT ((0)),
    [value] [nvarchar](255) NULL DEFAULT (NULL),
    [id_doc] [bigint] NULL DEFAULT (NULL)
 CONSTRAINT [PK_value_number_id] PRIMARY KEY CLUSTERED 
(
    [id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)

Also, the table value_number is partitioned by this partition function:

CREATE PARTITION FUNCTION PF_value_number (bigint) AS RANGE LEFT 
FOR VALUES (
29999999,
59999999,
89999999,
119999999,
149999999,
179999999,
209999999,
239999999
)

and this partition scheme:

CREATE PARTITION SCHEME PS_value_number 
AS PARTITION PF_value_number
ALL TO ([PRIMARY]);

CREATE TABLE [dbo].[value_number](
    [id] [bigint] IDENTITY(1,1) NOT NULL,
    [id_file] [bigint] NOT NULL DEFAULT ((0)),
    [id_field] [bigint] NOT NULL DEFAULT ((0)),
    [value] [nvarchar](255) NULL DEFAULT (NULL),
    [id_doc] [bigint] NULL DEFAULT (NULL)
 CONSTRAINT [PK_value_number_id] PRIMARY KEY CLUSTERED 
(
    [id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
ON PS_value_number([id_file])
)

Table entidades:

CREATE TABLE [dbo].[entidades](
    [id] [bigint] IDENTITY(1,1) NOT NULL,
    [cid] [bigint] NOT NULL,
    [mid] [bigint] NULL,
    [id_doc] [bigint] NULL,
    [name] [nvarchar](150) NOT NULL,
    [sincro] [tinyint] NOT NULL,
    [comprobar] [tinyint] NOT NULL,
    [op1] [tinyint] NOT NULL,
    [op2] [tinyint] NOT NULL,
    [op3] [tinyint] NOT NULL,
    [index] [tinyint] NOT NULL,
    [can_be_parent] [tinyint] NOT NULL,
    [accounting] [nchar](2) NULL,
    [nota] [nvarchar](60) NULL,
    [alias] [nvarchar](45) NULL,
    [element_type] [tinyint] NOT NULL,
    [size] [bigint] NOT NULL,
 CONSTRAINT [PK_entidades_id] PRIMARY KEY CLUSTERED 
(
    [id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON),
 CONSTRAINT [codigo] UNIQUE NONCLUSTERED 
(
    [cid] ASC,
    [name] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)

ALTER TABLE [dbo].[entidades]  WITH CHECK ADD  CONSTRAINT [FK_entidades_entidad] FOREIGN KEY([mid])
REFERENCES [dbo].[entidades] ([id])
GO

Indexes of the files table:

CREATE NONCLUSTERED INDEX [files_clientes] ON [dbo].[files]
(
    [cid] ASC
)
INCLUDE ([id]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
ON PS_files_partitioning([created])

CREATE NONCLUSTERED INDEX [files_grapado] ON [dbo].[files]
(
    [grapado] ASC
)
INCLUDE (   [id],
    [name]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
ON PS_files_partitioning([created])

CREATE NONCLUSTERED INDEX [files_mv] ON [dbo].[files]
(
    [masterversion] ASC,
    [year] ASC,
    [cat_id] ASC,
    [cid] ASC,
    [eid] ASC,
    [grapado] ASC,
    [sub_id] ASC,
    [tip_id] ASC
)
INCLUDE (   [id],
    [name]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
ON PS_files_partitioning([created])

CREATE NONCLUSTERED INDEX [files_ocr] ON [dbo].[files]
(
    [cid] ASC,
    [grapado] ASC,
    [indexed] ASC,
    [masterversion] ASC,
    [extension] ASC
)
INCLUDE (   [id],
    [eid],
    [cat_id],
    [tip_id],
    [sub_id],
    [year],
    [name]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
ON PS_files_partitioning([created])

CREATE NONCLUSTERED INDEX [files_ocr2] ON [dbo].[files]
(
    [cid] ASC,
    [eid] ASC,
    [grapado] ASC,
    [indexed] ASC,
    [masterversion] ASC,
    [extension] ASC
)
INCLUDE (   [id],
    [cat_id],
    [tip_id],
    [sub_id],
    [year],
    [name]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
ON PS_files_partitioning([created])

CREATE NONCLUSTERED INDEX [files_ocr3] ON [dbo].[files]
(
    [cid] ASC,
    [cat_id] ASC,
    [grapado] ASC,
    [indexed] ASC,
    [masterversion] ASC,
    [extension] ASC
)
INCLUDE (   [eid],
    [tip_id],
    [sub_id],
    [year],
    [name]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
ON PS_files_partitioning([created])

CREATE NONCLUSTERED INDEX [busqueda_name] ON [dbo].[files]
(
    [cid] ASC,
    [eid] ASC,
    [grapado] ASC,
    [year] ASC
)
INCLUDE (   [id],
    [cat_id],
    [tip_id],
    [sub_id],
    [grapadopri],
    [name],
    [size],
    [id_doc],
    [signed],
    [created],
    [modified],
    [related],
    [masterversion]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
ON PS_files_partitioning([created])

CREATE NONCLUSTERED INDEX [busqueda2] ON [dbo].[files]
(
    [cid] ASC,
    [eid] ASC,
    [cat_id] ASC,
    [grapado] ASC,
    [masterversion] ASC,
    [year] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
ON PS_files_partitioning([created])

CREATE NONCLUSTERED INDEX [cid] ON [dbo].[files]
(
    [cid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
ON PS_files_partitioning([created])

CREATE NONCLUSTERED INDEX [eid] ON [dbo].[files]
(
    [eid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
ON PS_files_partitioning([created])

CREATE NONCLUSTERED INDEX [extension] ON [dbo].[files]
(
    [extension] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
ON PS_files_partitioning([created])

CREATE NONCLUSTERED INDEX [FK_files_archivo] ON [dbo].[files]
(
    [grapado] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
ON PS_files_partitioning([created])

CREATE NONCLUSTERED INDEX [FK_files_tipo] ON [dbo].[files]
(
    [tip_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
ON PS_files_partitioning([created])

CREATE NONCLUSTERED INDEX [grapadopri] ON [dbo].[files]
(
    [grapadopri] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
ON PS_files_partitioning([created])

CREATE NONCLUSTERED INDEX [index_all] ON [dbo].[files]
(
    [cid] ASC,
    [eid] ASC,
    [grapado] ASC,
    [masterversion] ASC
)
INCLUDE (   [cat_id],
    [tip_id],
    [sub_id],
    [year],
    [grapadopri],
    [name],
    [size],
    [id_doc],
    [signed],
    [created],
    [modified],
    [related],
    [versioned]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
ON PS_files_partitioning([created])

CREATE NONCLUSTERED INDEX [missing_index_7_6] ON [dbo].[files]
(
    [cid] ASC,
    [eid] ASC,
    [grapado] ASC,
    [name] ASC,
    [year] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) 
ON PS_files_partitioning([created])

CREATE NONCLUSTERED INDEX [ocrCloudClients] ON [dbo].[files]
(
    [grapado] ASC,
    [indexed] ASC,
    [extension] ASC
)
INCLUDE (   [cid],
    [eid],
    [cat_id],
    [tip_id],
    [sub_id]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
ON PS_files_partitioning([created])

CREATE NONCLUSTERED INDEX [searchEntity] ON [dbo].[files]
(
    [cid] ASC,
    [eid] ASC,
    [grapado] ASC,
    [masterversion] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
ON PS_files_partitioning([created])

CREATE NONCLUSTERED INDEX [sub_id] ON [dbo].[files]
(
    [sub_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
ON PS_files_partitioning([created])

CREATE NONCLUSTERED INDEX [IX dbo.files cid, year, eid : grapado IS NULL AND masterversion IS NULL] ON [dbo].[files]
(
    [cid] ASC,
    [year] ASC,
    [eid] ASC
)
INCLUDE (   [grapado],
    [masterversion]) 
WHERE ([grapado] IS NULL AND [masterversion] IS NULL)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
ON PS_files_partitioning([created])

CREATE NONCLUSTERED INDEX [IX dbo.files cid, year, name : grapado IS NULL AND masterversion IS NULL] ON [dbo].[files]
(
    [cid] ASC,
    [year] ASC,
    [name] ASC
)
INCLUDE (   [grapado],
    [masterversion]) 
WHERE ([grapado] IS NULL AND [masterversion] IS NULL)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
ON PS_files_partitioning([created])

CREATE NONCLUSTERED INDEX [IX dbo.files cid, year, eid, name : grapado IS NULL AND masterversion IS NULL] ON [dbo].[files]
    (
        [cid] ASC,
        [year] ASC,
        [eid] ASC,
        [name] ASC
    )
    INCLUDE (   [grapado],
        [masterversion]) 
    WHERE ([grapado] IS NULL AND [masterversion] IS NULL)
    WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
    ON PS_files_partitioning([created])

Indexes of the value_number table:

CREATE NONCLUSTERED INDEX [searchValues] ON [dbo].[value_number]
(
    [id_field] ASC
)
INCLUDE (   [id_file],
    [value]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
ON PS_value_number([id_file])

CREATE NONCLUSTERED INDEX [search] ON [dbo].[value_number]
(
    [id_file] ASC,
    [id_field] ASC
)
INCLUDE (   [value]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
ON PS_value_number([id_file])

CREATE NONCLUSTERED INDEX [id_field] ON [dbo].[value_number]
(
    [id_field] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
ON PS_value_number([id_file])

CREATE NONCLUSTERED INDEX [FK_valueesN_documento] ON [dbo].[value_number]
(
    [id_doc] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
ON PS_value_number([id_file])

CREATE NONCLUSTERED INDEX [FK_valueesN_archivo] ON [dbo].[value_number]
(
    [id_file] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
ON PS_value_number([id_file])

Best Answer

Analysis

Going back over the history of your questions on this site, it seems you are struggling to find a design that allows you to execute a variety of queries with differing filtering and ordering requirements. Not all these filtering requirements can be supported by simple b-tree indexes (e.g. NOT IN).

If you are lucky enough to know in advance exactly which filtering conditions and orders will be needed, and the number of combinations is relatively small, a skilled database index tuner might be able to come up with a reasonable number of nonclustered indexes to meet those needs.

On the other hand, it might be that the number of indexes required would become impractical. There aren't enough details in any of your questions about the filtering/ordering requirements to make that assessment. We also don't know enough about your data relationships. But from the number of overlapping indexes you already have, it seems you are at risk of heading down this path.

Recommendation

As an alternative, I propose you drop the existing nonclustered indexes you have created so far to support your queries, and replace them with a single nonclustered columnstore index. These have recently become available on the Standard tier for Azure SQL Database. Leave the clustered primary key and any constraints or unique indexes you have for key enforcement.

The basic idea is to cover all the columns you filter and order by in the nonclustered columnstore index, and optionally filter the index by any conditions that are always (or very commonly) applied. For example:

CREATE NONCLUSTERED COLUMNSTORE INDEX 
    [NC dbo.files id, name, year, cid, eid, created, grapado, masterversion]
ON dbo.files
    (id, [name], [year], cid, eid, created, grapado, masterversion)
WHERE
    grapado IS NULL
    AND masterversion IS NULL;

This will then enable you to find qualifying rows very quickly, for a very wide range of queries, some of which would be hard or impossible to accommodate with b-tree indexes.

Examples

Start by writing a query to return the primary keys of the files table your query will return, and the order-by column. For example:

SELECT
    -- files table primary key and order by column
    F.id,
    F.created,
    F.[name]
FROM dbo.files AS F
WHERE
    F.grapado IS NULL
    AND F.masterversion IS NULL
    AND F.[year] IN (0, 2013)
    AND F.cid = 19
    AND F.eid NOT IN (10, 12)
ORDER BY
    F.[name] ASC
OFFSET 0 ROWS
FETCH NEXT 50 ROWS ONLY;

You should find that query performs very well, regardless of the filtering or ordering conditions you use. We do not return all the columns we need from the files table at this stage to minimize the size of the data we need to sort.

Now that we have the primary keys for the 50 rows needed, we can expand the query to add the remaining columns, including from any lookup table:

WITH FoundKeys AS
(
    SELECT
        -- files table primary key and order by column
        F.id,
        F.created,
        F.[name]
    FROM dbo.files AS F
        WITH (INDEX([NC dbo.files id, name, year, cid, eid, created, grapado, masterversion]))
    WHERE
        F.grapado IS NULL
        AND F.masterversion IS NULL
        AND F.[year] IN (0, 2013)
        AND F.cid = 19
        AND F.eid NOT IN (10, 12)
    ORDER BY
        F.[name] ASC
    OFFSET 0 ROWS
    FETCH NEXT 50 ROWS ONLY
)
SELECT
    F.id,
    F.[name],
    F.[year],
    F.cid,
    F.eid,
    F.created,
    vnVE0.keywordValueCol0_numeric
FROM FoundKeys AS FK
JOIN dbo.files AS F
    -- join on primary key
    ON F.id = FK.id
    AND F.created = FK.created
OUTER APPLY
(
    -- Lookup distinct values
    SELECT
        keywordValueCol0_numeric = 
            CASE
                WHEN VN.[value] IS NOT NULL AND VN.[value] <> ''
                THEN CONVERT(decimal(28, 2), VN.[value])
                ELSE CONVERT(decimal(28, 2), 0)
            END
    FROM dbo.value_number AS VN
    WHERE
        VN.id_file = F.id
        AND VN.id_field = 260
    GROUP BY
        VN.[value]
) AS vnVE0
ORDER BY
    FK.[name];

This should produce an execution plan like:

estimated plan

I would also encourage you to think again about partitioning these tables. It does not seem necessary to me at all, is over-complicating the queries, and compromising your uniqueness constraints. It should be possible to batch up rows to be inserted and apply them very quickly without blocking concurrent readers, if that is the concern.

If you want to compare the performance of the solution above with a simpler version (that may be slower to sort), try:

SELECT
    F.id,
    F.[name],
    F.[year],
    F.cid,
    F.eid,
    F.created,
    keywordValueCol0_numeric = 
        CASE
            WHEN vnVE0.[value] IS NOT NULL AND vnVE0.[value] <> ''
            THEN CONVERT(decimal(28, 2), vnVE0.[value])
            ELSE CONVERT(decimal(28, 2), 0)
        END
FROM dbo.files AS F
    WITH (INDEX([NC dbo.files id, name, year, cid, eid, created, grapado, masterversion]))
OUTER APPLY
(
    SELECT DISTINCT VN.[value]
    FROM dbo.value_number AS VN
    WHERE VN.id_file = F.id
    AND VN.id_field = 260
) AS vnVE0
WHERE
    F.grapado IS NULL
    AND F.masterversion IS NULL
    AND F.[year] IN (0, 2013)
    AND F.cid = 19
    AND F.eid NOT IN (10, 12)
ORDER BY
    F.[name] ASC
OFFSET 0 ROWS
FETCH NEXT 50 ROWS ONLY;

simpler query

Related Question