Sorry if my question was not clearly formulated. I need to use two conditions in my query, one of which is a date filter to narrow down the datasets. However, this date filter seems to be very time-consuming.
Currently, when the query is executed, the entire dataset is always loaded, which significantly affects performance. My hope was (and still is) that there might be a way to make the query asynchronous so that when selecting a page (e.g., page 2 with 25 records per page), only the records for that specific page are actually loaded — and not all records at once.
I know similar concepts of asynchronous queries from applications like messenger services (WhatsApp etc). There, only a certain number of messages are loaded initially. As you scroll up, additional messages (e.g., 20) are dynamically fetched. This approach is far more efficient than loading the entire conversation history for, let's say a year, all at once.
My hope is to implement a similar logic for tables in Ignition...
To better illustrate this, here is an example of a query structure I got in mind to achieve this...
Calculating the total number of pages:
First, I want to calculate the total number of records based on the filter conditions and determine the number of pages:
-- Calculate the total number of records based on the filter conditions
DECLARE @TotalRecords INT;
SELECT
@TotalRecords = COUNT(*)
FROM [DatabaseXY].[dbo].[Orders]
WHERE
(:Status IS NULL OR :Status = '' OR [Status] = :Status) -- Filter by status
AND (
(:OrderStartDate IS NULL OR :OrderStartDate = '' OR :OrderEndDate IS NULL OR :OrderEndDate = '')
OR [OrderCreationDate] BETWEEN :OrderStartDate AND :OrderEndDate -- Date filter
);
Next, I calculate the total number of pages:
-- Calculate the total number of pages
DECLARE @TotalPages INT;
SET @TotalPages = CEILING(CAST(@TotalRecords AS FLOAT) / :PageSize);
My idea was to use this calculation to display the number of pages in the pager area of the table.
However, I am unsure if these numbers can be manipulated or passed to the table component "pager".
But in case there is a way, I'd proceed with the next step...
Fetching records for the current page:
Here is the query I would use to fetch the records for the current page:
-- Fetch relevant records based on the current page and the number of records per page
SELECT
[ID],
...
[Status],
...
FROM [DatabaseXY].[dbo].[Orders]
WHERE
(:Status IS NULL OR :Status = '' OR [Status] = :Status) -- Filter by status
AND (
(:OrderStartDate IS NULL OR :OrderStartDate = '' OR :OrderEndDate IS NULL OR :OrderEndDate = '')
OR [OrderCreationDate] BETWEEN :OrderStartDate AND :OrderEndDate -- Date filter
)
ORDER BY [ID]
OFFSET (:CurrentPage - 1) * :PageSize ROWS
FETCH NEXT :PageSize ROWS ONLY;
Passing the initialOption value
on to the
:PageSize parameter works as expected!
But I haven’t found a way to pass the selected page number, like "2"
to the :CurrentPage parameter.
By finding a way to pass the information about which page is selected to the Parameter :CurrentPage, I believe this could be an effective approach to reducing the SQL server's load.
Once again, I apologize if I’m causing more confusion than clarity. However, if anyone understands what I’m trying to achieve and has suggestions for improvement or a best practice approach, I would be truly grateful.
