The DB Paginator?
A few years back, I had a client in the banking industry with a major performance issue in their Oracle Database when opening an account’s transaction screen. The problem was that while some accounts would have a moderate number of daily transactions, some could have millions per day.
The UI had a screen that would list the transactions, ordering by the transaction date/time and display 50 at a time. Just imagine the query here.. Select …. from transactions where accountNum = ? order by transDate limit 50. And then when the results would finally render on the page, a user would click “next”…
The easy (and correct) answer would be to call this a “design” issue. What good is a screen that list millions of transactions in a UI? Why would a user even want that? I agree! But, in our world, we sometimes don’t have a choice in the matter. We need to just make the screen work.
I ended up creating a POC stored procedure to improve performance when querying and sorting millions of rows by date. It addressed the need to retrieve the top N transactions over a large date range, where sorting is the bottleneck. The procedure splits the date range into smaller chunks, returns results via a cursor, and tracks progress to support stateless applications and pagination across subsequent calls. The screen would render results faster because now we have an additional condition on the transaction date with a range. In cases with millions of transactions, sometimes the range would be in minutes (or seconds!). There was an appropriate index already in place.
I think most of the logic in the procedure should be in the application code but I ended up just banging this out so that it would aid in clarifying the idea to the dev team.
Enjoy the madness!
Parameters:
t_reportname varchar2: -- this can be a table name or a view
t_collist VARCHAR2: -- list of columns in the table or view that you want returned
t_reportconditions varchar2: -- additional conditions to pass besides the date condition in t_datecolname, t_dtfrom and t_dtto below
t_pkcolname varchar2: -- name of the primary key column
t_datecolname varchar2: -- name of the date column that you are passing a condition on and are sorting by
t_sessionid IN VARCHAR2: -- identifier for the session executing the procedure
t_numrows IN NUMBER: -- number of rows to return
t_dtfrom IN DATE: -- from date to pass as a condition on t_datecolname
t_dtto IN DATE: -- to date to pass as a condition on t_datecolname
t_pageno_in IN NUMBER: -- OPTIONAL: if NULL runs for the next page to be generated. If you pass a value, it will look for a previously generated page in report_scroll and return those results.
t_sort IN VARCHAR2: -- ASC or DESC
t_hasmore OUT CHAR: -- return to the application 'Y' or 'N' as an indicator if there are more records
t_rec_output OUT SYS_REFCURSOR: -- the return results
Tables:
report_settings: this can be used to give a hint for the time interval to use for a give table/view query.
In the procedure, a very simplistic method is used to determine what interval to use. If you have an optimal interval to use, set it in the dayinterval column for the "reportname". Make sure to set "forcesetting" to 'Y'.report_instance: This is used to log the instance of someone running the report.
report_scroll: An entry exists for each page generated for a given report_instance.
Sample Call from SQL Developer:
DECLARE
T_REPORTNAME VARCHAR2(200);
T_COLLIST VARCHAR2(200);
T_REPORTCONDITIONS VARCHAR2(200);
T_PKCOLNAME VARCHAR2(200);
T_DATECOLNAME VARCHAR2(200);
T_SESSIONID VARCHAR2(200);
T_NUMROWS NUMBER;
T_DTFROM DATE;
T_DTTO DATE;
T_PAGENO_IN NUMBER;
T_SORT VARCHAR2(200);
T_HASMORE CHAR(200);
T_REC_OUTPUT SYS_REFCURSOR;
BEGIN
T_REPORTNAME := 'BIG_TRANSACTION_VIEW';
T_COLLIST := 'COLUMN_1, COLUMN_2, COLUMN_3, COLUMN_4';
T_REPORTCONDITIONS := 'ACC_ID =1934863';
T_PKCOLNAME := 'PRIMARYKEY_ID';
T_DATECOLNAME := 'DATECOLUMN';
T_SESSIONID := '3';
T_NUMROWS := 15;
T_DTFROM := to_date('2021-07-12','yyyy-MM-dd');
T_DTTO := to_date('2021-10-12','yyyy-MM-dd');
T_PAGENO_IN := NULL;
T_SORT := 'DESC';
ACCT15M.REPORT_PAGINATOR(
T_REPORTNAME => T_REPORTNAME,
T_COLLIST => T_COLLIST,
T_REPORTCONDITIONS => T_REPORTCONDITIONS,
T_PKCOLNAME => T_PKCOLNAME,
T_DATECOLNAME => T_DATECOLNAME,
T_SESSIONID => T_SESSIONID,
T_NUMROWS => T_NUMROWS,
T_DTFROM => T_DTFROM,
T_DTTO => T_DTTO,
T_PAGENO_IN => T_PAGENO_IN,
T_SORT => T_SORT,
T_HASMORE => T_HASMORE,
T_REC_OUTPUT => T_REC_OUTPUT
);
/* Legacy output:
DBMS_OUTPUT.PUT_LINE('T_HASMORE = ' || T_HASMORE);
*/
:T_HASMORE := T_HASMORE;
/* Legacy output:
DBMS_OUTPUT.PUT_LINE('T_REC_OUTPUT = ' || T_REC_OUTPUT);
*/
:T_REC_OUTPUT := T_REC_OUTPUT; --<-- Cursor
--rollback;
END;--table for logging/resuming report
create table report_settings (
reportid number GENERATED ALWAYS as IDENTITY(START with 1 INCREMENT by 1),
reportname varchar2(128),
dayinterval number,
modifiedon date default sysdate,
forcesetting char(1) default 'N',
primary key (reportid)
);
create table report_instance (
reportinstanceid number GENERATED ALWAYS as IDENTITY(START with 1 INCREMENT by 1),,
reportid number,
sessionid varchar2(128),
reportname varchar2(128),
reportconditions varchar2(2000),
columnlist varchar2(1000),
numrows number,
numrecsinrpt number,
pageno number,
datefrom date,
dateto date,
pagedatefrom date,
pagedateto date,
pkid number,
dayinterval number,
sort varchar2(4);
createdon date default sysdate,
primary key (reportinstanceid)
);
--table for logging/resuming report
create table report_scroll (
reportscrollid number GENERATED ALWAYS as IDENTITY(START with 1 INCREMENT by 1),
reportinstanceid number,
sessionid varchar2(128),
reportname varchar2(128),
pageno number,
numrows number,
datefrom date,
dateto date,
pagedatefrom date,
pagedateto date,
pkid number,
dayinterval number,
createdon date default sysdate,
primary key (reportscrollid)
);
--create index report_scroll_check on report_scroll(upper(sessionid), upper(reportname));
create or replace procedure REPORT_PAGINATOR(t_reportname varchar2, t_collist VARCHAR2, t_reportconditions varchar2, t_pkcolname varchar2, t_datecolname varchar2, t_sessionid IN VARCHAR2, t_numrows IN NUMBER, t_dtfrom IN DATE, t_dtto IN DATE, t_pageno_in IN NUMBER, t_sort IN VARCHAR2, t_hasmore OUT CHAR, t_rec_output OUT SYS_REFCURSOR)
AS
t_check NUMBER;
t_checkrpt NUMBER;
t_checkforce NUMBER;
t_checkpagecount NUMBER;
t_lastrunto DATE;
t_newrunfrom DATE;
t_newrunto DATE;
t_pagerunto DATE;
t_pagerunfrom DATE;
t_pageno NUMBER;
t_dayinterval NUMBER;
t_rptdayrange NUMBER;
t_numrecsinrpt NUMBER;
t_maxid NUMBER;
t_pkid NUMBER;
t_newpkid NUMBER;
t_rec_check_sqlstmt VARCHAR2(2000);
t_get_param_sqlstmt VARCHAR2(2000);
t_sqlstmt VARCHAR2(2000);
t_rundate DATE := SYS_EXTRACT_UTC(SYSTIMESTAMP);
t_reportinstanceid NUMBER;
BEGIN
if t_pageno_in is null then -- not lookng for a prebuilt page
select count(*) into t_checkforce from report_settings where upper(reportname) = upper(t_reportname) and forcesetting = 'Y';
if t_checkforce = 1 then
select dayinterval into t_dayinterval from report_settings where upper(reportname) = upper(t_reportname) and forcesetting = 'Y' order by modifiedon desc fetch first 1 rows only;
--could add logic here to select a recent interval if one exists to avoid extra work below
end if;
--need to find out if this report has been run in the last hour
select nvl(max(reportinstanceid), 0) into t_reportinstanceid from report_instance where upper(sessionid) = upper(t_sessionid) and upper(reportname) = upper(t_reportname) and upper(columnlist) = upper(t_collist) and upper(reportconditions) = upper(t_reportconditions) and upper(sort) = upper(t_sort) and datefrom = t_dtfrom and dateto = t_dtto and createdon > sysdate - (1/24);
if t_reportinstanceid = 0 then --this is the first run
t_newrunfrom := t_dtfrom;
t_newrunto := t_dtto;
t_pkid := 0;
t_pageno := 1;
--prepare a statement to check for total records in report
t_rec_check_sqlstmt := 'select count(*), max('||t_pkcolname||') + 1 from '||t_reportname||' where '||t_reportconditions||' and '||t_datecolname||' >= :2 and '||t_datecolname||' <= :3 ';
execute immediate t_rec_check_sqlstmt into t_numrecsinrpt, t_maxid using t_dtfrom, t_dtto;
if t_numrecsinrpt > 0 then --only go through this if there is data
if upper(t_sort) = 'DESC' then --need to go in reverse order
t_pkid := t_maxid;
end if;
if t_dayinterval is null then
--find a suitable day interval
t_rptdayrange := round(t_dtto - t_dtfrom);
if t_rptdayrange > 7 then --process in batches
if t_numrecsinrpt < 10000 then --if total count < 10000
t_dayinterval := t_dtto - t_dtfrom; --pass the whole range
elsif t_numrecsinrpt/t_rptdayrange <= t_numrows and t_numrecsinrpt > 10000 then --if daily average is less than number to display per page and total count > 10000
t_dayinterval := 7;
else --it's greater than 10000 and daily average is greater than number to display per page
t_dayinterval := 1;
end if;
else
t_dayinterval := t_dtto - t_dtfrom; --pass the whole range
end if;
if upper(t_sort) = 'ASC' then
t_newrunto := t_newrunfrom + t_dayinterval;
else
t_newrunfrom := t_newrunto - t_dayinterval; -- descending order by default
end if;
else
if upper(t_sort) = 'ASC' then
t_newrunto := t_newrunfrom + t_dayinterval; --use predefined interval
else
t_newrunfrom := t_newrunto - t_dayinterval; -- descending order by default
end if;
end if;
end if;
else
-- get the last page generated for the report
-- get values from the last run to pass to the ref cursor
if upper(t_sort) = 'ASC' then
select pageno, pagedateto, pkid, dayinterval, numrecsinrpt into t_pageno, t_newrunfrom, t_pkid, t_dayinterval, t_numrecsinrpt from report_instance where reportinstanceid = t_reportinstanceid order by createdon desc fetch first 1 rows only;
t_pageno := t_pageno + 1; --adding one here for insert below
t_newrunto := t_newrunfrom + t_dayinterval;
else
select pageno, pagedatefrom, pkid, dayinterval, numrecsinrpt into t_pageno, t_newrunto, t_pkid, t_dayinterval, t_numrecsinrpt from report_instance where reportinstanceid = t_reportinstanceid order by createdon desc fetch first 1 rows only;
t_pageno := t_pageno + 1; --adding one here for insert below
t_newrunfrom := t_newrunto - t_dayinterval;
end if;
end if;
--make sure we don't go passed date range
if t_newrunto > t_dtto then
t_newrunto := t_dtto;
end if;
if t_numrecsinrpt > 0 then --only go through this if there is data
--make sure we are getting the specified number of rows to display per page
t_checkpagecount := 0;
--prepare a statement to check for total records in page
if upper(t_sort) = 'ASC' then
t_rec_check_sqlstmt := 'select count(*) from '||t_reportname||' where '||t_reportconditions||' and (('||t_datecolname||' > :2 and '||t_datecolname||' <= :3) or ('||t_datecolname||' = :4 and '||t_pkcolname||' > :5))';
execute immediate t_rec_check_sqlstmt into t_checkpagecount using t_newrunfrom , t_newrunto, t_newrunfrom, t_pkid;
else
t_rec_check_sqlstmt := 'select count(*) from '||t_reportname||' where '||t_reportconditions||' and (('||t_datecolname||' >= :2 and '||t_datecolname||' < :3) or ('||t_datecolname||' = :4 and '||t_pkcolname||' < :5))';
execute immediate t_rec_check_sqlstmt into t_checkpagecount using t_newrunfrom , t_newrunto, t_newrunto, t_pkid;
end if;
if upper(t_sort) = 'ASC' then
WHILE t_checkpagecount < t_numrows and t_newrunto < t_dtto LOOP --if less than number to display add the interval and we have not met the date range
t_newrunto := t_newrunto + t_dayinterval;
if t_newrunto > t_dtto then
t_newrunto := t_dtto;
end if;
-- get a new count with the new range
t_rec_check_sqlstmt := 'select count(*) from '||t_reportname||' where '||t_reportconditions||' and (('||t_datecolname||' > :2 and '||t_datecolname||' <= :3) or ('||t_datecolname||' = :4 and '||t_pkcolname||' > :5))';
execute immediate t_rec_check_sqlstmt into t_checkpagecount using t_newrunfrom , t_newrunto, t_newrunfrom, t_pkid;
END LOOP;
else
--if descending sort order
WHILE t_checkpagecount < t_numrows and t_newrunfrom > t_dtfrom LOOP --if less than number to display add the interval and we have not met the date range
t_newrunfrom := t_newrunfrom - t_dayinterval;
if t_newrunfrom < t_dtfrom then
t_newrunfrom := t_dtfrom;
end if;
-- get a new count with the new range
t_rec_check_sqlstmt := 'select count(*) from '||t_reportname||' where '||t_reportconditions||' and (('||t_datecolname||' >= :2 and '||t_datecolname||' < :3) or ('||t_datecolname||' = :4 and '||t_pkcolname||' < :5))';
execute immediate t_rec_check_sqlstmt into t_checkpagecount using t_newrunfrom , t_newrunto, t_newrunto, t_pkid;
END LOOP;
end if;
--need to get new data for insert into report_scroll
if upper(t_sort) = 'ASC' then
t_get_param_sqlstmt := 'select '||t_datecolname||', '||t_pkcolname||' from (select '||t_datecolname||', '||t_pkcolname||' from '||t_reportname||' where '||t_reportconditions||' and (('||t_datecolname||' > :2 and '||t_datecolname||' <= :3) or ('||t_datecolname||' = :4 and '||t_pkcolname||' > :5)) order by '||t_datecolname||', '||t_pkcolname||' fetch first :6 rows only) order by '||t_datecolname||' desc, '||t_pkcolname||' desc fetch first 1 rows only';
execute immediate t_get_param_sqlstmt into t_pagerunto, t_newpkid using t_newrunfrom , t_newrunto, t_newrunfrom, t_pkid, t_numrows;
t_pagerunfrom := t_newrunfrom;
else
t_get_param_sqlstmt := 'select '||t_datecolname||', '||t_pkcolname||' from (select '||t_datecolname||', '||t_pkcolname||' from '||t_reportname||' where '||t_reportconditions||' and (('||t_datecolname||' >= :2 and '||t_datecolname||' < :3) or ('||t_datecolname||' = :4 and '||t_pkcolname||' < :5)) order by '||t_datecolname||' desc, '||t_pkcolname||' desc fetch first :6 rows only) order by '||t_datecolname||' , '||t_pkcolname||' fetch first 1 rows only';
execute immediate t_get_param_sqlstmt into t_pagerunfrom, t_newpkid using t_newrunfrom , t_newrunto, t_newrunto, t_pkid, t_numrows;
t_pagerunto := t_newrunto;
end if;
--check if we have a report entry w/o forced params
select nvl(max(reportid), 0) into t_checkrpt from report_settings where upper(reportname) = upper(t_reportname);
if t_checkrpt = 0 then
insert into report_settings (reportname, dayinterval) values (t_reportname, t_dayinterval);
end if;
--insert our settings/info
if t_pageno = 1 then --insert the instance record
insert into report_instance(sessionid, reportname, reportconditions, columnlist, pageno, numrows, numrecsinrpt, datefrom, dateto, pagedatefrom, pagedateto, pkid, dayinterval, sort)
values (t_sessionid, t_reportname, t_reportconditions, t_collist, t_pageno, t_numrows, t_numrecsinrpt, t_dtfrom, t_dtto, t_pagerunfrom, t_pagerunto, t_newpkid, t_dayinterval, t_sort);
select nvl(max(reportinstanceid), 0) into t_reportinstanceid from report_instance;
else --update the existing row
update report_instance set pageno = t_pageno, pagedatefrom = t_pagerunfrom, pagedateto = t_pagerunto, pkid = t_newpkid where reportinstanceid = t_reportinstanceid;
end if;
insert into report_scroll(sessionid, reportinstanceid, reportname, pageno, numrows, datefrom, dateto, pagedatefrom, pagedateto, pkid, dayinterval)
values (t_sessionid, t_reportinstanceid, t_reportname, t_pageno, t_numrows, t_dtfrom, t_dtto, t_pagerunfrom, t_pagerunto, t_newpkid, t_dayinterval);
end if;
else -- they want a specific page that is already generated
select pagedatefrom, pagedateto into t_newrunfrom, t_newrunto from report_scroll where pageno = t_pageno_in and upper(sessionid) = upper(t_sessionid) and upper(reportname) = upper(t_reportname) and datefrom = t_dtfrom and dateto = t_dtto and createdon > sysdate - (1/24);
--need the PKID from the previous page
select nvl(max(pkid),0) into t_pkid from report_scroll where pageno < t_pageno_in and upper(sessionid) = upper(t_sessionid) and upper(reportname) = upper(t_reportname) and datefrom = t_dtfrom and dateto = t_dtto and createdon > sysdate - (1/24);
end if;
--set indicator to inform if more records in report
if (t_pageno * t_numrows) >= t_numrecsinrpt then
t_hasmore := 'N';
else
t_hasmore := 'Y';
end if;
--build the SQL
if upper(t_sort) = 'ASC' then
t_sqlstmt := 'select '||t_collist||' from '||t_reportname||' where '||t_reportconditions||' and (('||t_datecolname||' > :2 and '||t_datecolname||' <= :3) or ('||t_datecolname||' = :4 and '||t_pkcolname||' > :5)) order by '||t_datecolname||', '||t_pkcolname||' fetch first :6 rows only';
OPEN T_REC_OUTPUT FOR t_sqlstmt USING t_newrunfrom , t_newrunto, t_newrunfrom, t_pkid, t_numrows;
else
t_sqlstmt := 'select '||t_collist||' from '||t_reportname||' where '||t_reportconditions||' and (('||t_datecolname||' >= :2 and '||t_datecolname||' < :3) or ('||t_datecolname||' = :4 and '||t_pkcolname||' < :5)) order by '||t_datecolname||' desc, '||t_pkcolname||' desc fetch first :6 rows only';
OPEN T_REC_OUTPUT FOR t_sqlstmt USING t_newrunfrom , t_newrunto, t_newrunto, t_pkid, t_numrows;
end if;
exception when no_data_found then
t_hasmore := 'N';
END;
/