Myles Wakeham wrote:

>
> Well I took all of your advice here (much appreciated) and have the
> procedure running like a rocket now. But I'm trying to work out how to move
> the 'paging' to the stored procedure, so that I can do the SELECT FIRST X
> SKIP Y logic in the stored procedure.
>

Here's the problem with what you're trying to do, whether in client
requests or a stored procedure.

When you run this statement

select first 20 skip 0 name from employees sorted by badge

Firebird reads all the employee records, sorts them by badge number,
returns the first 20 and stops.

When you run this statement:

select first 20 skip 20 name from employees sorted by badge

Firebird reads all the employee records, sorts them by badge number,
skips the first 20, returns the next 20 and stops.

When you run this statement:

select first 20 skip 40 name from employees sorted by badge

Firebird reads all the employee records, sorts them by badge number,
skips the first 40, returns the next 20 and stops.

When you run this statement:

select first 20 skip 60 name from employees sorted by badge

Firebird reads all the employee records, sorts them by badge number,
skips the first 60, returns the next 20 and stops.

After a few iterations, especially on a large table, the process
is pretty slow.

You can make it faster by omitting the sort, but that means that
you're not guaranteed to get consistent results.


Regards,


Ann