I think the performance gains are obtained when the stored procedure updates/interferes with the result set.

In that case, the performance gains can be significative.