I need to fetch and show data on a webpage whose number of records may vary based on filters from around 500 records to 1 million records.
Will caching be of any use here as I think million record in memory is not a good thought. SqldataReader?
Paging is a must to implement of course. Showing 1 million records is a worst case scenario(the stupid All filter in use-cases!).
Should I use connected architecture(SqlDataReader) or disconnected architecture(DataSets)?
First of all, think about it like this: displaying 1 million records makes absolutely no sense to any user. So, you have to think about what the user expects to see. Maybe a summary?! Maybe paginate the records in pages of say 25, or 50 or 100 records. Either of these approaches will not require you to hold 1 M records at a time in memory.
Also, when you run a query against a SQL database and use a SqlDataReader you will not be receiving all the records but instead the SQL driver will send the query to the SQL server, the server will execute the query, prepare a result set and create a forward-only cursor on the server. Then the driver will fetch a record at a time, every time you call Read() on your SqlDataReader. The behavior is very similar if you use LINQ to SQL which uses deferred execution. The result set is not transferred over in full until (or unless) you specifically request each and every row.
So, a simple pagination query will do the trick. Or in other cases some sort of summary report that aggregates the data from those 1 million records one or two pages of relevant data.
Of course if you do need to move back and forth through the pages, some sort of caching might make sense but again, think about it: how often will a user actually want to browse through 1 million records - probably never.
As a last note, if you do implement pagination - make sure that the method you use to implement the pagination relies on the SQL server sending data one page at a time and not reading all 1 million records into ASP.NET and then paginating the local copy of the data because that would be very inefficient and slow. Here is an example of a SQL Server query that performs pagination: SO Question #109232
I concur with the rest of the answerers. displaying 1M records is ludicrous. However, you can display the first X records, and page through.
The trick is in the Stored Procedure doing the fetching
ALTER PROCEDURE [dbo].[MyHugeTable_GetWithPaging]
(
@StartRowIndex int,
@MaximumRows int
)
AS
SET NOCOUNT ON
Select
RowNum,
[UserName]
From
(Select
[ID],
[UserName]
Row_Number() Over(Order By [ID] Desc) As RowNum
From dbo.[MyHugeTable] t)
As DerivedTableName
Where RowNum Between @StartRowIndex And (@StartRowIndex + @MaximumRows)
If your server can't cache 1 million records how do you think your user's web browser is going to handle 1 million records worth of HTML coming at it?
Consider paging (here is an example with 1 million records)
Also consider that the user never wants more than about 30 to 50 records. You are either showing them too low level of detail, or you need more filtering.
I will suggest use dynamic query with paging . so, when you click on specific page fetch records only for those pages. To fetch record from database from specific range use following query.
like this.
Create proc Test
@take smallint,
@skip smallint,
@orderBy nvarchar(20),
@subscriptionid smallint,
as
DECLARE @SQLQuery AS NVARCHAR(max)
SET @SQLQuery=' Select ROW_NUMBER() OVER (ORDER BY P.ProductId desc) as RowNum,* from product"
set @SQLQuery=@SQLQuery + ' and Subscriptionid='+CONVERT(nvarchar, @subscriptionid)
set @SQLQuery= ';WITH Results_CTE AS ( '+@SQLQuery
set @SQLQuery= @SQLQuery +' ) SELECT * FROM Results_CTE WHERE RowNum > '+CONVERT(nvarchar, @skip)+' AND RowNum <= '+CONVERT(nvarchar, @skip+@take) --//paging';
END
EXECUTE sp_executesql @SQLQuery
