Fog Creek Software
Discussion Board




Welcome! and rules

Joel on Software

.NET Books and Stored Procedures

Someone in the know please tell me why EVERYONE in the industry knows you don't write embedded SQL statements but instead use stored procedures explain to me why 90% of the books on .NET show it being done with embedded SQL statements???

WhereEverIRoam
Wednesday, May 11, 2005

It depends on the specific subject matter. If it's a book on data access best practices, then I'd be surprised. If it's a book on, say asp.net in general, then the point of the book is to teach you asp.net not best practices up and down the stack.

Calling a stored procedure, and showing the code for the stored procedure might distract you from the point the author is trying to make about the asp.net code.

rjc
Wednesday, May 11, 2005

Yep, +1 for rjc.

Everyone does it, even me.

Joel Spolsky
Thursday, May 12, 2005

Anyone have any recommendations for "best practices" related to this?

For example, how to you keep the stored procedures in sync with the source code?  How do you co-ordinate database changes with application updates? 

Chris F
Thursday, May 12, 2005

Two things...

1) Good communication

2) Good unit tests (to check for bad communication)

Jeff Mastry
Thursday, May 12, 2005

> If it's a book on, say asp.net in general, then the point of the book is to teach you asp.net not best practices up and down the stack.

Just thought to share that some of the more advanced ASP.NET text show best practices. 3-tiers, and yes only stored procedures are the only things you'll see in these properly written books. One I am reading now is: Marco Bellinaso and Kevin Hoffman's "ASP.NET Website Programming, C# Edition: Problems, Design, Solution".

Li-fan Chen
Thursday, May 12, 2005

> For example, how to you keep the stored procedures in sync with the source code?  How do you co-ordinate database changes with application updates? 

A book like Expert C# Business Object will tell you how to spread the tiers out in a way that's actually condusive towards writing maintainable code. I also found this article particularly helpful:

http://www.openmymind.net/NetDesignArticles.aspx

http://msdn.microsoft.com/asp.net/default.aspx?pull=/library/en-us/dnaspp/html/CustEntCls.asp

Li-fan Chen
Thursday, May 12, 2005

Oh goodness. I have to do this....

First Law of Distributed Object Design:

Don’t distribute your objects!

http://www.sdmagazine.com/documents/s=7897/sdm0304a/sdm0304a.htm?temp=src8Ub4XpT

Flame On! (Sorry, I saw a clip from the new FF movie last night...)

:)

Jeff Mastry
Thursday, May 12, 2005

"EVERYONE in the industry knows you don't write embedded SQL statements but instead use stored procedures"

Explain to me why EVERYONE in the industry thinks that stored procedures are ALWAYS a good idea?

We don't use stored procedures... When I say this people just roll their eyes. But there are sometimes good reasons not to use them. Let me explain:

We write Point of Sale software that runs in thousands of retail stores. That's thousands of individual databases across the country for a single retailer. Getting the schema synchronized and correct in all of those databases is hard enough. Now imagine having to handle versioning of stored procedures in all of those databases... yeah... you get the point. Embedded SQL statements allow me to use xcopy deployment to update a report. Stored procedures require me to execute SQL against thousands of database for the same simple change. Maintaining the number of stored procedures that we would need to have in all of those individual databases would be an absolute nightmare. The benefits of stored procedures quickly pale in comparison to the drawbacks in this type of scenario.

When I hear people argue that you should always use stored procedures it's because they are familiar with the single database at a single location structure. Stored procedures are great when you have physical access to the database at your location and a DBA on staff. However, they really stink for cases with thousands of installations where the smartest person on-site doesn't even know how to turn on the PC.

That's just my rant about stored procedures.... don't even get me started about replication, triggers, or views.  ;)

matt
Thursday, May 12, 2005

The On Rule (TM) that should be taught to every software developer is this:

There are no absolutes! (except for this rule)

Jeff Mastry
Thursday, May 12, 2005

Amen Jeff!

matt
Thursday, May 12, 2005

That should have been...

The One Rule (TM)

I hope that doesn't invalidate my trade mark.... :)

Jeff Mastry
Thursday, May 12, 2005

Very good point matt, will keep that in mind.

We are at the point where you can write C#/VB.NET stored procedures for SQL Server 2005, will that ever  be xcopy-friendly?

Li-fan Chen
Thursday, May 12, 2005

Matt has a good exception.

Besides that, I see alot of hackers on this forum  :)

WhereEverIRoam
Thursday, May 12, 2005

VS 2005 sp in code is really going to slow you down unless you have this big need to write alot of business logic in your sp.  (That is from a Microsoft developer).

ie. alot of string parsing.

However, I would debate at that point why you wouldn't just retrieve the data with a fast - forward cursor and do the logic in the business object

WhereEverIRoam
Thursday, May 12, 2005

Interresting... I don't really know. Having stored procedures be xcopy friendly would change things a little. However, we still probably wouldn't use them.

The big advantages of stored procedures really aren't there in our environment. For example: enhanced security. In my situation the user has physical access to both the database and the code. One could argue that it is actually easier for the user to get into the database to see/alter stored procedures than it is to see/alter embedded SQL in the application. To get at the database they could use any number of tools available for database editing. To get at the code they would need to reverse engineer it with something like Reflector (and making changes requires recompiling). This assumes of course that they could even find the SQL in the app. The bottom line is that in our environment the security issue is pretty much a wash because the user has physical access to the machine. This is clearly not the case for a web server type of situation.

Speed is also not an issue in our environment because SQL Server will cache the embedded SQL to give virtually identical performance. And since this is not a huge website with thousands of users making random querries, you are not likely to see the cache invalidated very often.

Anyway, as Jeff said there are no hard and fast rules about these kinds of things. Assuming that stored procedures are always best simply because somebody on the web says so is probably not going to yield the most optimal architecture in all circumstances. Although you might avoid a few people "rolling their eyes" at you...

matt
Thursday, May 12, 2005

It's more than 'a few people on the web' say to use it.

We have 40,000 users and started with 500.

If I would have used your approach, the application would have had to be re-written.

I still say, teach the right way.  I don't buy books anymore that teach bad practices, I've worked enough projects to know that  some shortcuts will bite you later.

WhereEverIRoam
Friday, May 13, 2005

Let me add as well:

Because of our use of stored procedures, we have greatly modularized our code and have great code reuse.  We have a separate layer involved.

Meanwhile the old asp pages with embedded sql are more difficult to manage, involve building complex queries with strings (this in and of itself is a horrible practice), full of twisted spaghetti code, etc...

I view the same with editing html vs using code behind.  I write entire projects without touching the html.  It's fast, easy - I use CSS stylesheets, etc... no drag and drop either, inherited pages and controls.

These applications have been very stable and produce far less bugs than the asp with the embedded sql.  The guy who did the embedded sql read some book and thought that was ok - it's now a mess.

WhereEverIRoam
Friday, May 13, 2005

"Meanwhile the old asp pages with embedded sql are more difficult to manage..."

This is an argument against collapsing logical layers - not so much against embedded SQL. Old-style ASP is hard to maintain because presentation, business and data logic are usually all smashed together in the .ASP file. Spaghetti code to the extreme.

A good data layer can use embedded SQL or stored procedures and still be maintainable. I've done both many times with no strong preference either way. Occasionally though, we have to deal with a DBA with God syndrome - and then we just go with embedded SQL to reduce friction.

You don't need stored procedures to have a well partitioned system. But they can help.

Jeff Mastry
Friday, May 13, 2005

*  Recent Topics

*  Fog Creek Home