>I’m trying to set standards for the usage of SQL stored procedures and could use some opinions about it. We are a VFP shop in the process of moving VFP data over to SQL. For the moment, we plan to continue to have middle and front ends remain in VFP.
>
>One view point I hear is that we should simply have all access to SQL tables be performed through calls to SPs. That this allows for optimum performance and also allows the DBA to easily manage table structural changes because he can use SQL procedures to easily find dependencies upon any field. If SQL pass through was used, he would no longer be able to do this.
>
>Another view though is to spread the SELECT, UPDATE, INSERT code as is appropriate throughout the application. Ie. INSERT and UPDATE code in the business objects, and SELECTs where ever needed, but primary for reporting. Basically, if what you need from SQL server can be accomplished in a single statement, why bother creating a stored procedure for each one? This approach is seen as having a huge developer productivity benefit, since the application has 100s of SELECT statements that would be unique to specific reports within the system.
>
>The common agreements we have between these veiw points is that in the case of code that may be reused, they want it in one place. The first view advocating that spot be in SPs, the second view being in business objects.
>
>Stored procedures are not required for security purposes to our tables. Security can be adequately managed in other ways. Performance issues are generally of the end to end through put nature and not in the sense of 1000s of people simultaneously running the code.
>
>Please jump in here with your views and any pros and cons you see.
>
>Thanks,
>Anthony Letts

Anthony,
Each and every SQLExec statement send to the server should be parsed, checked and compiled. It might be done once for a given session via SQLPrepare. OTOH the same statement(s) if were on a stored procedure then it'd be compiled only the first time executed. In practice IMHO not a big difference from SQLPrepare. However this way it'd mean I should keep my handle for the life of my app. It in a way looks like I created the procedure as a temp private stored procedure. IOW handle lifetime would affect the decision. Another thing is using single or batch statements in a single call. No wonder a stored procedure is a target for the batch. None of the apps I know use only pure batch calls. Some might need single statements at times. These are perfect candidates not to become a stored procedure ever.
Sometimes I might even want to have stored procedure for a single statement. ie: say I want to send and store and image in server. Image data is binary but I can't send the binary data directly (though I might think I'm sending as binary), it should be converted to hex representation that doubles the size and on the server end converted back to binary halving the size. If this were a stored procedure then data would travel as binary from start to end and conversion would not be needed.
Another thing if it'd a stored proc or not depends on where would you keep some logic. Say a calculation done to get a scalar value - a common sample would be customer's credit limit. If it's in a stored procedure you wouldn't need a change on app side if that calculation changes. If on app side then all apps would need the change (BTW I'm getting some of these from -Inside MS SQL server, Soukup&Delaney,MSPress- but mixing with my own thoughts) that uses this calculation. However this is more for nonOOPers (this part is an objection to above that is based on the book). You could well have that logic code in a class and all you need is to make the change in the class. Of course this would depend on app creators despite any SQL server administrator-implementor could do it on server. It depends how you'd want to look at it.
Anyways I'd think simple, put aside performance and decide how much of this I could do with stored procedures if it were pure local VFP DBC and tables (taking into account SQL server's SQL is much richer than VFP's). And possibly I'd do all stored procedures if I can. I'd do that way not based on performance but thinking I might tomorrow decide to create a tier in a different language, use OLEDB calls intead of ODBC based ones etc. Of course I could do that with VFP today too (and yesterday even in VFP5). BTW single statement calls could and should stay on VFP side. They could be written in any language and not a compelling reason to be a stored procedure (IMHO). Also I'd think that stored procedures how good and fast they're sometimes some tricky data organization for a representation layer cannot be processed as fast as VFP does once it gets an initial set to work with. One objection to this would be VFP should then get a larger set, yes, but processing that set and sending a small set to VFP might sometimes be more expensive than sending large data. I might even think of an extreme scenario :
-Initial data set (unprocessed) to work on is 100K records
-After processing end data set would be 120K
-SQL server stored procedure processes very very fast and completes in 0.1 secs
-On the average 100K data delivered to client in 1 sec, 120K 1.2 sec
-VFP processes much slower and completes processing in 1 sec

-Server has single processor and gets 20 client hits at some time requesting data via this SP

With stored procs :
If I'm the lucky client served first I would get processed data back in 1.3 secs.
If I'm the red headed step child 20th served client it'd mean 20*1.3 = 26 secs for me.

W/o stored procedure :
1st client served would get the data in 1 sec+process in 1 sec = 2secs
20th client served would get the data in 20*1 secs+process in 1 sec = 21secs

If there were some kind of multithreading here actually it wouldn't help the clients and make that worse. Only clients would think they were being served almost equally possibly all getting results in more than 26 secs.

What if I had zillion processors on the server. Well then I might even ignore the process time totally but still I'd get back 120K which means 24 secs for 20th client. But I also have zillion network cards, oh yes then all clients could be served in no more than 1.3 secs. But hang on w/o stored procedure still clients could be served in 2 secs. Go figure :)

In summary stored procedure is something I never would want to be w/o them but I'd be cautious to use stored procedure when cost outweighs the benefits. That cost sometimes might even just mean my abilities writing SPT better than SP or vice versa.

PS: Also if I think initial parsing and compiling wouldn't be much of a concern, I might totally go with SPT where I send 'batches' in one SPT call. It'd be similar to SP. If I hit something that should definitely be dynamic, it'd be much better for me to do with SPT batch than creating SP each time I needed it. Rare but happens to us all :)
Also refer to wiki site for thoughts about SPT,RV,SP as well as some benchmarkings.
Cetin