Ms sql top: TOP (Transact-SQL)

Содержание

Подробнее о TOP / Хабр

В прошлой статье я писал об особом виде оператора TOP, известного как ROWCOUNT TOP. Теперь рассмотрим несколько других интересных сценариев появления в плане оператора TOP.

В общем случае, ТОР — довольно приземленный оператор. Он просто подсчитывает и возвращает заданное количество строк. SQL Server 2005 включает в себя два усовершенствования этого оператора, которых не было в SQL Server 2000.

Во-первых, в SQL Server 2000 можно указать только константу в виде целого числа возвращаемых строк. В SQL Server 2005 мы можем указать произвольное выражение, включая выражение, содержащее переменные или параметры T-SQL.

Во-вторых, SQL Server 2000 допускает только TOP в операторе SELECT (хотя он поддерживает ROWCOUNT TOP в операторах INSERT, UPDATE и DELETE). SQL Server 2005 допускает TOP с операторами SELECT, INSERT, UPDATE и DELETE.

В этой статье мы сосредоточимся на нескольких простых примерах с оператором SELECT. Для начала создадим небольшую таблицу:

CREATE TABLE T (A INT, B INT)
CREATE CLUSTERED INDEX TA ON T(A)
SET NOCOUNT ON
DECLARE @i INT
SET @i = 0
WHILE @i < 100
  BEGIN
    INSERT T VALUES (@i, @i)
    SET @i = @i + 1
  END
SET NOCOUNT OFF

План простейшего запроса с TOP не нуждается в пояснениях:

SELECT TOP 5 * FROM T

Rows   Executes
5      1        |--Top(TOP EXPRESSION:((5)))
5      1             |--Clustered Index Scan(OBJECT:([tempdb]. [dbo].[T].[TA]))

TOP часто используется в сочетании с ORDER BY. Сочетание TOP с ORDER BY способствует детерминированности выборки. Без ORDER BY выборка зависит от плана запроса и даже может меняться от выполнения к выполнению. Если у нас есть подходящий индекс для поддержки выбранного порядка строк, план запроса останется простым (обратите внимание на ключевые слова ORDERED FORWARD):

SELECT TOP 5 * FROM T ORDER BY A

Rows   Executes
5      1        |--Top(TOP EXPRESSION:((5)))
5      1             |--Clustered Index Scan(OBJECT:([tempdb].[dbo].[T].[TA]), ORDERED FORWARD)

Если подходящего индекса нет, SQL Server вынужден будет добавить в план сортировку:

SELECT TOP 5 * FROM T ORDER BY B

Rows   Executes
5      1        |--Sort(TOP 5, ORDER BY:([tempdb].[dbo].[T].[B] ASC))
100    1             |--Clustered Index Scan(OBJECT:([tempdb].[dbo].[T].[TA]))

Обратите внимание что, если нет подходящего индекса для выборки первых 5 строк SQL Server должен просмотреть все 100 строк таблицы. Также обратите внимание, что сортировка в этом сценарии будет «TOP sort». Такая сортировка обычно использует меньше памяти, чем обычная сортировка, поскольку ей нужно прокрутить через алгоритм сортировки только несколько топовых строк, а не всю таблицу.

Теперь давайте рассмотрим, что произойдет, если мы запросим TOP 5% строк. Чтобы это определить. Для получения результата SQL Server должен подсчитать все строки и вычислить 5%. Это делает запросы, использующие TOP PERCENT, менее эффективными, чем запросы, использующие TOP с абсолютным числом строк.

SELECT TOP 5 PERCENT * FROM T

Rows   Executes
5      1        |--Top(TOP EXPRESSION:((5.000000000000000e+000)) PERCENT)
5      1             |--Table Spool
100    1                 |--Clustered Index Scan(OBJECT:([tempdb].[dbo].[T].[TA]))

Как и в предыдущем примере, SQL Server будет просматривать все 100 строк таблицы. Тут SQL Server использует «жадную» очередь (Eager Spool), которая буферизует и подсчитывает все входные строки, прежде чем что-либо возвращать. Затем TOP запрашивает число строк в очереди, вычисляет 5% и продолжает работу, как любой другой TOP.

Если SQL Server в плане запроса должен выполнять сортировку, этим он также может обеспечить подсчёт затронутых строк. Однако только обычная сортировка умеет подсчитывать их количество. Сортировка «TOP sort» должна знать какое число строк необходимо вернуть с самого начала.

SELECT TOP 5 PERCENT * FROM T ORDER BY B

Rows   Executes
5      1        |--Top(TOP EXPRESSION:((5.000000000000000e+000)) PERCENT)
5      1             |--Sort(ORDER BY:([tempdb].[dbo].[T].[B] ASC))
100    1                  |--Clustered Index Scan(OBJECT:([tempdb].[dbo].[T].[TA]))

TOP WITH TIES также несовместим с «TOP sort». TOP WITH TIES не позволяет узнать наверняка, сколько строк будет получено, пока не будет вычитаны все «привязки». В нашем примере давайте сделаем «привязку» для пятой строки:

INSERT T VALUES (4, 4)
SELECT TOP 5 WITH TIES * FROM T ORDER BY B

Rows   Executes
6      1        |--Top(TOP EXPRESSION:((5)))
7      1             |--Sort(ORDER BY:([tempdb].  [dbo].[T].[B] ASC))
101    1                  |--Clustered Index Scan(OBJECT:([tempdb].[dbo].[T].[TA]))

В этом представлении плана нет TOP WITH TIES, но при SHOWPLAN_ALL или STATISTICS PROFILE можно увидеть следующее: «TIE COLUMNS:([T].[B])». Это также доступно в графическом и XML-планах запроса для SQL Server 2005. Обратите внимание, что TOP теперь возвращает на одну строку больше. Когда TOP N WITH TIES достигает N-й строки, он хранит копию для привязки значения столбца этой строки (в примере B==4) и сравнивает каждую следующую в выборке строку с этим значением. Если есть подходящие строки, он их все вернёт в результате запроса. Поскольку TOP вынужден сравнивать значения всех оставшихся строк, пока не выберет все совпадения для первых N строк, в нашем примере TOP извлечёт из сортировки на одну строку больше, чем было до него.

Наконец, есть пара вырожденных случаев, когда оптимизатор знает, что TOP 0 и TOP 0 PERCENT никогда ничего не возвращают, и заменяет любой такой план запроса на сканирование константы:

SELECT TOP 0 * FROM T

  |--Constant Scan

Оптимизатор также знает, что TOP 100 PERCENT всегда возвращает все строки и удаляет оператор TOP из плана запроса:

SELECT TOP 100 PERCENT * FROM T

  |--Clustered Index Scan(OBJECT:([tempdb]. [dbo].[T].[TA]))

Для этих случаев требуется, чтобы количество строк было постоянным. Например, использование выражения, включающего переменную или параметр T-SQL, приведёт к тому, что план запроса будет такой же, как в общем случае. Оба описанных упрощения плана также работают и с операторами INSERT, UPDATE и DELETE.

Обратите внимание, что не рекомендуется использовать TOP для обхода ограничений языка SQL на использование ORDER BY в подзапросах или представлениях или для принудительного определенных порядка использования операторов в плане запроса.

TOP и LIMIT в SQL

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

Данных в таблицах обычно очень много, но вы часто будете сталкиваться с ситуациями, когда нужны не все данные. Например, если вам нужно получить только 10 лучших сотрудников, недавно пришедших на работу, или получить 3 лучших студента по баллам.

Для таких ситуаций в SQL есть оператор TOP. Но он поддерживается только в SQL Server и MS Access.

Для MySQL суеществует аналог LIMIT. А в Oracle ту же функцию выполняет ROWNUM.

Синтаксис TOP

TOP используется для ограничения количества возвращаемых строк. Вот его синтаксис:

SELECT TOP число | процент список_столбцов FROM имя_таблицы;

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

+--------+--------------+------------+--------+---------+
| emp_id | emp_name     | hire_date  | salary | dept_id |
+--------+--------------+------------+--------+---------+
|      1 | Ethan Hunt   | 2001-05-01 |   5000 |       4 |
|      2 | Tony Montana | 2002-07-15 |   6500 |       1 |
|      3 | Sarah Connor | 2005-10-18 |   8000 |       5 |
|      4 | Rick Deckard | 2007-01-03 |   7200 |       3 |
|      5 | Martin Blank | 2008-06-24 |   5600 |    NULL |
+--------+--------------+------------+--------+---------+

Следующий запрос вернет три самых высокооплачиваемых сотрудника из таблицы

employees:

-- Синтаксис для SQL Server  
SELECT TOP 3 * FROM employees
ORDER BY salary DESC;

+--------+--------------+------------+--------+---------+
| emp_id | emp_name     | hire_date  | salary | dept_id |
+--------+--------------+------------+--------+---------+
|      3 | Sarah Connor | 2005-10-18 |   8000 |       5 |
|      4 | Rick Deckard | 2007-01-03 |   7200 |       3 |
|      2 | Tony Montana | 2002-07-15 |   6500 |       1 |
+--------+--------------+------------+--------+---------+

Вы можете использовать ключевое слово PERCENT после фиксированного значения в TOP, если хотите получить определенный процент строк, а не фиксированное количество строк.

Примечание. Нецелеые значения округляются до следующего целого значения. Например, 1.5 округляется до 2.

Следующий запрос вернет 30% самых высокооплачиваемых сотрудников:

-- Синтаксис для SQL Server  
SELECT TOP 30 PERCENT * FROM employees
ORDER BY salary DESC;

Результат такого запроса:

+--------+--------------+------------+--------+---------+
| emp_id | emp_name     | hire_date  | salary | dept_id |
+--------+--------------+------------+--------+---------+
|      3 | Sarah Connor | 2005-10-18 |   8000 |       5 |
|      4 | Rick Deckard | 2007-01-03 |   7200 |       3 |
+--------+--------------+------------+--------+---------+

Синтаксис LIMIT (для MySQL)

LIMIT в MySQL — это аналог TOP. Вот его синтаксис:

SELECT список_столбцов FROM
  имя_таблицы LIMIT число;

Следующий запрос вернет три самых высокооплачиваемых сотрудника из таблицы employees.

-- Синтаксис для MySQL 
SELECT * FROM employees 
ORDER BY salary DESC LIMIT 3;

Результат запроса:

+--------+--------------+------------+--------+---------+
| emp_id | emp_name     | hire_date  | salary | dept_id |
+--------+--------------+------------+--------+---------+
|      3 | Sarah Connor | 2005-10-18 |   8000 |       5 |
|      4 | Rick Deckard | 2007-01-03 |   7200 |       3 |
|      2 | Tony Montana | 2002-07-15 |   6500 |       1 |
+--------+--------------+------------+--------+---------+

Совет. В операторе SELECT
всегда используйте ORDER BY вместе с LIMIT. В противном случае вы можете не получить желаемого результата.

Смещение строк через LIMIT

У LIMIT есть второй необязательный параметр.

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

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

-- Синтаксис для MySQL 
SELECT * FROM employees 
ORDER BY salary DESC LIMIT 2, 1;

В результате вы получите только одну запись — как и ожидалось:

+--------+--------------+------------+--------+---------+
| emp_id | emp_name     | hire_date  | salary | dept_id |
+--------+--------------+------------+--------+---------+
|      2 | Tony Montana | 2002-07-15 |   6500 |       1 |
+--------+--------------+------------+--------+---------+

sql-сервер — T-SQL SELECT TOP 1

спросил 8 лет, 6 месяцев назад

Изменено 8 лет, 6 месяцев назад

Просмотрено 15 тысяч раз

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

 выбрать TOP 1 u.ID
от [dbo].[Пользователь] u
где u.ID <> '5dc89076-e554-42f2-a9ae-787b20f6f56b' И u.gender != 'мужской'
кроме
выберите [dbo].[Like].likes
от [dbo].[Нравится]
где [dbo].[Like].[user] = '5dc89076-e554-42f2-a9ae-787b20f6f56b'
кроме
выберите [dbo].[Не нравится].не нравится
от [dbo].[Не нравится]
где [dbo].[Не нравится].[пользователь] = '5dc89076-e554-42f2-a9ae-787b20f6f56b'

Этот запрос ничего не возвращает

 выберите u.ID
от [dbo].[Пользователь] u
где u.ID <> '5dc89076-e554-42f2-a9ae-787b20f6f56b' И u.gender != 'мужской'
кроме
выберите [dbo].[Like].likes
от [dbo].[Нравится]
где [dbo].[Like].[user] = '5dc89076-e554-42f2-a9ae-787b20f6f56b'
кроме
выберите [dbo].[Не нравится].не нравится
от [dbo].[Не нравится]
где [dbo].[Не нравится].[пользователь] = '5dc89076-e554-42f2-a9ae-787b20f6f56b'

Этот запрос возвращает идентификатор: 9EF5B83E-319A-4E2F-88A1-E67227DBFDCE

sql
sql-server
tsql

 выберите TOP 1 u. ID
от ...
кроме
...

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

Это не то, что вам нужно. Вы хотите выполнить три запроса, исключить строки и, наконец, получить 1 лучший результат.

Один допустимый синтаксис для этого:

 выберите верхний 1 ID
от (
  выберите u.ID
  от ...
  кроме
  ...
) как q

Есть и другие подходы, которые могут дать тот же результат, но я бы выбрал именно этот.

Или вы можете использовать простой OFFSET 0 ROWS FETCH NEXT 1 ROWS ONLY ключевое слово

Примечание: для этого требуется предложение ORDER BY.

 СОЗДАТЬ ТАБЛИЦУ #test (
    Идентификатор INT,
    Имя varchar(50)
)
ИДТИ
ВСТАВЬТЕ В #test ЗНАЧЕНИЯ
    (1, «Содовая»),
    (2, «Кока-кола»),
    (3, «Пиво»),
    (4, «Вино»)
ИДТИ
ВЫБИРАТЬ *
ОТ #test
ЗАКАЗАТЬ ПО Идентификатору
СМЕЩЕНИЕ 0 СТРОК ТОЛЬКО СЛЕДУЮЩИЙ 1 РЯД
ИДТИ
УДАЛИТЬ ТАБЛИЦУ #test;
ИДТИ

Зарегистрируйтесь или войдите в систему

Зарегистрируйтесь с помощью Google

Зарегистрироваться через Facebook

Зарегистрируйтесь, используя электронную почту и пароль

Опубликовать как гость

Электронная почта

Обязательно, но не отображается

Опубликовать как гость

Электронная почта

Требуется, но не отображается

Нажимая «Опубликовать свой ответ», вы соглашаетесь с нашими условиями обслуживания и подтверждаете, что прочитали и поняли нашу политику конфиденциальности и кодекс поведения.

сервер sql — T-SQL SELECT * быстрый, SELECT TOP 50 медленный

Задавать вопрос

спросил 3 года 11 месяцев назад

Изменено 3 года, 11 месяцев назад

Просмотрено 1к раз

(Удалил предыдущий пост, может не так спросил, попробую еще раз)

 -- 300Ms
    ВЫБЕРИТЕ AppId ИЗ приложения ap
    LEFT OUTER JOIN MissingThings mt on mt.AppId = ap.AppId
    ГДЕ mt.AppId имеет значение NULL
    ЗАКАЗАТЬ ПО mt.Id
-- 1,5 с
ВЫБЕРИТЕ ТОП 50 ИЗ (TheSame)
    -- 100 мс
    ВЫБЕРИТЕ ТОП 50 AppId ИЗ приложения ap
    LEFT OUTER JOIN MissingThings mt on mt.AppId = ap.AppId
    --ГДЕ mt.AppId имеет значение NULL
    ЗАКАЗАТЬ ПО mt. Id

Если я применяю TOP к исходному запросу, он замедляется. Если нет TOP, он быстро возвращает все 1000 записей.

Если я удалю предложение WHERE и получу TOP 50, это снова будет быстро.

Также пытался, поскольку другие сообщения предлагают заменить ГДЕ на НЕ СУЩЕСТВУЕТ. Не помогло.

Существуют ли какие-либо общие возможные причины или это действительно связано с моей базой данных? Если это конкретно, я думаю, я не могу ожидать помощи здесь, потому что мне не разрешено делиться планами выполнения.

РЕДАКТИРОВАТЬ:

В предложении WHERE было 2 других условия, которыми, как мне казалось, действительно можно пренебречь, но как только я прокомментировал их, исходный запрос возвращается через 50 мс!!!!!!

 ВЫБЕРИТЕ ТОП 50 AppId ИЗ приложения ap
        LEFT OUTER JOIN MissingThings mt on mt.AppId = ap.AppId
        ГДЕ mt.AppId имеет значение NULL
И ap.IsOrderFinished = 1
И ap.IsAssigned = 2
        ЗАКАЗАТЬ ПО mt.Id

Добавлен некластеризованный индекс для IsOrderFinished и IsAssigned — нет помощи с обоими или одним из индексов.

Больше результатов:

Быстрое убывание (50 мс!) ORDER BY mt.Id DESC (см. рисунок)

sql-server
tsql

Иногда я не могу заставить оптимально работать полный запрос, но могу сделать оптимальными их части. Затем, вместо того чтобы пытаться оптимизировать полный запрос, я просто перемещаю части во временную таблицу и использую их для построения полного запроса.

Это должно выполняться за 300 мс вместо 1,5 с

 ВЫБЕРИТЕ идентификатор приложения
в #Приложение
ИЗ приложения ap
     LEFT OUTER JOIN MissingThings mt on mt.AppId = ap.AppId
ГДЕ mt.AppId имеет значение NULL
ЗАКАЗАТЬ ПО ap.AppId
ВЫБЕРИТЕ ТОП 50 * ИЗ #Приложения
DROP TABLE #Приложение

Мы также можем немного ускорить его, используя ваш самый быстрый запрос. Это должно выполняться ближе к 100 мс.

 CREATE TABLE #Application (AppId INT, MissingAppId INT)
CREATE CLUSTERED INDEX #IX_Application ON #Application (MissingAppId, AppId)
ВСТАВЬТЕ В #Application (AppId, MissingAppId)
ВЫБЕРИТЕ ap. AppId, mt.AppId
ИЗ приложения ap
     LEFT OUTER JOIN MissingThings mt on mt.AppId = ap.AppId
ВЫБЕРИТЕ ТОП 50 *
ИЗ #Приложение
ГДЕ MissingAppId имеет значение NULL
ЗАКАЗАТЬ ПО AppId
DROP TABLE #Приложение

Зарегистрируйтесь или войдите в систему

Зарегистрируйтесь с помощью Google

Зарегистрироваться через Facebook

Зарегистрируйтесь, используя электронную почту и пароль

Опубликовать как гость

Электронная почта

Требуется, но никогда не отображается

Опубликовать как гость

Электронная почта

Требуется, но не отображается

Штирлиц — Сеть печатных салонов в Перми

Ms sql top: TOP (Transact-SQL) — SQL Server

Подробнее о TOP / Хабр

TOP и LIMIT в SQL

Синтаксис TOP

Синтаксис LIMIT (для MySQL)

Смещение строк через LIMIT

sql-сервер — T-SQL SELECT TOP 1

Зарегистрируйтесь или войдите в систему

Опубликовать как гость

Опубликовать как гость

сервер sql — T-SQL SELECT * быстрый, SELECT TOP 50 медленный

Зарегистрируйтесь или войдите в систему

Опубликовать как гость

Опубликовать как гость

Добавить комментарий Отменить ответ