Interestingly today we found that on certain situations the following query worked better than the query I submitted.  (SQL 2000).  It is not always, but there is a difference at times.  I have not explored the reasons yet.  but a heads up so that you all can start a research on the best method SELECT * FROM (    ...

Hi,I doubt that this is related to some hardware resources like memmory or IO operation.''According to tests I have done the time gap expands as data volume goes up'', this is not a issue. It should go up when the volume goes up because it needs to read more pages and more resources.

Hi,It depends on the way you have written the SP. This post may help you to find the problem.Use CURSOR whenever you have no other options for implementation. Most of the time, WHILE loop can be used instead CURSOR. If the above link does not help you on this, please let us know the content of the SP.