A long time ago, in a galaxy far, far away, "Bupp Phillips" <hello@noname.com> wrote:
>I have a table that has 103,000 records in it (record size is about
>953 bytes) and when I do a select all (select * from <table>) it takes
>a whopping 30 secs for the data to return!!
>SQLServer on the other hand takes 6 secs, but you can also use what is
>called a firehose cursor, which will return the data in < 1 sec.
>I have done everything that I know how to speed this up, does anyone
>have any advise?
Have you VACUUMed the table? 30 seconds to start getting data back
from such a query _seems_ a liittle high.
It would be quite a reasonable idea to open up a CURSOR and request
the data in more byte-sized pieces so that the result set wouldn't
forcibly bloat in any one spot.
You start by submitting the cursor definition, inside a transaction:
begin transaction;
declare cursor my_fire_hose for select * from <table>;
You then iterate over the following, which fetches 1000 rows at a time:
fetch forward 1000 in my_fire_hose;
That should cut down the time it takes to start getting records to
near enough to zero...
--
output = reverse("gro.gultn" "@" "enworbbc")
http://www3.sympatico.ca/cbbrowne/lisp.html
"Microsoft is sort of a mixture between the Borg and the
Ferengi. Combine the Borg marketing with Ferengi networking..."
-- Andre Beck in dcouln
