I am trying to return the results of a query in Sql Server
(to an asp.net front end but this is irrelevant) in page sets depending on a requested Page No. & Page size to reduce the network traffic.
I have tried two different solutions, one using a set of nested queries using the TOP clause and the other solution using a temporary table to store the no. of records containing the page required and then using the temp table serial key to extract the ones pertinent to the page.
These are pretty similar to the solutions proposed by Microsoft but extensive testing has revealed that the query slows down considerably within certain areas.
For the nested queries solution it seems to slow down when you request a page in the middle (not the end as I expected) [/color ]
[COLOR=green ] For the Temp table solution, the query seems to slow down towards 7% area of the total no of pages. [/color ].
I've put the results in a spreadsheet to see if I could see a pattern but I can't make any sense of it at all.
[COLOR=red ] Has anyone experienced this kind of performance erosion and knows why and how can you fix it ??? [/color ]
(to an asp.net front end but this is irrelevant) in page sets depending on a requested Page No. & Page size to reduce the network traffic.
I have tried two different solutions, one using a set of nested queries using the TOP clause and the other solution using a temporary table to store the no. of records containing the page required and then using the temp table serial key to extract the ones pertinent to the page.
These are pretty similar to the solutions proposed by Microsoft but extensive testing has revealed that the query slows down considerably within certain areas.
For the nested queries solution it seems to slow down when you request a page in the middle (not the end as I expected) [/color ]
[COLOR=green ] For the Temp table solution, the query seems to slow down towards 7% area of the total no of pages. [/color ].
I've put the results in a spreadsheet to see if I could see a pattern but I can't make any sense of it at all.
[COLOR=red ] Has anyone experienced this kind of performance erosion and knows why and how can you fix it ??? [/color ]
