Hi,

> I'd like to know what is a better choice in general, selectable SP or
> VIEW? I didn't experienced any performance drawbacks when using SPs
> instead of views, but what is your opinion? I even noticed better plan
> were used for procedures, than views. I would prefer SPs, because of the
> recompiling issue of views when there are dependencies. With SPs this go
> away and they can be compiled whenever I want.

If you do a WHERE clause on your View, you will get a much better
plan compared to a SP. With an SP, first the SP itself will be executed
and all rows fetched before any WHERE clause is applied (this counts
for JOINs as well), while with a View, indices on the base tables can
be used to modify the query plan and this is pushed down to the select
statement of the View.

So, it depends :-)

With regards,

Martijn Tonies
Upscene Productions
http://www.upscene.com

Download Database Workbench for Oracle, MS SQL Server, Sybase SQL
Anywhere, MySQL, InterBase, NexusDB and Firebird!

Database questions? Check the forum:
http://www.databasedevelopmentforum.com