I don’t know about Oracle, but in MS SQL server using a stored procedure does not offer any performance gain. It’s also worth noting that limiting the number of records coming back form the DB certainly reduces the about of data transferred to the client, but has little effect on the execution plan IF you are ordering the result set since the database still need to collect all the records first and sort them before it takes the top 1000.
Thanks!
~Brad
ColdBox Platform Evangelist
Ortus Solutions, Corp
E-mail: brad@coldbox.org
ColdBox Platform: http://www.coldbox.org
Blog: http://www.codersrevolution.com