Hi friends,
I was recently developing some mobile app in flutter and was requested to create auto-paging on scrolling.
So when user scrolls to the bottom it would go to/append the next page of content and this is inside of the simple
SingleChildScrollView (of course as I was already using the SingleChildScrollView as main container :) ).
We have the scroll controller property but what it gives us? Offset and scroll direction in general but how we will use it to make paging?
We need to detect the bottom of the current page list and we should allow user to interact with last item in the current list before going to or appending a new page.
So here is my solution for it:
ScrollController _controller;
int bottomOutOfRange = 0;
_scrollListener() {
if (_controller.offset > 0.0 &&
_controller.position.maxScrollExtent > 0.0) {
if (_controller.offset >= _controller.position.maxScrollExtent &&
!_controller.position.outOfRange) {
if (++bottomOutOfRange >= 2) {
bottomOutOfRange = 0;
setState(() {
if (curPage < (totalPages - 1)) {
curPage++;
_pbVisible = true;
SearchCars();
} else {
curPage = 0;
_pbVisible = true;
SearchCars();
}
});
} else {
_controller.animateTo(_controller.offset - 5,
duration: Duration(milliseconds: 500), curve: Curves.easeIn);
}
}
}
}
@override
Widget build(BuildContext context) {
return Scaffold(
key: _context,
appBar: CustomAppBar(),
body: Container(
margin: EdgeInsets.only(bottom: 82.0),
child: SingleChildScrollView(
controller: _controller,
....
First time user srolls to the very bottom of a page we bounce a little bit back and increment our counter, now if user really whishes to scroll down and see the next page our counter will be triggered one more time and we will know that it will be time to go to/append the next page.
Thank you and see you :)
1vqHSTrq1GEoEF7QsL8dhmJfRMDVxhv2y
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