[dba-SQLServer] Paging through 1.5 million records in chunks of ~25, 000 records...

[Stuart McLachlan]

Can you handle BIGINT in your front end?  That CAST on 1.5 million rows will probably be 
taking up a large part of the time.   If that is the problem, how about using SPs so that you 
pass the WHERE parameters as INTs, but apply them in the SP directly as BIGINTs



On 15 Jun 2016 at 10:25, Salakhetdinov Shamil wrote:

>  Hi All --
> 
> I have to implement paging in an MS SQL database's one table through
> its ~1.5 million records in chunks of ~25,000 records in four
> views/sort orders both ASC and DESC iow total 8 views. This is a
> desktop application.
> 
> Using
> 
> Cast(ROW_NUMBER() over (ORDER BY [{{SortFieldName}}] ASC) as int)
> RowNumber
> 
> and then applying WHERE clause to the dynamic RowNumber field is
> prohibitively time consuming.   What approach would you propose based
> on your own experience?
> 
> Thank you.
> 
> -- 
>  
> _______________________________________________
> dba-SQLServer mailing list
> dba-SQLServer at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> http://www.databaseadvisors.com
>