Sql App help

[egiggey]

I have an app i'm designing basicaly it's a simple app but i cannot figure out one part I need to display the records in groups of twenty the database has over 5 million records so I have a search feature that could possibly return 100,000 records
anyone know what the best way to deal with this would be ?

[Psyrus]

You could use the Recordset object's PageSize and MaxRecords properties if you are using ADO or in .Net when you use the .Fill method set the maxrecords property to limit the number returned. You could offer the user the option of how many to return(10,50,100 etc) and pass in that value.

Useful links:

http://msdn.microsoft.com/library/de...p/asps3reb.asp

http://msdn.microsoft.com/library/de...maxrecords.asp

http://msdn.microsoft.com/library/de...axrecordsx.asp

http://msdn.microsoft.com/library/de...FillTopic8.asp

[Thelonius]

You could use a stored procedure to run your query, but only return a specifc page of data:

CREATE PROCEDURE [sp_Get_Customers_By_Page]
@CurrentPage int,
@PageSize int,
@TotalRecs int output
AS
--Create a temp table to hold the current page of data
--Add and ID column to count the records
CREATE TABLE #TempTable
(
ID int IDENTITY PRIMARY KEY,
CompanyName nvarchar(40),
ContactName nvarchar (30),
ContactTitle nvarchar (30),
Phone nvarchar (24),
Fax nvarchar (24)
)
--Fill the temp table with the Customers data
INSERT INTO #TempTable (CompanyName, ContactName, ContactTitle, Phone, Fax)
SELECT CompanyName, ContactName, ContactTitle, Phone, Fax FROM Customers
--Create variable to identify the first and last record that should be selected
DECLARE @FirstRec int, @LastRec int
SELECT @FirstRec = (@CurrentPage - 1) * @PageSize
SELECT @LastRec = (@CurrentPage * @PageSize + 1)
--Select one page of data based on the record numbers above
SELECT CompanyName, ContactName, ContactTitle, Phone, Fax
FROM #TempTable
WHERE ID > @FirstRec AND ID < @LastRec
--Return the total number of records available as an output parameter
SELECT @TotalRecs = COUNT(*) FROM Customers