Hi friends,
today I'm going to show you one possible way to speed up the COUNT(*) performance in case when you need to know the total amount of rows of your select query with paging.
Here is the example of my first and slow query:
Select
COUNT(*) OVER () as TotalCount
, ID
, OwnerID
, Name
, [Description]
, keywords
From [dbo].[tblData]
where pState = N'a' and [status] <> 'P'
and (@FilterBy = 0 OR @FilterBy = TypeID)
Order By DateAdded Desc
OFFSET @p0 ROWS FETCH NEXT @p1 ROWS ONLY;
this query did take from 4 seconds up to 9 seconds selecting 315000 rows from about 450000 rows -
that is really slow. It seems like OVER () overloads the query itself (but this is just my guess).
Also converting from nchar to char takes a lot of time, so I did change it (notice the N letter before string)
After some time of thinking and playing with that around, I did come to the following solution:
select @rowstotal = count(*)
From [dbo].[tblData]
where pState = 'a' and [status] <> 'P'
and (@FilterBy = 0 OR @FilterBy = TypeID)
select
@rowstotal as TotalCount
, ID
, OwnerID
, Name
, [Description]
, keywords
From [dbo].[tblData]
where pState = 'a' and [status] <> 'P'
and (@FilterBy = 0 OR @FilterBy = TypeID)
Order By DateAdded Desc
OFFSET @p0 ROWS FETCH NEXT @p1 ROWS ONLY;
(pay attention that to calculate total number of rows to be selected you do not need ordering. I think count(*) with ordering works much slower)
Now this query takes up to 1 second to select the same 315000 rows from about 450000 that is minimum 4 times faster :)
I'm not sure if this is really best solution as I'm not a DBA master :)
but in my case it sped up the query enough.
Thank you.
1vqHSTrq1GEoEF7QsL8dhmJfRMDVxhv2y