Thanks again Geoff,

I know you got in serious trouble some time ago. I followed your
postings and the answers on it very closely :-), to learn something out of
it.

Thanks,

Nico
-----Oorspronkelijk bericht-----
Van: Geoff Worboys [mailto:geoff@...]
Verzonden: vrijdag 4 mei 2001 12:52
Aan: IBObjects@yahoogroups.com
Onderwerp: Re: [IBO] Query / SP question

> Computers are so fast today that you can bearely see a
> difference in speed between all the different kind of
> select's. So, I'm wondering what would be faster :
> a query (with a few joins), retrieved with a TIB_Query,
> or a stored procedure that returns a result set ?

AFAICT there is very little difference. I recently changed a huge
statement over from a several left outer join situation to a stored
procedure and the performance appeared to be very similar.

There may be implications with default/inner joins, since IB **may**
be able to perform optimisations that limit how much needs to be read
from disk.

IMO stored procedures have the advantage of allowing the developer to
express much more clearly what is required. Statements with complex
joins often get very confusing, and stored procedures offer a way of
avoid that confusion.


As a side note: I recently came across an interesting situation that
tried to blow my IB server. I was using a select that contained
several embedded selects from stored procedures. The select returned
over half a million records, and for some reason caused IB to continue
consuming more and more memory as records were returned to the
client - at the end, around 380Mb of RAM was used!

When I hit the problem I tried changing over to use a stored
procedure, and moved the embedded selects inside the FOR SELECT ...
within the procedure. This change MADE NO DIFFERENCE to the problem
or performance - which I think adds credance to the idea that there is
very little performance difference between putting code inside a
stored procedure versus building huge select statements.


Geoff Worboys
Telesis Computing


Yahoo! Groups Sponsor

Lose 20 lbs by July 9th!


Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service.



[Non-text portions of this message have been removed]