Запрос SQL Paging (Offset, Fetch) очень медленный
Я не понимаю, что здесь происходит. Я запрашиваю одну таблицу, как показано в моем запросе ниже. Я получаю только первые 20 записей, но выполнение запроса занимает 24 секунды.
Есть ли способ ускорить этот поисковый запрос?
;WITH TempResult AS(
SELECT distinct
D.GLCompany
,D.GLAcct
,D.GLProdNum
,D.GLCostCenter
,D.FCSCompany
,D.FCSAcct
,D.FCSCostCenter
,D.JournalDetailId
,D.[EffDt]
,D.[JournalLineAmt]
,D.[JournalLineDesc]
,D.[ManagedByCd]
,D.[LegalOwnerId]
,D.[JournalLineNum]
,D.[RoundedFlagBit]
,D.[CLPreValErrCd]
,D.[GLPreValErrCd]
,D.[SuspenseErrCd]
,D.GLProfitCenter
,D.GLTradingPartner
,D.GLInternalOrder
,D.GLSubAcct
,D.GLAcctActivity
,D.GLDataSrc
,D.GLId
,D.GLProdGrp
,D.HeaderId
from MyDetail D
)
SELECT * FROM TempResult
ORDER BY TempResult.HeaderId
OFFSET 0 ROWS
FETCH NEXT 20 ROWS ONLY
OPTION(RECOMPILE)
Существует некластеризованный индекс для заголовка, как показано ниже.
CREATE NONCLUSTERED INDEX [FCSAcctJournalDetail_idx] ON [dbo].[MyDetail]
(
[FCSAcct] ASC,
[FCSCompany] ASC,
[JournalEntryEffDt] ASC,
[DataDt] ASC,
[HeaderId] ASC,
[JournalDetailId] 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)
GO
Да, я отключил Distinct, и время сократилось до 9 секунд. Тем не менее это медленно для всего 20 записей
У вас есть индекс по HeaderId? Вам действительно нужен DISTINCT?
MyDetail обычная таблица или представление?
@LukaszSzozda это обычный стол.
А индексы какие?
@Milney Да, для HeaderId есть некластеризованный индекс вместе с несколькими другими столбцами в этом выражении.
Запрос, который вы разместили, не нуждается в CTE.
Кроме того, почему подсказка recompile?
Этот индекс бесполезен в этом запросе. Вам нужно ORDER BY ведущих столбцов в индексе, чтобы избежать полной пересортировки таблицы для получения первых 20 строк.
Технически у вас НЕТ индекса для HeaderID. HeaderID «включен» в индекс, которым вы поделились, но он так далеко в списке, что сортировка только по «header_id» не имеет большого значения. Столбцы, перечисленные в индексе, определяют порядок данных индекса. Если требуется сортировка по HeaderID, я бы добавил индекс NC только для HeaderID. Представьте, что вы сортируете файл Excel с людьми по Last_Name, First_Name и Birth_Date... а затем пытаетесь найти всех людей с датой рождения в июне. Тот факт, что дата рождения включена в сортировку после имен... не поможет.
Сортировка по дате рождения помогает, когда у вас есть кто-то с таким же именем, но совсем не помогает при поиске в наборе данных.
Ответы 1
Добавьте индекс на HeaderId:
CREATE NONCLUSTERED INDEX [FCSAcctJournalDetail_HeaderId_idx] ON [dbo].[MyDetail]
(
[HeaderId] 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)
GO
Как написал Дэвид Браун в своем комментарии, индекс, который у вас есть в настоящее время, не имеет отношения к этому запросу.
Если бы HeaderId был первым столбцом в индексе, он был бы релевантным, но поскольку он не первый (и даже близко не первый), он просто не имеет значения в контексте этого запроса.
1.
distinct.2. КОМПЕНСИРОВАТЬ