Hi Steve,

>> 1.)Stored procs are more portable.
>> If we have "N" front end applications that mostly have different motives on >the database, then we have app-specific code for "N" apps in stored >procedures, and not otherwise encapsulated with the clients or otherwise in >separate abstractions. All this to say that this isn't a silver bullet -- you >can never completely insulate layers from changes in other layers.

I think the whole idea of write once and sell many does not really work when it comes to operating systems or even implementations! If you have an app and are going to have different clients accessing the data, you must accommodate for it. So clearly additional code will have to written someplace, whether client or backend. Stored procs leave the data access part on the back end so the logic of getting to the data will be the same regardless of how it’s presented to the client. This just works for my mind set.

>I do know this: any claim that SPs reduce coupling with client layers is obviously >bogus. So in summary, is there a better architecture than blind use of vanilla SPs that >eliminate the coupling between the database layer and its clients?

I think you get coupling regardless of architecture. It just depends on where you want it and how you prefer to work with it. If anything, working with stored procs is more work and not less.

>On a related subject (maybe this deserves another thread), I'm theoretically troubled by >the notion that with SPs you need to know about the needs and implementations of >clients.

Not sure why you have an issue with this. We always have to insert, update and delete. The client side does not have to know what the implementation is. Build an object, implement a few methods and you have basic data manipulation.

>> 2)... If I’m using stored procs, then I’m looking for everything in the same place. Like triggers for example. They’re on the server. They can’t be in VFP since VFP is the interface. Additional data manipulation is simplified by calling other stored procs on the server.

>Actually, the SP's are the interface, VFP is the client. The code is not in one place, it's in >two places.

Well, the code is in three places actually. I use a middle tier with VFP front end and SQL on back end. Each does it’s job. If SQL server is going to be doing something, I want that control to remain on the server. Codebases are always dynamic. I think three tiers makes it simpler.

>> 4.) I think stored procedures are easier to debug and develop. I see what’s going on and can make other adjustments accordingly.

>That's not always going to be true. Of course more elaborate SP's are easier to develop >in the database <s> but the 99% use case, creating a SQL statement, is as easily done in >either layer or in the query analyzer and thereafter pasted in one place or another.

This is developer thing. My preference. You can always produce cases where one implementation for a given case is better than something else.

>> 5. It is my opinion that each table should have a primary key and clustered index in SQL Server. The maintenance issues for indexes are minimal and SQL Server is built to handle this. Table scans even use clustered indexes.

>>I'm not sure if you saw my posts with Rod. Our situation is clearly not benefiting from >>a clustered index on PK or anything else. I can imagine other situations in science and >>engineering where a clustered might be ill advised. Simulations with transactional >>tables with N-dimensional data, or multivariate timeseries data come to mind, but I >>can't be sure because I don't play there much anymore.

Yes, I saw that. It does sound like there’s something odd going on with the app. 800,000 rows is nothing for SQL Server. I never seen a case where clustered index is ill advised. Not saying it’s not possible, just never seen the case. SQL2000 in particular is highly optimized to handle indexes. In fact the old adage of don’t index what you don’t need is starting to go away. I took a quick look at my reference materials and nothing suggests otherwise.

Are you using a lot of varchars? I’ve seen slow performance result from a lot of updates to this data type. Are you using the rollup operator in queries? I’d like to help you with this when I get back from Germany.

>>Generalities are generally true. Anything more and I'm just naturally skeptical <s>.

It’s only the smart people who understand there are exceptions to everything in software development. There are always sever ways of doing something. You make decisions based on your knowledge at the time, go with it, monitor and make changes as more info becomes available. That said, we all begin with rules of thumb, best practices, etc.

- Val Matison