How to do Select from a large amount of data (gridview + paging)?

Question

SQL

I looked into the future and imagined that in 1 of the tables there would be, for example, 1M rows. I decided to add and check how everything will work. Unfortunately, my expectations were not justified at all, and for example, in order to switch between paging pages on a local basis, it takes about ~ 5 seconds. (ps and this is only from one user), in the future everything can be much worse.
The idea immediately came to mind to select a range or the first "x" rows, but here again we get a problem. And if the user needs to sort in the future the data that he added 3 years ago? Actually, I'm completely confused. Experienced people, please tell me what is the best way to act in such situations? :)
P.s. At the moment, the select request goes through the following procedure:

ALTER Procedure [dbo].[usp_Purchase_Select_All] (		
    @SelectByDate1 date = null,
    @SelectByDate2 date = null,
    @ddl_Category varchar(70) = null,
                @UserID int
  ) as
   select * 
   from Purchase
   where UserID = @UserID
         and P_Date >= coalesce(@SelectByDate1, p_Date)    //параметры для сортировки(если не пустые)
   and P_Date <= coalesce(@SelectByDate2, p_Date)    //по дате до, после и категориям
   and P_Text_ddl = coalesce(@ddl_Category, P_Text_ddl)
   order by p_date desc

Answer 1

If the question is about MSSql, then LIMIT will help:

select * from Purchase where .... 
order by p_date desc SKIP @page*@limit LIMIT @limit;

For page 5 to 20 would be "SKIP 4*20 LIMIT 20"
Source https://msdn.microsoft.com/en-us/library/vstudio/b...

Answer 2

Do not try to test the "speed" of the work on the dev machine, because food machines are many times faster.
In my experience, MySQL on a 30 million table can easily do a mask search (LIKE 'abc%') in 0.5 - 1 sec.