>Hi,
>
>From your experience how much of a speed difference is running a SQL statement with sql passthru (ex: "=SQLEXECT('Select * FROM orders')") or calling a stored procedure with sql passtru (ex: "=SQLEXEC({CALL usp_GetOrders})")) where the SQL Stored procedure contains that select statement? I know the store procedure is faster, my question is how much faster? By the way, the back end is SQL 2000.
>
>Thanks.


Hi Roy,

While Stored Procs are faster - the speed improvement may not be noticebly faster. Then again, performance is the LAST reason you use stored procs. You use stored procs because you want:

1. Security
2. Scaleability
3. Program/Data independence
4. Greater Flexiblity
5. Development tool indendence
6. Better maintainability

From a performance standpoint, it can definitely be much better if instead of a proc - you make successive calls to bring more data than is actually required - only to do more filtering on the client. If you handle all of these operations on the server, generally, you will have better performance.

It is a myth by the way that Fox handles data faster than SQL Server - it doesn't....


My default rule is that everything should be a stored proc. That said, there are times when rending SQL from the client is the most feasible way to go. It is and should be the rare exception. In most situations however, I think you will find that the only variables in a query are those that can be handed in with parameters. And, if you have conditional logic, you can handle that in T-SQL - and return the correct result set.