[dba-SQLServer] dba-SQLServer Digest, Vol 149, Issue 1

[David Lewis]

I would bring the 1.5 million rows (or better, an appropriately sized subset) to the local computer and do the paging there, rather than ask sql server to handle it.


------------------------------

Message: 5
Date: Wed, 15 Jun 2016 10:25:21 +0300
From: Salakhetdinov Shamil <mcp2004 at mail.ru>
To: Discussion concerning MS SQL Server
<dba-sqlserver at databaseadvisors.com>
Subject: [dba-SQLServer] Paging through 1.5 million records in chunks
of ~25,000 records...
Message-ID: <1465975521.971271613 at f335.i.mail.ru>
Content-Type: text/plain; charset=utf-8

 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.

--
???????????? ??????


________________________________

The contents of this e-mail message and its attachments are covered by the Electronic Communications Privacy Act (18 U.S.C. 2510-2521) and are intended solely for the addressee(s) hereof. If you are not the named recipient, or the employee or agent responsible for delivering the message to the intended recipient, or if this message has been addressed to you in error, you are directed not to read, disclose, reproduce, distribute, disseminate or otherwise use this transmission. If you have received this communication in error, please notify us immediately by return e-mail or by telephone, 530-893-3520, and delete and/or destroy all copies of the message immediately.