Results output patterns

Query Builder has two patterns for the results output:

Paginal mode

The paginal mode in Query Builder splits data into pages and is used for displaying data with a large number of records. It simplifies the overview of data and reduces database load, which in turn brings better performance.

To enable the paginal mode, click the Paginal Mode button.

Paginal mode button

Note

The paginal mode can also be enabled by clicking the corresponding button on a message received when retrieving data from a table that has a large number of records.

To configure the number of records per page, type or select the value from the Page Row Count drop-down list. To navigate through pages, use the corresponding buttons on the document toolbar:

Paginal mode buttons

Note

By default, the paginal mode displays 1000 records per page. You can set the paginal mode by default and specify any preferred number of records to be displayed per page on the Tools menu > Options > Data Editor > General.

If not all records are displayed on one page when the paginal mode is on, a warning icon is shown on the status bar. When you hover over the icon, a notification will be shown, informing that the data is split into pages.

Infinite scrolling

Infinite scrolling output pattern in Query Builder loads data continuously as the user scrolls down the page, eliminating the need for pagination.

All data can be fetched at once by pressing Ctrl+End. If you do so, you may get the following message:

Out of memory

Since all data is fetched to the random access memory gradually, clicking Continue may lead to the Out of Memory error.

Output patterns comparison table

Both paginal mode and infinite scrolling have their advantages and disadvantages:

Paginal mode

Pros Cons
Faster data retrieval Scrolling required for full data view
The paginal mode view can help to speed up data retrieval times since only a limited number of records are displayed on each page. This can be particularly helpful when dealing with large datasets. The paginal mode requires users to navigate through multiple pages to view all table data, making it difficult to get a complete overview of the data at once.
Less load on database server Export limitations
When using the paginal mode, the number of records displayed on each page is limited, which means that the database server is only queried for a subset of the data at a time. When using the paginal mode, data export from the grid is limited to only the data displayed on the current page, which can be a drawback for users who need to export larger datasets.
Lower RAM usage Count limitations
In the paginal mode, only a limited number of records are loaded into memory at any given time, typically the records displayed on the current page and a few surrounding pages. The paginal mode does not show immediate record count, but users can find it by clicking “Go to last page” in paginal mode control located on the Data Editor toolbar.
Lock-free SELECTs Limited sorting options
The paginal mode does not lock tables for DDL (Data Definition Language) operations when executing a SELECT statement. The paginal mode can limit the available sorting options to those that apply only to the currently displayed page rather than allowing for sorting across the entire dataset.
Ease of navigation Limited searching options
By breaking the dataset into smaller, more manageable pieces, the paginal mode can make it easier for users to navigate and find the specific data they need. When searching for specific data in a paginated view, users may need to perform multiple searches on each page rather than searching across the entire dataset.

Infinite scrolling

Pros Cons
Less load on database server Locked SELECTs
When using infinite scrolling, the data is selected as it is scrolled, which means that the database server is only queried for a subset of the data that it currently being viewed. Infinite scrolling locks tables for DDL (Data Definition Language) operations when executing a SELECT statement.
Full data export Increased RAM usage
When using infinite scrolling, the user can export all the data directly from the grid without being limited to the records displayed on the current page. Infinite scrolling requires the local computer to load more data at a given time period, which means a heavier load on RAM.
Better user experience Increased chances of memory overflow
With infinite scrolling, users can continuously scroll through content without needing to manually click to load new pages, which can provide a more seamless experience. Selecting and viewing large amounts of data from an SQL database can cause memory overflow due to the limited capacity of the computer’s memory to handle and store the data.
Reduced clicks Less control
Infinite scrolling can reduce the number of clicks required to navigate through the data, which can save time and increase efficiency. With infinite scrolling users cannot easily navigate to specific parts of the dataset and the scroll position can be easily lost.

Want to Find out More?

Overview

Overview

Take a quick tour to learn all about the key benefits delivered by dbForge Query Builder for SQL Server.
Request a demo

Request a demo

If you consider employing the Query Builder for your business, request a demo to see it in action.
Ready to start using dbForge Query Builder for SQL Server?