UPDATE
I'm trying order by "vnVE0.keywordValueCol0_numeric" using this query:
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 $PARTITION.PF_files_partitioning(F.created) = PN.partition_number ORDER BY (SELECT NULL) 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 $PARTITION.PF_files_partitioning(F.created) = PN.partition_number ORDER BY (SELECT NULL) 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 vnVE0.keywordValueCol0_numeric DESC OFFSET 0 ROWS FETCH FIRST 50 ROWS ONLY; It takes ~9 minutes with the following execution plan: https://www.brentozar.com/pastetheplan/?id=ryJEtn5qG
Also, now the table value_number is partitioned by the next partition function:
CREATE PARTITION FUNCTION PF_value_number (bigint) AS RANGE LEFT FOR VALUES ( 29999999, 59999999, 89999999, 119999999, 149999999, 179999999, 209999999, 239999999 ) And the next partition scheme:
CREATE PARTITION SCHEME PS_value_number AS PARTITION PF_value_number ALL TO ([PRIMARY]); As a result, I have had to update the primary key, and the indexes (dropping it and creating again):
ALTER TABLE value_number ADD CONSTRAINT [PK_value_number] PRIMARY KEY CLUSTERED ( [id] ASC, [id_file] )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON PS_value_number([id_file]) GO Indexes of 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]) Maybe the partitions are too big? This changes have been made by recent requeriments.
