I have implemented pagination using SQL and stored that result of search into temporary table.

Temporary table is named after unique tab id.

So that other tab can have seperate result sets.

That table can be deleted using unload event of browser.

Now issue I am getting is, sometimes browser crash or system restart, unload event is not firing up.

I have to store into temporary table due to large result size (that's what I found easy to implement).

And it is not feasible to apply search query on each first, previous, next, last navigations.

Is there a better way to handle this scenario?

SELECT * INTO Search_1587904945298 -- Timestamp as unique id using javascript new Date().getTime()
FROM    ( SELECT    ROW_NUMBER() OVER ( ORDER BY OrderDate ) AS RowNum, *
          FROM      Orders
          WHERE     OrderDate >= '1980-01-01'
        ) AS RowConstrainedResult
WHERE   RowNum >= 1
    AND RowNum < 20
  • How big is the result size? I can't think of why it would be necessary to store results in a temporary table. If there is an end user paging through results, why would they have need to see any more than 15-20 items at a time? Even if the page size is hundreds or low thousands I can't see a need to store in a temporary table? Commented Apr 26, 2020 at 12:36
  • Also, can you share some snippets of the code that you have so far? It's hard to help without seeing some code. Also what frameworks are you working with? Is this an ASP.NET MVC 5 application? Or is it a ASP.NET Core? Commented Apr 26, 2020 at 12:37
  • I have added sql query which uses temporary table for example. and also I need to store result, because I don't want to change search result if there is any, during navigation. I want to preserve initial result except that user does search again. Commented Apr 26, 2020 at 12:47
  • records need to handle as large as 20-30 million. Commented Apr 26, 2020 at 12:48
  • 2
    It's a good approach to cache the results for sometime. Post that why can't you remove these tables using a cron? Commented Apr 26, 2020 at 13:25


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.