Re: Anything I can do to speed up this query?

On Tue, 2006-12-05 at 14:56, Wei Weng wrote:
> I have a table that has roughly 200,000 entries and many columns.
>
> The query is very simple:
>
> SELECT Field1, Field2, Field3... FieldN FROM TargetTable;
>
> TargetTable has an index that is Field1.
>
> The thing is on this machine with 1Gig Ram, the above query still takes
> about 20 seconds to finish. And I need it to run faster, ideally around
> 5 seconds.

You're basically asking for everything in the table, right?

If you're not using a cursor, then it's gonna take the time to grab the
data then transfer it across the wire.

If you wrap that query in a cursor:

begin;
declare bubba cursor for select * from targettable;
fetch 100 from bubba; -- repeat as necessary
commit; -- or rollback, doesn't really matter.

That way you can start getting data before the whole result set is
returned. You won't get the data any faster, but you can start spewing
it at the user almost immediately. Which makes is feel faster.