0

I've just had to start paging in SQL Server 2012 and I'm trying to get the total row count before paging is applied, but the problem I have is that my view has a few too many function calls in it that massively slow it down.

I've looked at this post and I've ended up with a query that takes 39 secs to run without the full data set in the DB.

Get total row count while paging

    SELECT *
    , COUNT(TaskId) OVER()
FROM TaskVersionView
WHERE (.. ~10 predicates here .. )
ORDER BY StartDate
OFFSET 0 ROWS
FETCH NEXT 50 ROWS ONLY

Without the COUNT it takes <1 second.

I would have expected SQL to optimize it so that it only counts the TaskIds instead of calling the functions but that doesn't seem to be the case, because:

SELECT COUNT(TaskId)
FROM TaskVersionView

Takes <1 sec.

Community
  • 1
  • 1
AndrewC
  • 321
  • 1
  • 2
  • 11

1 Answers1

0

I would have expected SQL to optimize it so that it only counts the TaskIds instead of calling the functions

  • If the predicates are always 'true' then this 'optimization' would return the correct value. Not that SQL Server could, even in theory, guess that the functions will always return true. But if you know (as it seems to imply from your expectation) that the functions in the predicates always return true then obviously you should remove them from the WHERE clause...

  • If the predicates sometimes return 'false' then obviously they cannot be optimized away, as the returned values would be incorrect.

Something gotta give.

PS. Paging with total counts is a bad idea, as it forces a full scan on every visit. Paging with total counts on which the total count is returned for every row is a horrible bad idea (modeling wise, perf wise, sanity wise).

Remus Rusanu
  • 288,378
  • 40
  • 442
  • 569
  • Just to clarify there are no functions in my predicates. This is a hierarchial structure I'm modelling (Tasks with nested child tasks) so the functions are for traversing up and down the tree to pull down data to display in the view, and this is what is slow on larger data sets. I get your concern with the total counts being done like this, but is there any reasonable alternative? – AndrewC Jun 03 '14 at 10:29
  • I would say that the problem is anchored in the UX/UI. *why* present a total count? Today there are many alternatives, eg. contigous scrolling with powerfull filtering. Read [The End of Pagination](http://blog.codinghorror.com/the-end-of-pagination/). If you insist on pagination, then total counts will *always* be tough to crack, as soon as you reach any reasonable data size and traffic. I've seen literally *thousands* of hours invested in solving this problem (eg. via app cache + in-memory count maintenance). – Remus Rusanu Jun 03 '14 at 10:35
  • I'd agree with endless scrolling.. But the problem is in some instances I might have 5+ of these tables on screen for a dashboard so pagination and a total count is sort of required. – AndrewC Jun 03 '14 at 10:37
  • Pick your poison: **Approximate**: display counts that are *near* accurate, but cheap to compute. **Cache**: display counts that are stale and refresh every minute or so. **Precompute**: use [indexed views](http://technet.microsoft.com/en-us/library/ms187864(v=sql.105).aspx) to precompute aggregates (has serious locking consequences for updates). The whole 9 yards is an in memory cached count that gets continuously updated by the app. Is hard to pull off on a single node, on a farm of apps is next to impossible. – Remus Rusanu Jun 03 '14 at 10:41