Re: SQL Result Paging

On 11 Mar 2013 12:28, Mike Wills wrote:

I have a query that is sub-second. When I run it on the green-screen,
I get a result as soon as I hit "enter". So I know that that query
itself isn't the problem. I am returning around 800 results.

The interactive STRSQL query report run output to display runs with effectively the implicit clause specifications of OPTIMIZE FOR 19 ROWS and FOR READ ONLY, and probably also the WITH NC. Unless B=Bottom with REFRESH(*FORWARD) had been requested after the first subset of rows appeared, the query is still pending completion; i.e. only the first FETCH was requested.

Comparing the plans for the two query requests may have value.

However, when I pipe it through my C# ASP.NET application, it takes
FOREVER (okay 3 minutes), but lets be honest, that is forever in the
web world.

And when that SQL query includes all of those clauses noted above? If just the OPTIMIZE clause for a smaller number of rows improves the query response for the return of all data, then that may indicate an issue with the optimizer implementing via a sub-optimal path.

<<SNIP paging inquiry>>