Почему столбцы в моем SELECT не поступают в выходные данные при просмотре плана выполнения?
Я выполняю приведенный ниже запрос, и указанный ниже индекс присутствует для удовлетворения запроса.
SELECT top 100 RCLNT, RBUKRS, RYEAR, RACCT, DRCRK, SUM(ISNULL([000],0.00)) AS CSLVT,SUM(ISNULL([001],0.00)) AS CSL01,SUM(ISNULL([002],0.00)) AS CSL02,SUM(ISNULL([003],0.00)) AS CSL03,SUM(ISNULL([004],0.00)) AS CSL04,SUM(ISNULL([005],0.00)) AS CSL05,SUM(ISNULL([006],0.00)) AS CSL06,SUM(ISNULL([007],0.00)) AS CSL07,SUM(ISNULL([008],0.00)) AS CSL08,SUM(ISNULL([009],0.00)) AS CSL09,SUM(ISNULL([010],0.00)) AS CSL10,SUM(ISNULL([011],0.00)) AS CSL11,SUM(ISNULL([012],0.00)) AS CSL12,SUM(ISNULL([013],0.00)) AS CSL13,SUM(ISNULL([014],0.00)) AS CSL14,SUM(ISNULL([015],0.00)) AS CSL15,SUM(ISNULL([016],0.00)) AS CSL16
FROM dbo.ACDOCA WITH (NOLOCK)
PIVOT
(
MAX(ACDOCA.CSL) FOR ACDOCA.POPER IN ([000],[001],[002],[003],[004],[005],[006],[007],[008],[009],[010],[011],[012],[013],[014],[015],[016])
) AS ACDOC_PIV
GROUP BY RCLNT, RBUKRS, RYEAR, RACCT, DRCRK
Индекс:
CREATE NONCLUSTERED INDEX [IX_ACDOCA_RCLNT_RBUKRS_RYEAR_RACCT_DRCRK_INCL_CSL_POPER] ON [dbo].[ACDOCA]
(
[RCLNT] ASC,
[RBUKRS] ASC,
[RYEAR] ASC,
[RACCT] ASC,
[DRCRK] ASC
)
INCLUDE([CSL],[POPER]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
Но по какой-то причине SQL Server выполняет поиск по кластерному ключу, чтобы получить все остальные столбцы таблицы. Есть ли какая-либо причина, по которой он это делает, и можно ли этого избежать для повышения производительности?
Скриншот плана выполнения:
Можете ли вы увидеть, какие столбцы просматриваются из ПК? Возможно, он использует не IX_ACDOCA_RCLNT_RBUKRS_RYEAR_RACCT_DRCRK_INCL_CSL_POPER, а какой-то другой индекс?
Не по основной теме, но TOP 100 без ORDER BY и NOLOCK оба сомнительны
@MartinSmith Спасибо, ваш ответ сработал! Я добавил TOP 100, потому что таблица содержит миллиард строк и NOLOCK потому что таблица не будет обновляться.
Если таблица не будет обновляться, что вам даст NOLOCK? Разве не является главным преимуществом возможность не блокировать (или быть заблокированным) авторов, с чем в любом случае лучше справляется RCSI?
Ответы 1
Как объясняет Ицик Бен Ган здесь
Конструкция оператора PIVOT требует явного указания элементы агрегации и распространения, но позволяет SQL Server неявно определить группирующий элемент методом исключения. Какие бы [оставшиеся] столбцы ни появлялись в исходной таблице, которая предоставляется в качестве входных данных для PIVOT оператор, они неявно становятся элементом группировки.
Если вы используете табличное выражение, которое проецирует только соответствующие столбцы и PIVOT вместо этого вы избегаете проблемы с нежелательными/неожиданными столбцами, участвующими в неявном GROUP BY. Вам также следует избавиться от явных GROUP BY и SUM, поскольку тогда неявные столбцы GROUP BY в любом случае будут такими же, как ваша явная группировка, поэтому это было необходимо только из-за присутствия этих столбцов, добавляющих дополнительные нежелательные группы.
Итак, вы можете использовать (Fiddle)
WITH PivotSource
AS (SELECT RCLNT,
RBUKRS,
RYEAR,
RACCT,
DRCRK,
CSL,
POPER
FROM ACDOCA)
SELECT TOP 100 RCLNT,
RBUKRS,
RYEAR,
RACCT,
DRCRK,
ISNULL([000], 0.00) AS CSLVT,
ISNULL([001], 0.00) AS CSL01,
ISNULL([002], 0.00) AS CSL02,
ISNULL([003], 0.00) AS CSL03,
ISNULL([004], 0.00) AS CSL04,
ISNULL([005], 0.00) AS CSL05,
ISNULL([006], 0.00) AS CSL06,
ISNULL([007], 0.00) AS CSL07,
ISNULL([008], 0.00) AS CSL08,
ISNULL([009], 0.00) AS CSL09,
ISNULL([010], 0.00) AS CSL10,
ISNULL([011], 0.00) AS CSL11,
ISNULL([012], 0.00) AS CSL12,
ISNULL([013], 0.00) AS CSL13,
ISNULL([014], 0.00) AS CSL14,
ISNULL([015], 0.00) AS CSL15,
ISNULL([016], 0.00) AS CSL16
FROM PivotSource
PIVOT ( MAX(CSL)
FOR POPER IN ([000],[001],[002],[003],[004],[005],[006],[007],[008],[009],[010],[011],[012],[013],[014],[015],[016]) ) AS ACDOC_PIV
--ORDER BY /*Todo: TOP 100 ordered by what?*/
В запросах агрегации, которые включают большинство записей в таблице, разница между сканированием кластеризованного индекса и сканированием некластеризованного индекса может быть незначительной... поскольку в агрегации участвует большинство строк.