FreeTDS Development Group

[Norman Palardy <norm AT kirais.com>]

> I'll respectfully disagree that using stored procs is a bad idea.
> 
> (yes it hurts portability, but it allows for better performance, for
> data hiding, and for single location query tuning).

I agree with the data hiding and location query tuning, but not the
performance item.

We have very large very active tables (something like 20 million rows in one
of them) and stored procs are not great in this situation because of how
they cache query plans. This is on Sybase 11.9.x and Sybase 12.x

The only way to get around this is to create the proc with recompile option,
which effectively makes it run about the same as an embedded SQL query.

There ARE situations where procs can be enormous performance boosts, but
they tend to be on tables where the data distribution is not changing much.

The single biggest boost we've seen on these tables has come from
re-examining the indexes used by queries and recreating them so the indexes
are used more frequently by more queries.