Questions tagged [paging]

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

Filter by
Sorted by
Tagged with
0
votes
1answer
35 views

In Oracle 12c, how can we implement pagination on large table using rowid?

I am new to the database. I would like to implement pagination on large tables using rowid but what I found that rowid can be alphanumeric digits and not necessarily they are in any order so I wonder ...
0
votes
0answers
38 views

Mongo - Find is returning differents results with each request

Currently in my collection has the following spec: Version: MongoDb - 4.0.6 Sharding: 3 shards Shard key: Publication identifier The shards are consistent The problem is due using the same operation ...
1
vote
2answers
39 views

How to get N rows after the first row matching a condition?

I am implementing a cursor based pagination. The requirement is to sort a dataset by an arbitrary condition (e.g. description) and return N rows after X row (where X represents the last item on the ...
1
vote
1answer
49 views

Avoiding code duplication for counting and selecting the same resultset

I have a requirement to return the total number of records and paged data from a stored procedure. The row count should only be computed if a boolean parameter is true. How do I perform the 2 queries (...
2
votes
1answer
294 views

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'...
3
votes
1answer
2k views

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 ...
2
votes
2answers
79 views

Comparison operators give wrong results with keyset pagination

I implemented keyset pagination on these tables CREATE TABLE profiles ( id serial primary key, username varchar(255), followers bigint, tsv tsvector ); CREATE TABLE ...
5
votes
1answer
343 views

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 ...
1
vote
1answer
216 views

Infinite scrolling with full text search in PostgreSQL

I am building a service that allows full-text searches and I would like to paginate the results. I don't want to use OFFSET, so I opted for infinite scrolling with the keyset pagination, as outlined ...
2
votes
2answers
1k views

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 ...
5
votes
1answer
1k views

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 ...
1
vote
1answer
287 views

Oracle pagination with order by field not unique performance

Usually, we will write pagination SQL like this if order by field is unique: SELECT * FROM ( SELECT XX.*, ROWNUM AS RN FROM ( SELECT * FROM T_LOG WHERE OP_TYPE = 'Q' ...
3
votes
1answer
2k views

MSSQL efficient paging including a join on big tables

We have a table with products and a table with a current selection/assortment of products. Now need to join them create an export file by some .net code. Currently using query like this: SELECT * ...
0
votes
1answer
1k views

Cursor Based Pagination for Timestamp field with non-unique values

We are trying to create a query for the following table : CREATE TABLE `Action` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `vendor_Id` int(10) unsigned DEFAULT NULL, `name` varchar(60) ...
1
vote
1answer
58 views

Always return X rows with variable starting row

My goal is to return X rows from a table, where the value of X is some value in the low 100's. For the purposes of testing this query, I am experimenting with different X values. This is a take on ...
4
votes
2answers
107 views

Implicit conversion in fetch

While studying different query plans to improve performance I noticed that FETCH is doing an implicit convert to bigint. Example table and query: CREATE TABLE checkPagintion ( Id INT NOT NULL ...
0
votes
2answers
24 views

Is it possible to keep track of which rows were selected and fetched last in MySQL?

I am working on a small website using Java where the user posts are stored in MySQL, I am getting the posts 5 at a time (5 rows using LIMIT 5) by most recent one (using timestamp DESC) and send it to ...
0
votes
2answers
395 views

SQL Server target/total server memory is not equal to sum of database size in dm_os_buffer_descriptors

I am having an issue in my SQL Server like high memory paging rate and more compilation/sec which lead me to a different observation in memory, so first i checked what is total size of memory of ...
1
vote
1answer
367 views

Paginated SqlServer Query with Changing Data

The traditional way to do server-side pagination of queries with SqlServer (earlier than 2012) has been to use ROW_NUMBER(), as somewhat covered here and in other places. Let's say that I'm building ...
1
vote
2answers
1k views

UPDATE with LIMIT / OFFSET in PL/pgSQL function

In my Postgres 9.2 database I've got the following SELECT, which will be used by a PL/pgSQL function: SELECT id FROM tablea WHERE migrated = false; I want to get 2000 records each ...
0
votes
1answer
201 views

left outer join with order by on foreign key returns duplicates with pagination

I have two tables api_user and api_user with api_user having foreign key to user table. The schemas for the two tables are as listed below. Table "public.api_user" ...
0
votes
2answers
509 views

Oracle - Count groups in group by

I have the following query: SELECT substr("Timestamp", 1, 16) "Timestamp", "Action", count(*) "Count" FROM "MyTable" WHERE substr("Timestamp", 1, 13) <= substr(CURRENT_DATE, 1, 13) GROUP BY ...
2
votes
1answer
2k views

In MySQL, what's the most efficient way to query an entire table in chunks?

If I have a table User is it faster to do something like: SELECT * FROM user WHERE id > ? ORDER BY id LIMIT 10000 (With each subsequent query using the previous last row's id as the param) Or to ...
1
vote
1answer
369 views

Efficient pagination for big amount of data [closed]

I need to be able to quickly (1-2 seconds) retrieve pages of 50,000 records from table that contains ~3,000,000 records. There's a UNIQUE index on a string primary key which contains values like '...
17
votes
3answers
5k views

Pagination in SQL Server

I have a very large database, roughly 100 GB. I'm executing query: select * from <table_name>; and I want to show only the 100th to 200th rows. I want to understand how this happens ...
2
votes
2answers
597 views

Paging through data that is frequently changing

When users are browsing pages of data where the data keeps changing, what is the best way to control this so the user experience is not impacted? If I browse a catalog of 100 records with 10 per page,...
2
votes
2answers
400 views

How to sort the data fetched from horizontal partitioned tables

I have a telco billing software system. In it there are daily logs of users' calls. The logs are horizontally partitioned by date (month). Each partition is stored in a separate database and may be ...
7
votes
1answer
8k views

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 ...
2
votes
1answer
98 views

Paged query against timestamp populated by trigger missing items

We're having a strange problem with a PostgreSQL 9.2 database that I've been racking my brain trying to figure out. First some background. We have a "tombstone" table that is populated using a ...
10
votes
2answers
228 views

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. ...
2
votes
1answer
735 views

HQL pagination returns same result on different pages - with SQL

We have the following query in the server (using sql): Query query = this.entityManager.createQuery( "SELECT type, id, date, amount FROM Transaction ORDER BY type ASC"); The type field is ...
4
votes
1answer
206 views

Pagination - Text comparison with greater than and less than with DESC

I am implementing a seek method for pagination and am wondering about how to best query on a text column with DESC. The queries for this seek approach use a less than or greater than depending on if ...
18
votes
2answers
3k views

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 ...
0
votes
1answer
1k views

Pagination, check number of selected records

I am working on pagination for a complex join query and unfortunately I am not sure what's the best way to go... Right now I am thinking about the following solution. Imagine that SELECT id... is the ...
0
votes
1answer
1k views

Mysql pagination without offset

Let's assume I have got a table with these data: I want to order it by createdAt column DESC, and id column DESC select id, createdAt from post order by createdAt desc, id desc Now it looks like ...
7
votes
3answers
371 views

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 ...
0
votes
0answers
119 views

Paging via Number of Result Record vs COUNT(*)

I have been working on fixing a piece of code for a customer that has been very slow for them. I have made that fix, and thats all lovely, but I noticed something odd in the paging. When the query ...
3
votes
1answer
2k views

Paging in SQL Server: Is an order by clause required? (currently, rows are being missed)

We have some code that pages through a SQL result. (Currently running on SQL Server 2008) We notice that when paging is done, some rows are not returned. Let me clarify this a bit: nHibernate ...
2
votes
1answer
976 views

Optimistic Paging and separate query for COUNT?

I am using SQL Server 2012 FETCH/OFFSET after searching a lot that this will give me the best performance. But for paging to work, I need a total count. There are 2 ways to achieve this: Paging ...
1
vote
1answer
545 views

Can this query be optimized further?

I have 100K records in my Documents table. This is my query which gives a page of 50 records in roughly 750 ms. I would like to further bring it down if possible. I am using Row_Number function to ...
1
vote
1answer
842 views

Paging and grouping using CTE?

Given the following code, from the answer to this question, how can I adapt it for paging? I've read that Common Table Expression is a good way to do paging, but since I implemented CTE to solve a ...
3
votes
1answer
689 views

Paging in SQL Server 2012 vs. previous versions

I have used lot of paging code in SQL Server 2008. The format is With CTE( -- SQL With RowNumber ) -- SELECT ALL Page N This is working fine. But I have heard that SQL Server 2012 has better ...