12

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 ~26 million rows for the client 19 ("f"."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"."value" is not null AND "vnVE0"."value" != '') THEN CAST("vnVE0"."value" 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 "vnVE0" 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"."value" is not null AND "vnVE0"."value" != '') THEN CAST("vnVE0"."value" 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

Query without ORDER BY

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"."value" is not null AND "vnVE0"."value" != '') THEN CAST("vnVE0"."value" 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 "vnVE0" 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"."value" is not null AND "vnVE0"."value" != '') THEN CAST("vnVE0"."value" 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

Query with ORDER BY

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

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

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) 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.

Also, I'm doing a massive insert of data in this tables, and in a few days I will have up to ~240 million rows in files table (for one cid) and ~480 million rows in value_number table.

Additional info

Partiton 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]);

** I will have around 15 million rows in each partition.

files table:

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) )

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) )

Indexes of 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])

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) 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) 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) 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) 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)

Statistics are up to date. I have changed the data types for year and other columns and now the performance seems to be a little better, but the execution plan is still the same. I'm trying to fix the Cardinality Estimation (changing index), but I hadn't success yet. According to Azure documentation I should have 130 compatibility level in the database and I already have 100 with ProductVersion 12.0.

Improve this question
0

1 Answer 1

Reset to default
10

Analysis

For the query with no order preference, SQL Server can stream grouped rows using a Hash Match Flow Distinct. If it encounters the required number of distinct entries quickly, the execution time is short.

When a specific order is required, SQL Server must test every row. For example, to place rows in name order, it must sort all rows by name. This will be slow if there are a lot of rows, and no index to provide that order without sorting.

There are a number of fundamental complications in your case, most notably the partitioning, and the disjunction on [year]. The partitioning means your indexes cannot deliver the order you might expect. For example an index on name is actually sorted first by partition number, then by name. It cannot deliver rows sorted on name alone.

You also have FORCED PARAMETERIZATION set. This may be beneficial overall, but it comes with impacts you should fully understand. That combined with the partitioning and multi-column indexes means your statistics are largely useless.

The disjunction on year also messes with ordering, and means SQL Server can only seek year >= 0 and year <= 2013 in your plan. This is much less selective than seeking on year = 0 and year = 2013 separately.

Recommendations

So, in light of all the above:

A good index for the ORDER BY name query is:

CREATE INDEX [IX dbo.files cid, year, name : grapado IS NULL AND masterversion IS NULL] ON dbo.files (cid, [year], [name]) INCLUDE (grapado, masterversion) WHERE grapado IS NULL AND masterversion IS NULL;

A better index for the value_number table is:

CREATE INDEX [IX dbo.value_number id_file, id_field, value] ON dbo.value_number (id_file, id_field, [value]);

The query can then be written to fetch at most 50 rows for each year and partition. We then take the first 50 in order from the combined set:

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 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 $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 execution plan will sort at most 100 rows from the files table:

estimated plan

A different index would generally be required for each different ordering.

If you need to count the total number of results, use:

SELECT COUNT_BIG(*) FROM dbo.files AS F 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;

db<>fiddle

Improve this answer
0

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.