Getting counts for a paged SQL search stored procedure

Question

I've written a paged search stored procedure using SQL Server 2005. It takes a number of parameters and the search criteria is moderately complex.

Due to the front-end architecture I need to be able to return the number of results that would come back without actually returning the results. The front end would then call the stored procedure a second time to get the actual results.

On the one hand I can write two stored procedures - one to handle the count and one to handle the actual data, but then I need to maintain the search logic in at least two different places. Alternatively, I can write the stored procedure so that it takes a bit parameter and based on that I either return data or just a count. Maybe fill a temporary table with the data and if it's count only just do a count from that, otherwise do a select from it. The problem here is that the count process could be optimized so that's a lot of extra overhead it seems (have to get unneeded columns, etc.). Also, using this kind of logic in a stored procedure could result in bad query plans as it goes back and forth between the two uses.

The amount of data in the system isn't too high (only a couple million rows for even the larger tables). There may be many concurrent users though.

What are people's thoughts on these approaches? Has anyone solved this problem before in a way that I haven't thought of?

They CANNOT take the results and count at the same time from a single call.

Thanks!

Answer 1

This isn't a normal problem and you usually want the total counts at the same time your getting a page.

That said, use two different procedures. The reason is that you have two very different actions which only resemble each other superficially.

Answer 2

I personally go with the two query approach, yes, you have to maintain the search logic in two places, but I've found that the performance optimization benefit, and overall cleanness of the code pays off in the end.

Using a flag passed to a single procedure, is a potential solution, but I just find that very hard to maintain, especially for complex search logic.

The route of using temporary tables etc, that just adds WAY more overhead than what is needed.

Thus, why I have landed with the two query method. Everything that I find online recommends this approach as well.

Answer 3

Is caching a possibility?

Answer 4

Might not help with your specific problem, but SQL 2005 introduces the Row_Number function which is handy for paging check

Row_number example

Much easier than temp tables.

Answer 5

I found this thread researching something else, and thought I'd mention that it is possible to return the result set and the record count with one query. You just need an 'out' parameter to carry the value. Below is a copy/paste of an Oracle example, but the technique is very similar for SQL Server (I don't have access to SQL Server atm).

The big thing with SQL Server is you might need to use row_number() vs rownum.

procedure get_sample_results (
    startrow in number default 1,
    numberofrows in number default 10,
    whereclause in varchar2,
    matchingrows out number,
    rc  out sys_refcursor
)
is
    stmnt varchar2(5000);
    endrow number;
begin

    stmnt := stmnt || 'select * from table t where 1=1';
    if whereclause is not null then
        stmnt := stmnt || ' and ' || whereclause;
    end if;

    execute immediate 'select count(*) from (' || stmnt || ')' into matchingrows;

    stmnt := 'select * from (' || stmnt || ') where rownum between :1 and :2';        

    -- must subtract one to compenstate for the inclusive between clause
    endrow := startrow + numberofrows - 1;
    open rc for stmnt using startrow, endrow;

end get_sample_results;

Answer 6

I'm sure you've considered this: If the data is changing the COUNT and any subsequent actual Paging may be different (if rows added / removed)

You could have a User Defined Function that returned the PKs of the matching rows, relatively easy to do a

SELECT COUNT(*) FROM dbo.MyQueryFunction(@Param1, @Param2)

to get the count, and then

SELECT Col1, Col2, ...
FROM dbo.MyQueryFunction(@Param1, @Param2) AS FN
     JOIN dbo.MyTable AS T
         ON T.ID = FN.ID
     ... more JOINs ...

to get the data.

Don't know how well this sits with Row_Number for the subsequent paging, but it would keep the actual "query logic" contained within MyQueryFunction - you're still going to have all the JOINs for any column to be retrieved duplciate in the Sproc and the Function.