0

I am using latest SQL Server. In my DB, I have close to one million rows, the below pagination query is taking 8 seconds to return data. When I remove totalcount column, its returning in 1 sec, so any better approach to get totalcount in same query with better performance?

Note:For brevity, I put my query as below where I do have filters and all for the below query

DECLARE @pageNum  INT = 1;
DECLARE @pageSize INT = 25; 
            
    select 
        t1.col1,t1.col2,t2.col3,t2.col4
        ,count(t1.col1) over() as totalCount
        from mytable t1
        left join mysecondtable t2 on t1.col1=t2.col3
    
    ORDER BY t1.col1
    
      OFFSET (@pageNum-1)*@pageSize ROWS
      FETCH NEXT @pageSize ROWS ONLY;
Dale K
  • 25,246
  • 15
  • 42
  • 71
A DEv
  • 255
  • 1
  • 19
  • Is the relationship between `t1` and `t2` a one/many-to-many? If not, you might find a "better" option is to use the system tables to get the table's row count. – Thom A Jun 08 '23 at 14:29
  • 1
    You might want to consider Keyset Pagination which is much more performant, see https://stackoverflow.com/questions/70519518/is-there-any-better-option-to-apply-pagination-without-applying-offset-in-sql-se/70520457#70520457 Otherwise if you can make sure `col1` is indexed properly, and don't return the `totalCount` if you can manage without it – Charlieface Jun 08 '23 at 14:29
  • In my query i do have filters and all, so I cannot use system tables to get the rowcount – A DEv Jun 08 '23 at 14:38
  • https://use-the-index-luke.com/sql/partial-results/fetch-next-page describes why OFFSET becomes more expensive for each page. – jarlh Jun 08 '23 at 14:42
  • *"In my query i do have filters and all, so I cannot use system tables to get the rowcount"* Perhaps that's also part of the problem; don't overly simplify the query. – Thom A Jun 08 '23 at 14:47
  • I ran without filters, similar like above for getting just the paginated data along with total count, and it gave me 8 secs.. – A DEv Jun 08 '23 at 14:56
  • i would probably just remove the offset / fetch thing, do a ROW_NUMBER() and filter pages based by that. Since you anyway are touching every row in your selection to get the count. Pagination pretty much always suck :/ – siggemannen Jun 08 '23 at 15:09
  • It probably won't make a measurable difference but you could try `count(*)` instead of `count(t1.col1)`. The cost of obtaining the total row count is roughly the same as running the query without pagination and returning rows back to the client. – Dan Guzman Jun 08 '23 at 15:12
  • 1
    if you have good index on the COUNT query without needed to fetch the rest of the columns from the left join:ed table, it might actually be more performant to do the COUNT as separate query and then do the pagination one – siggemannen Jun 08 '23 at 15:18
  • can we get totalcount as well as paginated data in one query using CTE? – A DEv Jun 08 '23 at 15:20
  • finally I got a much better way....from here itself....so am posting here for anyone who look for...https://stackoverflow.com/questions/21537511/sql-server-query-with-pagination-and-count – A DEv Jun 08 '23 at 16:29
  • "I am using latest SQL Server" - please post your actual version, because this will no longer be accurate when the next release comes out. – Dale K Jun 09 '23 at 00:56

0 Answers0