¿Búsqueda eficiente de pagination (límite) en SQLServer 2000?

¿Cuál sería la forma más eficiente de hacer una consulta de búsqueda en SQLServer 2000?

Donde una "consulta de búsqueda" sería el equivalente a usar la instrucción LIMIT en MySQL.

EDITAR: ¿Podría un procedimiento almacenado ser más eficiente que cualquier consulta basada en un set en ese caso?

La pagination de grandes sets de resultados y el ganador está utilizando RowCount. También hay una versión generalizada para consultas más complejas. Pero dale crédito a Jasmin Muharemovic 🙂

DECLARE @Sort /* the type of the sorting column */ SET ROWCOUNT @StartRow SELECT @Sort = SortColumn FROM Table ORDER BY SortColumn SET ROWCOUNT @PageSize SELECT ... FROM Table WHERE SortColumn >= @Sort ORDER BY SortColumn 

El artículo contiene el código fuente completo.

Por favor, lea la información "Actualización 2004-05-05". !

Creo que una consulta anidada SELECT TOP n es probablemente la forma más eficiente de lograrlo.

 SELECT TOP ThisPageRecordCount * FROM Table WHERE ID NOT IN (SELECT TOP BeforeThisPageRecordCount ID FROM Table ORDER BY OrderingColumn) ORDER BY OrderingColumn 

Reemplace ThisPageRecordCount con elementos por página y BeforeThisPageRecordCount con (PageNumber - 1) * items-per-page .

Por supuesto, la mejor manera en SQL Server 2005 es usar la function ROW_NUMBER() en un CTE.

La eficiencia de la consulta realmente depende de cómo está estructurada la tabla subyacente. Si, por ejemplo, tiene una key principal llamada ID que es una IDENTIDAD, y es un índice agrupado, y puede suponer que nadie ha estado haciendo IDENTITY_INSERT en ella, puede hacer una consulta como:

SELECCIONE TOP XXX DE LA tabla DONDE ID> @ LastPagesID;

Eso te dará resultados lo más rápido posible. Todo lo demás que va a ser realmente eficiente es una variante de esto, tal vez no sea una identificación, tal vez lo que estás usando para la página es en realidad una date que sabes que es única, pero entiendes el punto … Es probable que las consultas basadas en IN () que se muestran aquí funcionen, pero no afectarán el performance de un escaneo de índice agrupado o agrupado parcial.

Creo que lo que realmente tienes aquí es una razón de peso para actualizar a SQL 2005.

En SQL 2005 esto se puede hacer de forma rápida y fácil con:

 select ROW_NUMBER() over (order by [MyField]) as rowNum, * from [MyTable] where rowNum between @firstRow and @lastRow 

Si realmente está atascado con SQL 2000, me preocuparía: Microsoft no lo va a admitir por completo, dado que ahora está a dos generaciones.

No hay una mejor manera de hacerlo, me temo, todas las soluciones son un poco hacks.

La respuesta de Pedro Petrov es probablemente la más consistente, sin embargo:

  • Si está tratando con un índice agrupado en una tabla más pequeña para su orderamiento, entonces el método ASC-DESC (construyendo dinámicamente dos orderamientos en cada sentido con TOP) probablemente sea más rápido.
  • Si sus datos son relativamente estáticos y su sorting es fija, puede agregar su propio campo rowNum que actualice cuando cambie el order de sorting (suena horrible pero será rápido para tablas grandes).

Creo que estás viendo unas pocas horas ajustando con el analizador de consultas cada vez. Un process almacenado no supondrá mucha diferencia: el almacenamiento en caching del plan de consulta probablemente no sea el cuello de botella.

Este es un procedimiento almacenado genérico de SQL Server 2000 que realizará la pagination en cualquier tabla. El procedimiento almacenado acepta el nombre de la tabla, las columnas a mostrar (por defecto a todas las columnas en la tabla), una condición WHERE opcional, un order de sorting opcional, el número de página a recuperar y el número de filas por página.

  CREATE PROCEDURE [dbo].[GetPage] @pTableName VARCHAR(30), @pColumns VARCHAR(200) = '*', @pFilter VARCHAR(200) = '', @pSort VARCHAR(200) = '', @pPage INT = 1, @pPageRows INT = 10 AS SET NOCOUNT ON DECLARE @vSQL VARCHAR(4000) DECLARE @vTempTable VARCHAR(30) DECLARE @vRowStart INT DECLARE @vTotalRows INT SET @vTempTable = '##Tmp' + CAST(DATEPART(YYYY, GETDATE()) AS VARCHAR(4)) + CAST(DATEPART(MM, GETDATE()) AS VARCHAR(2)) + CAST(DATEPART(DD, GETDATE()) AS VARCHAR(2)) + CAST(DATEPART(HH, GETDATE()) AS VARCHAR(2)) + CAST(DATEPART(MI, GETDATE()) AS VARCHAR(2)) + CAST(DATEPART(SS, GETDATE()) AS VARCHAR(2)) + CAST(DATEPART(MS, GETDATE()) AS VARCHAR(3)) SET @vSQL = 'SELECT ' + @pColumns + ', IDENTITY(INT, 1, 1) AS ROWID INTO ' + @vTempTable + ' FROM ' + @pTableName IF @pFilter != '' AND @pFilter IS NOT NULL SET @vSQL = @vSQL + ' WHERE ' + @pFilter IF @pSort != '' AND @pSort IS NOT NULL SET @vSQL = @vSQL + ' ORDER BY ' + @pSort EXECUTE (@vSQL) -- Get the total number of rows selected SET @vTotalRows = @@ROWCOUNT -- If page number = 0, set it to the first page IF @pPage = 0 SET @pPage = 1 -- If page number is beyond the last page, set page to the last page IF (@pPage * @pPageRows) > @vTotalRows BEGIN SET @pPage = @vTotalRows / @pPageRows IF (@vTotalRows % @pPageRows) != 0 SET @pPage = @pPage + 1 END SET @vRowStart = ((@pPage - 1) * @pPageRows) + 1 SET @vSQL = 'SELECT * FROM ' + @vTempTable + ' WHERE ROWID BETWEEN ' + CAST(@vRowStart AS VARCHAR(10)) + ' AND ' + CAST((@vRowStart + @pPageRows - 1) AS VARCHAR(10)) + ' ORDER BY ROWID' EXECUTE (@vSQL) SET @vSQL = 'DROP TABLE ' + @vTempTable EXECUTE (@vSQL) GO 

Aquí hay algunos ejemplos sobre cómo usarlo usando la database de Northwing:

 EXECUTE [dbo].[GetPage] 'Customers', '*', '', '', 1, 10 EXECUTE [dbo].[GetPage] 'Customers', '*', '', 'CustomerID DESC', 1, 10 

Para confirmar, este no es mi trabajo, pero es cortesía de http://www.eggheadcafe.com/PrintSearchContent.asp?LINKID=1055

Saludos, John