Pagination in T-SQL to be done on 50000 records . Display 20 rows at single time

• myzus2004

  Old Hand

  Points: 378

  More actions

  September 25, 2015 at 4:14 am

  #399090

  Hello Guys,

  i got a requirement from business that they need pagination for an application.

  The stored procedure takes ~ 10 seconds every call. The execution plan is Index seek or clustered index scan almost everywhere . No table spools or lazy loads or key lookups. Cannot share the code or execution plan due to company norms. Any help is appreciated.

  Need help with changing business logic of my T SQL code.

  Issue is that i am dealing with result set of around 50,000 records . Out of these i have to return 20 records at a single time (which is also customizable i.e 40 / 60/ 150 records in a page). Application cannot handle all 50k records so i have to return 20 records for every stored procedure call.

  The result set changes as per the start date and end date which i recieve as parameters.

  In application there are few Column filters namely- Country(around 50 countries), Outcome(around 6 to 10 values ) . These filters will values in drop down( as excel ) depending on the distinct values in that columns. These filters will be populated on every page, if no filter value is selected.

  Issue is if user does sorting or filtering any records , this stored procedure is called and every time i have to deal with ~50000 records.

  Current Code :

  Step 1 ) Get the required result set in temp table .

  Step 2) Compute the results on some business rules . (Outcome and SharesAvailable calculation - see attachment)

  Step 3) populate filter Columns (Country,Outcome) these values will be comma seperated.

  Step 4) Dynamic query to get required result set i.e if user wants only 10 records in single page then TOP 10 . Sorting can be applied on any column mentioned in screenshot.

  Please let me know if any other information is needed.

  Thanks,

  Myzus