Database

DECLARE @Start INT, @Count INT

SET @Start = 10
SET @Count = 5


DECLARE @c TINYINT
SET @c = 1;

WITH paging AS (
	SELECT id, title, description, ROW_NUMBER() OVER (ORDER BY Date DESC) rownum
	FROM News
	WHERE Culture = @c
)
SELECT * FROM paging WHERE rownum BETWEEN @Start AND (@Start + @Count - 1)

CREATE INDEX X_Test ON News (Culture, Date DESC);

SELECT TOP(@Count)
       id, title, description
FROM  News a
WHERE Culture = @c
AND   @Start <= (SELECT COUNT(*)
                 FROM  News b
                 WHERE b.Date < a.Date
                 AND   b.Culture = @c)
ORDER BY Date DESC

CREATE INDEX X_Test ON News (Culture, Date DESC);

DECLARE @Start INT, @Count INT

SET @Start = 1
SET @Count = 5

DECLARE @c TINYINT
SET @c = 1;

WITH paging AS (
	SELECT id, title, [description], ROW_NUMBER() OVER (ORDER BY Date DESC) rownum
	FROM News
	WHERE Culture = @c
)
SELECT * FROM paging WHERE rownum BETWEEN @Start AND (@Start + @Count - 1)

Maysam Mahfouzi wrote:

after I disabled the index which only was on Date column, both queries ran very fast

Maysam Mahfouzi wrote:

But when I set @start to 100,000, the query become a long-running one

Maysam Mahfouzi wrote:

In the real-world running database though, the Date value is greater for greater ID columns for sure.

Mika Wendelius wrote:

And I suspect that this was the way both query versions behaved?

Mika Wendelius wrote:

Is that something we can rely on?

ALTER TABLE News
ADD	StartPoint  AS ((Culture * 100000000) + CONVERT(float, Date))
PERSISTED

CREATE NONCLUSTERED INDEX X_StartPoint ON News (StartPoint)

DECLARE @Start float, @Count INT
SET @Start = 0
SET @Count = 5
DECLARE @c TINYINT
SET @c = 1;
SELECT TOP(@Count)
       id, title, description, StartPoint
FROM  News a
WHERE Culture = @c
AND   StartPoint > @Start
AND   StartPoint < ((@c + 1) * 100000000)
ORDER BY Start

CREATE TABLE News (
[Date]    datetime,
[Id]      int,
[Culture] tinyint
)
--
ALTER TABLE News 
ADD	StartPoint  AS ((Culture * 100000000) + CONVERT(float, [Date]))
PERSISTED

Mika Wendelius wrote:

CREATE TABLE News (
[Date] datetime,
[Id] int,
[Culture] tinyint
)
--
ALTER TABLE News
ADD StartPoint AS ((Culture * 100000000) + CONVERT(float, [Date]))
PERSISTED

ADD StartPoint AS CONVERT(float, [Date])

Maysam Mahfouzi wrote:

when I want to alter the current News table, it tells me that the columns is non-deterministic

Maysam Mahfouzi wrote:

I didn't get the concept of multiplying Culture by 100000000

ROW_NUMBER() OVER(ORDER BY news.[Date] DESC)

CREATE TABLE [News](
	[ID] [bigint] IDENTITY(1,1) NOT NULL,
	[Url] [varchar](300) NULL,
	[Title] [nvarchar](200) NULL,
	[Description] [nvarchar](3000) NULL,
	[Date] [datetime] NULL,
	[Rank] [float] NULL,
	[ViewCount] [int] NULL,
	[Vote] [smallint] NULL,
	[Culture] [tinyint] NULL,
 CONSTRAINT [PK_NewsItem_1] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, 
ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

DECLARE @Start datetime, @Count INT
SET @Start = GETDATE()
SET @Count = 5
DECLARE @c TINYINT
SET @c = 1;
SELECT TOP(@Count)
       id, title, description, Date
FROM  News a
WHERE Culture = @c
AND   Date < @Start
ORDER BY Date DESC

Mika Wendelius wrote:

Do you see problems in that solution?

Maysam Mahfouzi wrote:

Is this just MY problem with pagination, or everybody else is dealing with such problem for large number of rows

Maysam Mahfouzi wrote:

You know my conclusion is that, maybe, pagination has never been meant to be used for large number of rows

Mika Wendelius wrote:

One technical modification could be to use partitioning (but that requires Enterprise Edition) etc.

Maysam Mahfouzi wrote:

I'm sorry to bother you with this long conversation

Maysam Mahfouzi wrote:

The problem with your last solution is that it will not work if we want to give paging to a result set from more than one table

Maysam Mahfouzi wrote:

How can partitioning help

Maysam Mahfouzi wrote:

These activities have been probably collected from different tables in database