Блог

У меня есть запрос, выполняемый к базе данных SQL Server, выполнение которого занимает более 10 секунд. Запрашиваемая таблица содержит более 14 миллионов строк.

Я хочу отобразить Text столбец из Notes таблицы по заданному ServiceUserId порядку дат. Там могут быть тысячи записей, поэтому я хочу ограничить возвращаемые значения управляемым уровнем.

 SELECT Text
FROM   
    (SELECT    
         ROW_NUMBER() OVER (ORDER BY [DateDone]) AS RowNum, Text
     FROM      
         Notes
     WHERE     
         ServiceUserId = '6D33B91A-1C1D-4C99-998A-4A6B0CC0A6C2') AS RowConstrainedResult
WHERE   
    RowNum >= 40 AND RowNum < 60
ORDER BY 
    RowNum
 

Ниже приведен план выполнения вышеупомянутого запроса.

• Некластеризованный индекс — некластеризованный индекс в столбцах ServiceUserId и DateDone в порядке возрастания.
• Поиск по ключу — первичный ключ для таблицы, которая NoteId

Если я запускаю тот же запрос во второй раз, но с разными номерами строк, я получаю ответ в миллисекундах, я предполагаю, что из кэшированного плана выполнения. Однако выполнение запроса для другого ServiceUserId займет ~ 10 секунд.

Любые предложения о том, как ускорить этот запрос?

Вам следует изучить разбивку на страницы набора ключей.

Это намного эффективнее, чем разбивка на страницы набора строк.

Он принципиально отличается от него тем, что вместо ссылки на конкретный блок номеров строк вы ссылаетесь на начальную точку для поиска ключа индекса.

Причина, по которой это намного быстрее, заключается в том, что вам все равно, сколько строк находится перед определенным ключом, вы просто ищете ключ и двигаетесь вперед (или назад).

Допустим, вы фильтруете по одному ServiceUserId , упорядочивая по DateDone . Вам нужен индекс следующим образом (вы можете не указывать INCLUDE , если он слишком большой, это не сильно меняет математику):

 create index IX_DateDone on Notes (ServiceUserId, DateDone) INCLUDE (TEXT);
 

Теперь, когда вы выбираете некоторые строки, вместо того, чтобы указывать номера начальной и конечной строк, укажите начальный ключ:

 SELECT TOP (20)
    Text,
    DateDone
FROM
    Notes
WHERE     
    ServiceUserId = '6D33B91A-1C1D-4C99-998A-4A6B0CC0A6C2'
    AND DateDone > @startingDate
ORDER BY 
    DateDone;
 

При следующем запуске вы передаете последнее DateDone полученное значение. Это приведет к следующему пакету.

Одним небольшим недостатком является то, что вы не можете переходить по страницам.Однако гораздо реже, чем некоторые могут подумать (с точки зрения пользовательского интерфейса), пользователь хочет перейти на страницу 327. Так что это действительно не имеет значения.

Ключ должен быть уникальным. если он не уникален, вы не можете искать точно следующую строку. Если вам нужно использовать дополнительный столбец, чтобы гарантировать уникальность, это становится немного сложнее:

 WITH NotesFiltered AS
(
    SELECT * FROM Notes
    WHERE     
        ServiceUserId = '6D33B91A-1C1D-4C99-998A-4A6B0CC0A6C2'
)
SELECT TOP (20)
    Text,
    DateDone
FROM (
    SELECT
        Text,
        DateDone,
        0 AS ordering
    FROM NotesFiltered 
    WHERE
        DateDone = @startingDate AND NoteId > @startingNoteId
    UNION ALL
    SELECT
        Text,
        DateDone,
        1 AS ordering
    FROM NotesFiltered 
    WHERE
        DateDone > @startingDate
) n
ORDER BY 
    ordering, DateDone, NoteId;
 

Примечание

В СУБД, поддерживающих сравнение значений строк, многоколоночный пример можно упростить до исходного кода, написав:

 WHERE (DateDone, NoteId) > (@startingDate, @startingNoteId)
 

К сожалению, SQL Server в настоящее время не поддерживает это.
Пожалуйста, проголосуйте за запрос обратной связи Azure для этого

Я бы предложил использовать order by offset fetch : он начинается со строки № x и извлекает следующую строку z, которую можно параметризовать

 SELECT    
   Text
FROM      
   Notes
WHERE     
   ServiceUserId = '6D33B91A-1C1D-4C99-998A-4A6B0CC0A6C2'
Order by DateDone
OFFSET 40 ROWS FETCH NEXT 20 ROWS ONLY
 

также убедитесь, что у вас есть правильный индекс для «DateDone», возможно, включите его в индекс, который у вас уже есть в «Notes», если вы еще этого не сделали

возможно, вам потребуется включить текстовый столбец в свой индекс :

 create index IX_DateDone on Notes(DateDone) INCLUDE (TEXT,ServiceUserId)
 

однако следует отметить, что добавление такого огромного столбца в индекс повлияет на эффективность вставки / обновления и, конечно, для этого потребуется место на диске