Hello Frank,

Thanks for your detailed answer. Your answer is helping me a lot. It is
so slow because there are a lot of calculations on the procedure.

Many thanks, best regards,
Nico Callewaert


-----Oorspronkelijk bericht-----
Van: Frank Ingermann [mailto:frank.ingermann@...]
Verzonden: vrijdag 22 maart 2002 0:52
Aan: ib-support@yahoogroups.com
Onderwerp: Re: [ib-support] SP question / fetching query results


Hi Nico,

Nico Callewaert wrote:

>
> Thanks for the reply, but my question was : Why if a SP takes only 7

seconds

> to execute, why the server needs 1.5 minutes to fetch only a few hundred
> records ? The data is displayed in a grid, within 7 seconds there are
> enough rows displayed to fill the screen, but when I scroll down in the

grid

> until the end, it needs a lot of time before I can see the last record...

(is "a lot of time" vaguely the same as 1.5 minutes ??? :-) see below...

> That's why I wonder : is the whole stored procedure executed in one time ?

No. It stops after every SUSPEND and waits for the client to send another
Fetch command. If the client doesn't, then the storedproc doesn't do
anything
either.

> or just enough rows to fill the grid on the screen ?

Yep. IBO (with FetchAll = FALSE!) and (presumably, i don't use it) IBX only
fetch just enough records to fill the grid. That's why scrolling is slow:
the result row on the server (for the recs beyond those already in the grid)
is built and sent to the client just when you scroll, not before.

The grid tells the query: "i need more rows" -> the query tells the server
"i need more rows" (=Fetch) -> the server *either* replys: "there are no
more
rows" -> (EOF for the client) *or* the storedproc continues after it's last
SUSPEND, returning another row. One more line in the grid, one scroll,
record by record. ( <- this may be 6 lines when you use a wheel mouse <g>)

So the 7 seconds you see is the time it takes to return just enough rows
to fill the grid, while the 1.5 mins is how long it takes to fetch *all*
rows until the (server-side) storedproc says: "That's all!" (and not the
client-side query/grid saying: "i don't want {to fetch} more rows").

(in case you need/want more help, it would be helpful if you could supply
some more information: storedproc source, how many recs in the tables,
indexes etc. An SP taking 1.5 mins to return 300 rows is either on a very
slow server or might need some optimization :-)

hth + regards,
fingerman


To unsubscribe from this group, send an email to:
ib-support-unsubscribe@egroups.com



Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/