Hi Charlie

I'm glad your SP app is fast. But how can you be so sure it is faster than RVs? We run a breast screening service for a population of 1 million using mobile vans and 36.6k modems using RVs. It's incredibly fast.

Here's what I did to check this out. I got my chaps to do a SP for a fairly simple requirement we use all the time- pulling lists of maybe 20 document rows from a SQL database for a particular patient.

We ran it for 20,000 iterations using random keys.

We did it again using RVs.

The difference for 20,000 iterations was less then five minutes. This is with SQL Server 7 with ODBC set up efficiently.

As an average user might use this facility every 5 minutes or so in the course of a day, the daily saving is a fraction of a second.

I'm afraid I do not perceive any advantage whatever in real life terms. We will not be moving to SPs.

I agree I have not tested with 100 people hitting the system simultaneously, which would be a useful test. But then, there are others who have not tested at all.

You comment on flexibility. I'm afraid SPs are not more flexible. My RV version also works against Oracle, Cache and Sybase. That's flexible. With SPs, you would need to write another 3 versions to achieve this. You would need to develop or purchase expertise in each backend. This costs, especially with Oracle.

If you mean that using SPs puts the logic on the server and removes complexity at the client, there are lots of ways to do that. Actually it might be "better" to place such logic in a 3rd tier rather than the database if you are really interested in flexibility.

It has been mooted that some clients might require SPs. In that case you'd use SPs but an fair response would be that SPs are used because they have to be, not because they are "superior". Such a response would not label others "lousy" either. Others might say they use SPs because they like them. That's honest.

As for being "old school"; well, I'm not ashamed to be old school if that is what my way of working is. I would happily use SPs if anybody could give me more than an "everybody knows" reason. Till then, RVs work, they are easy to maintain, do not require (expensive) understanding of backends, prevent hijack of my ability to choose by tying me into just one way... I see heaps of advantages.

IMHO a lot of complexity in IT today is invented. A "need" to use SPs creates an extra layer of complexity that in the end is passed onto the customer as a cost. That's great for developers who need the work, but IMHO it does our industry no good and in the end engenders a backlash. IMHO we need to be ever watchful to ensure that we do not become part of the "Emperor's Clothes Admirers Club" when we are the sellers of the "clothes".

Regards

JR