Обсуждение: Limiting the number of records in Execute Query

Hello,
I would like to know how to retrieve large amount of records in small segments.

I am retrieving 1 million records by executing the sql query
   SELECT * FROM myTable ORDER BY sub_id LIMIT 20000 OFFSET <num>;
in a loop, incrementing <num> every time by 20000.

This is working fine, but the speed of execution is slow (may be due to the ORDER BY that is done each time.)

Is there any sql-odbc api which does the same(a variant of SQLExecute)?
An api which enables to scroll along the records and execute the query?

Thanks,
Shooj.

Raj Shooj-Q16466 <shooj@motorola.com> writes:
> I am retrieving 1 million records by executing the sql query
>    SELECT * FROM myTable ORDER BY sub_id LIMIT 20000 OFFSET <num>;
> in a loop, incrementing <num> every time by 20000.

Wouldn't it be better to use a cursor?

    BEGIN;
    DECLARE c CURSOR FOR SELECT * FROM myTable ORDER BY sub_id;
    FETCH 20000 FROM c;
    ... repeat FETCH as needed ...
    COMMIT;

            regards, tom lane