Вполне стандартным решением для этого сервера является помещение во временную таблицу с автоинкрементом, первичного ключа выборки, которую необходимо пронумеровать. А затем, результирующая выборка объединяется с временной таблицей и сортируется в порядке возрастания автоинкремента.
| SET NOCOUNT ONDECLARE @tmp TABLE(ID int IDENTITY, OuterID int)INSERT INTO @tmp (OuterID) SELECT [ID] FROM sysobjects ORDER BY [Name]SELECT T.[ID], SO.* FROM sysobjects SO INNER JOIN @tmp T ON SO.[ID] = T.OuterIDORDER BY T.[ID] |
Здесь можно отделаться более простым запросом, но тоже не совсем тривиальным. Эта СУБД дает некоторый доступ к своей внутренней информации, и внутри у нее записи пронумерованы. Но проблема в том, что сервер нумерует строки для своих нужд до сортировки, поэтому приходится делать вложенный запрос с сортировкой.
| SELECT RowNum, U.* FROM(SELECT * FROM user_tables ORDER BY tablespace_name) U |
Но еще раз хочу напомнить, что если есть возможность избавить сервер от нумерации, значит надо сервер от нее избавлять. В приложении должно быть что-то сильно не так, если требуется подобная функциональность.
Довольно часто, особенно при построении web-сайтов, приходится сталкиваться с задачей постраничного вывода записей. В некоторых СУБД есть специальные параметры для подобного вывода, а в некоторых все приходится делать самостоятельно. Но в любом случае серверу приходится выполнять примерно один и тот же объем работы. Сначала необходимо выбрать все записи, затем отсортировать их, а затем отправить клиенту нужный диапазон. Очевидно, что выдача диапазона без сортировки смысла не имеет, так как СУБД не гарантирует выдачу записей в каком-либо определенном порядке.
Здесь можно придумать довольно много решений и все они будут по-своему хороши.
Во-первых, в данном случае, на удивление эффективным может оказаться применение динамических запросов. При удачном стечении обстоятельств (нужные индексы в нужных местах, и достаточно простые выборки пусть и на большом объеме данных) этот способ является самым быстрым.
| DECLARE @Page int, @PageSize int, @MaxRecord varchar(10), @Count varchar(10)-- номер страницыSET @Page = 10-- размер страницыSET @PageSize = 20SET @MaxRecord = cast((@Page * @PageSize + @PageSize) as varchar(10))SET @Count = cast(@PageSize as varchar(10))EXECUTE ('SELECT * FROM (SELECT TOP ' + @Count + ' * FROM (SELECT TOP ' + @MaxRecord + ' * FROM sysobjects ORDER BY name ASC) SO1 ORDER BY name DESC) SO2ORDER BY name') |
Однако при таком подходе следует быть внимательным, поскольку в случае не оптимально написанного запроса производительность падает довольно резко. Впрочем, на таких объемах, где это будет заметно, к написанию любого запроса надо подходить вдумчиво и аккуратно.
Если же вы испытываете подсознательный страх и неприязнь к динамическим запросам, то есть еще ряд способов. Например, можно сделать практически то же самое, что и приведенный выше запрос, но разложить его по частям, воспользовавшись временной таблицей. Из таблицы (или таблиц), которую нужно отобразить постранично, берется поле, однозначно идентифицирующее каждую запись, и скидывается в нужном порядке во временную табличку с автоинкрементным полем. Причем скидывается не все, а только до последней записи отображаемой страницы. А результирующая выборка делается с объединением с вышеупомянутой временной таблицей, ограничивая диапазон по автоинкрементному полю.
| SET NOCOUNT ONDECLARE @Page int, @PageSize int, @MaxRecord int-- номер страницыSET @Page = 10-- размер страницыSET @PageSize = 20-- созданиевременногохранилищаDECLARE @pg TABLE(RowNum int IDENTITY, OuterID int) -- максимальное количество записей, которое нужно забрать-- из исходной таблицыSET @MaxRecord = @Page*@PageSize + @PageSize-- установка количества записей обрабатываемых запросомSET ROWCOUNT @MaxRecord-- запись отсортированных данных в переменнуюINSERT INTO @pg (OuterID) SELECT ID FROM OriginalTable ORDER BY SortValue ASC-- теперь нужны записи для одной страницыSET ROWCOUNT @PageSize-- вот эти данные уходят на клиентаSELECT O.* FROM OriginalTable O INNER JOIN @pg PON O.ID = P.OuterIDWHERE RowNum > @MaxRecords - @PageSizeORDER BY P.RowNum -- снятие ограничений на количество записей -- обрабатываемое одним запросомSET ROWCOUNT 0 |
Не составляет никаких проблем написать подобную хранимую процедуру, которая разбивала бы на станицы какую угодно комбинацию таблиц подобным образом.
Ознакомившись с этими методами, может возникнуть совершенно законный вопрос - а нельзя ли реализовать все то же самое, но без динамических запросов и без временных таблиц? Точно то же самое нельзя, поскольку ключевое слово TOP не понимает переменных, а жестко зашивать в запрос номер и размер страницы смысла не имеет. Переменные понимает оператор ROWCOUNT, который делает то же самое, что и TOP, но область действия этого оператора распространяется и на подзапросы, что в данном случае не годится, поэтому и приходится использовать временную таблицу.
Но можно использовать курсоры, и с помощью них осуществлять смещение до нужной записи и производить необходимую выборку.
| SET NOCOUNT ONDECLARE @Page int, @PageSize int, @MinRecord int, @MaxRecord int-- номер страницыSET @Page = 10-- размер страницыSET @PageSize = 20SET @MinRecord = @Page*@PageSizeSET @MAXRecord = @Page*@PageSize+@PageSizeSET ROWCOUNT @MaxRecord-- созданиекурсораDECLARE @Cursor CURSORSET @Cursor = CURSOR SCROLL KEYSET READ_ONLY FOR SELECT * FROM OriginalTable ORDER BY SortValueOPEN @Cursor-- смещение к нужной записиFETCH ABSOLUTE @MinRecord FROM @CursorDECLARE @i intSET @i = 0-- выор в цикле нужного количестваWHILE @i < @PageSizeBEGIN FETCH NEXT FROM @Cursor SET @i = @i + 1ENDCLOSE @CursorDEALLOCATE @CursorSET ROWCOUNT 0 |
Этот способ чуть быстрее, чем предыдущий, но обладает тем недостатком, что возвращает не один набор записей, а каждую запись в отдельном наборе.
Можно так же скомбинировать два последних способа и позиционировать с помощью курсора, а затем считывать во временную таблицу нужное количество записей. Получившийся гибрид будет работать довольно быстро, а в некоторых случаях это самый быстрый вариант, и будет наиболее устойчив к различного рода вольностям в запросе.
Что касается относительной скорости всех методов, то тут можно смело использовать то, к чему душа больше лежит, разница составляет не больше нескольких процентов.
Ограничение первой выборки последней записью отображаемой страницы, позволяет довольно сильно повысить скорость выполнения запроса, так как в подавляющем большинстве случаев дальше второй-третьей страницы пользователи не заглядывают. Для очень больших выборок, критичных ко времени обращения к последним страницам, можно придумать хитрую процедуру, которая умела бы различать начальные и конечные страницы, и к последним применять обратную сортировку при выборке. Таким образом, к первым и последним страницам был бы наиболее быстрый доступ.
В Оракле проблема постраничного вывода решается несколько проще. Стандартный способ, подходящий для подавляющего большинства задач выглядит примерно так:
| SELECT * FROM (SELECT A.*, RowNum R FROM (SELECT * FROM user_tables ORDER BY table_name) A WHERE RowNum < :MaxRecord) WHERE R >= :MinRecord |
Однако при большом желании, так же можно придумать хитрую процедуру, которая по-разному обрабатывала бы начальные и конечные станицы большой выборки.
В новой версии Microsoft SQL Server специальных ключевых слов, для подобной функциональности не добавилось, но, тем не менее, постраничную выборку можно немного упростить. В этой версии ключевое слово TOP стало понимать переменные, и даже запросы, и постраничный вывод можно организовать примерно так:
| SELECT * FROM(SELECT TOP (@PageSize) * FROM (SELECT TOP (@Page * @PageSize + @PageSize) * FROM sys.objects ORDER BY name ASC) SO1 ORDER BY name DESC) SO2ORDER BY name |
Скорости это не добавит, так как сервер все равно выполняет примерно одну и ту же работу, но количество нажатий на клавиатуру несколько уменьшится, что, возможно, продлит срок ее службы. Ну и код выглядит компактнее, что конечно приятно.
Упростить постраничный вывод вряд ли возможно. Существуют СУБД, в которых введен специальный синтаксис для вывода данных постранично, но это не более чем syntactic sugar, так как производятся те же действия, что и в примерах выше, просто часть реализации остается за кадром.