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

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

The point here is, if I do this query:

SELECT "f"."id" AS "FileId" , "f"."name" AS "FileName" , "f"."year" AS "FileYear" , "f"."cid" AS "clientId" , "f"."created" AS "FileDate" , CASE WHEN ("vnVE0"."valor" is not null AND "vnVE0"."valor" != '') THEN CAST("vnVE0"."valor" AS decimal(28,2)) ELSE 0 END AS "keywordValueCol0_numeric" FROM files "f" OUTER APPLY ( SELECT DISTINCT VT.[value] FROM dbo.value_number AS VT WHERE VT.id_file = F.id AND VT.id_field = 260 ) AS V WHERE "grapado" IS NULL AND "masterversion" IS NULL AND ("f"."year" = 2013 OR "f"."year" = 0) AND "f"."cid" = 19 GROUP BY "f"."id", "f"."name", "f"."year", "f"."cid", "f"."created", CASE WHEN ("vnVE0"."valor" is not null AND "vnVE0"."valor" != '') THEN CAST("vnVE0"."valor" AS decimal(28,2)) ELSE 0 END ORDER BY (SELECT NULL) OFFSET 0 ROWS FETCH NEXT 50 ROWS ONLY; 

I get the results in 0 seconds, with the following execution plan: https://www.brentozar.com/pastetheplan/?id=SkV0-FDcG

But If I try to order by "name" the query becomes too slow:

SELECT "f"."id" AS "FileId" , "f"."name" AS "FileName" , "f"."year" AS "FileYear" , "f"."cid" AS "clientId" , "f"."created" AS "FileDate" , CASE WHEN ("vnVE0"."valor" is not null AND "vnVE0"."valor" != '') THEN CAST("vnVE0"."valor" AS decimal(28,2)) ELSE 0 END AS "keywordValueCol0_numeric" FROM files "f" OUTER APPLY ( SELECT DISTINCT VT.[value] FROM dbo.value_number AS VT WHERE VT.id_file = F.id AND VT.id_field = 260 ) AS V WHERE "grapado" IS NULL AND "masterversion" IS NULL AND ("f"."year" = 2013 OR "f"."year" = 0) AND "a"."cid" = 19 GROUP BY "f"."id", "f"."name", "f"."year", "f"."cid", "f"."created", CASE WHEN ("vnVE0"."valor" is not null AND "vnVE0"."valor" != '') THEN CAST("vnVE0"."valor" AS decimal(28,2)) ELSE 0 END ORDER BY "f"."name" OFFSET 0 ROWS FETCH NEXT 50 ROWS ONLY; 

This query takes 11 minutes to return me the results, with the following execution plan: https://www.brentozar.com/pastetheplan/?id=Sk3Fbtv9M

Also, if I change the order by column, the result is the same.

As you can see in the execution plan, the index "archivos_mv" has a cost of 61%, this is the index definition:

CREATE NONCLUSTERED INDEX [archivos_mv] ON [dbo].[archivos] ( [masterversion] ASC, [anyo] ASC, [cat_id] ASC, [cid] ASC, [eid] ASC, [grapado] ASC, [sub_id] ASC, [tip_id] ASC ) INCLUDE ( [id], [nombre]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) GO 

I use SQL Server with Azure. Specifically, Azure SQL Database with pricing/model tier "S4 Estándar (200 DTUs)".

I'm receiving a lot of data but I think the internet connection is not a bottleneck, because in other queries I receive a lot of data too and they're faster.

Additional info

files table:

USE [dd_produccion_test2] GO CREATE TABLE [dbo].[files]( [id] [bigint] IDENTITY(1,1) NOT NULL, [cid] [bigint] NOT NULL, [eid] [bigint] NOT NULL, [cat_id] [bigint] NOT NULL, [tip_id] [bigint] NOT NULL, [sub_id] [bigint] NOT NULL, [anyo] [bigint] NOT NULL, [grapado] [bigint] NULL, [nombre] [nvarchar](255) NOT NULL, [extension] [int] NOT NULL, [size] [bigint] NOT NULL, [id_doc] [bigint] NOT NULL, [created] [datetime2](7) NOT NULL, [masterversion] [int] NULL, CONSTRAINT [PK_archivos_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), 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) ) GO 

value_number table:

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) ) GO ALTER TABLE [dbo].[value_number] WITH NOCHECK ADD CONSTRAINT [FK_valuesN_field] FOREIGN KEY([id_field]) REFERENCES [dbo].[fields] ([id]) ON UPDATE CASCADE ON DELETE CASCADE GO ALTER TABLE [dbo].[value_number] CHECK CONSTRAINT [FK_valuesN_field] GO