CODEWITHSUNDEEP
javasqlsql-serverjdbcpagination
quarks
quarks

Reputation: 35276

Strategies for paging in Stored procedure (View or ?)

I have this stored procedure that returns around 100K to 1M rows, depending on the parameter:

CREATE PROCEDURE dbo.sp_doStuff
    @userId bigint = 0,
    @date datetime = null
AS
BEGIN
    SET NOCOUNT ON;

    SELECT 
        ...
    from 
        ...
    order by theDate DESC;

END

This stored procedure is called by a Java method via jdbcTemplate, and it does the paging from there. However that is very slow, considering that sp_doStuff average execution time is 60 seconds already.

That is this sp_doStuff is called for every next page button the user clicks (very slow). Its ok for one time its 60 seconds, but for the next page it should not be. How can I implement a view (or whatever solution application) within this SQL code such that the jdbcTemplate call will not have to deal with this hundred thousand rows everytime.

Called every time next button is clicked:

String sql = "MyDB..sp_doStuff '12345', '2013-01-24'"
return jdbcTemplate.query(sql, new ResultSetExtractor<MyModel<Map<String, String>>>() {
        @Override
        public MyModel<Map<String, String>> extractData(ResultSet rs)
                throws SQLException, DataAccessException {
                ....
        }
    });

Upvotes: 2

Views: 761

Answers (1)

Evgeniy Fitsner
Evgeniy Fitsner

Reputation: 946

Sorry, but you really should display all 100k rows to user on page??? It's looks crazy, real person never analize 100k rows of information in real life. It's OK if you have 1M of rows and calculate AVG of their values or smth else, but for displaying to person from SP is very bad practise. From my perspective, you should using approach with limit of result set of rows and indexes. Ex.:SELECT salary, date FROM USER_SALARY t WHERE t.id = '1748' ORDER BY date DESC LIMIT 29799, 100; This request will display 100 rows (start rows position - 29800) from USER_SALARY table for user with id 1748 which ordered by day field (of course index already created by ID field) I suppose, that above approach allow you to work with big data tables which should be displayed to user on page.

Upvotes: 1

Related Questions