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

[Salakhetdinov Shamil]

 Stuart --

Yes, that could have been CAST function - I missed it - it was a lot of issues involved here during recent days and T-SQL development wasn't the main my concern.

CAST is not needed, I have removed it and my query seems to be working reasonably well now.

Thank you.

-- Shamil


>Thursday, June 16, 2016 1:18 AM +03:00 from "Stuart McLachlan" <stuart at lexacorp.com.pg>:
>
>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
>> 
>
>
>
>_______________________________________________
>dba-SQLServer mailing list
>dba-SQLServer at databaseadvisors.com
>http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
>http://www.databaseadvisors.com
>