Database Administrators Stack Exchange
Database Administrators Stack Exchange

Questions tagged [pagination]

Separating results into equally-sized 'pages' typically for display purposes. Also known as 'paging'.

71 questions
19
votes
2 answers

Paging performance with customizable sorting over many millions of rows

In our application we have a grid where users can page over a large number of records (10-20 million). The grid supports sorting in ascending and descending order in a number of columns (20+). Many of the values are also not unique and so the…
Justin
  • 930
  • 1
  • 7
  • 11
19
votes
3 answers

Pagination in SQL Server

I have a very large database, roughly 100 GB. I'm executing query: select * from ; and I want to show only the 100th to 200th rows. I want to understand how this happens internally. Does the database fetch all of the records from disk…
AV94
  • 301
  • 2
  • 6
14
votes
1 answer

Why am I seeing key lookups for all rows read, instead of all rows matching the where clause?

I have a table such as the following: create table [Thing] ( [Id] int constraint [PK_Thing_Id] primary key, [Status] nvarchar(20), [Timestamp] datetime2, [Foo] nvarchar(100) ) with a non-clustered, non-covering index on the Status…
Twicetimes
  • 263
  • 1
  • 5
12
votes
1 answer

Efficient pagination for big tables

Using PostgreSQL 10.5. I'm trying to create a pagination system where the user can go back and forth between various of results. In an attempt to not use OFFSET, I pass the id from the last row in the previous page in a parameter called p (prevId).…
David
  • 123
  • 1
  • 1
  • 6
12
votes
2 answers

MySQL - UUID/created_at cursor based pagination?

For a large dataset, paginating with an OFFSET is known to be slow and not the best way to paginate. A much better way to paginate is with a cursor, which is just a unique identifier on the row so we know where to continue paginating from where we…
Wonka
  • 145
  • 2
  • 2
  • 10
10
votes
2 answers

Windowing offset based on time stamp

I'm writing a query that will be used to page results for a social feed. The concept is the mobile app will request N items, and provide a starting datetime which I have called @CutoffTime below. The purpose of the cutoff time is to establish when…
Erik
  • 4,833
  • 4
  • 28
  • 57
8
votes
3 answers

Most cost efficient way to page through a poorly ordered table?

I have a table that has three columns: HashUID1, HashUID2, Address_Name (which is a textual email address, and the previous two hash colunms are of some crazy creation to link event participant tables to email addresses. its ugly, it barely works…
beeks
  • 1,251
  • 1
  • 8
  • 15
8
votes
1 answer

OFFSET FETCH in SQL Server 2008

I have this query to paginate the results and it was working fine on SQL Server 2012. However I had to move my database to SQL Server 2008 and now my stored procedure is not working. I did some research and got to know that OFFSET does not work in…
Iman
  • 239
  • 1
  • 3
  • 6
7
votes
3 answers

Postgres sometimes uses inferior index for WHERE a IN (...) ORDER BY b LIMIT N

We have a PostgreSQL table with ~5 billion rows that has developed a nasty habit of missing the proper indices and doing a Primary Key scan on certain LIMIT operations. The problem generally manifests on an ORDER BY .. LIMIT .. clause (a common…
Arne Claassen
  • 185
  • 6
7
votes
1 answer

What is the recommended way to join junction tables for efficient ordering/pagination?

Summary: I have a simple database schema but even with just a few 10's of thousands of records the performance on basic queries is already becoming a problem. Database: PostgreSQL 9.6 Simplified schema: CREATE TABLE article ( id bigint PRIMARY…
Jeff Camera
  • 173
  • 1
  • 6
6
votes
1 answer

SQL Server table query with pagination performance tuning, understand the current solution

as stated in the title i start a performance tuning of a table query with pagination generated by a legacy program that use Linq To SQL as ORM. I have found this resource in which is highly recommended to sort table before pagination…
Skary
  • 368
  • 2
  • 11
5
votes
1 answer

Inconsistent keyset pagination when using (timestamp, uuid) fields

I am using the keyset pagination method for uuids on my Postgres database described in this post: How to do Pagination with UUID v4 and Created Time on Concurrent Inserted Data? However, I have noticed when I have two records where the date is the…
Daniel
  • 51
  • 3
5
votes
1 answer

How to do Pagination with UUID v4 and Created Time on Concurrent Inserted Data?

Context: Out of curiosity, I'm doing load testing for my application. And then the result there's a lot of concurrent inserts happened. After doing the load testing on create-endpoint, I'm trying to do load testing on the Fetch endpoint, including…
Iman Tumorang
  • 695
  • 1
  • 6
  • 12
5
votes
1 answer

Reduce query time for higher offset in sql server

Currently, I have table base_voter with data around 100M of dummy data. I have stored procedure as follows: CREATE Procedure [dbo].[spTestingBaseVoter] @SortColumn NVARCHAR(128) = N'name_voter', @SortDirection VARCHAR(4) = 'asc',…
Saroj Shrestha
  • 195
  • 1
  • 1
  • 6
5
votes
1 answer

How to optimize a keyset pagination query with CTEs on a big table?

I tried to document myself as much as I could on the topic before coming here to bother you, but here I am anyway. We want to implement keyset pagination on this table: create table api.subscription ( subscription_id uuid primary key, …
Florian Klein
  • 153
  • 4
1
2 3 4 5