I am working with a MS SQL2005 database and have a page where one can see results of their search. Using T-SQL, I have figured out how to return only a page of data at a time, and how to request the next page, and all that. My table presentation includes clickable column headers that re-query the tables with new "Order By" to get the new sort order.
It all works fairly well, but is certainly database heavy. The problem comes when a user executes a complicated (long) query that only returns a dozen rows or so. When they click on a column header to resort the results, the query goes back to the database and executes another long query. This delay doesn't work well for the user since he's thinking that I only needed to sort a dozen rows.
Is there a way to cache a dataset in PHP 5 that will allow me to sort and resort (and change number of rows on each page, etc) a lot quicker than what I am currently doing?
If so, is there an online article or such that will help me to gain familiarity and understanding of using this new area of PHP?
Also, how do I handle the situation where the query returns thousands of rows? In my current setup, the database only returns n number of rows at a time (default = 15). If I use some kind of dataset cache, will I mess myself up waiting for the initial load of the dataset?
Another way may be to create a temporary table with the query results, then use my current way of asking only for a page full of rows at a time. Might this be the best way?
Or is there a better way?