Click here to Skip to main content
14,740,380 members
Home » Discussions » C#
   

C#

 
QuestionGraphicsPath Pin
cocoonwls4-Aug-08 19:32
Membercocoonwls4-Aug-08 19:32 
AnswerRe: GraphicsPath Pin
cocoonwls5-Aug-08 0:53
Membercocoonwls5-Aug-08 0:53 
AnswerRe: GraphicsPath Pin
mav.northwind5-Aug-08 2:57
Membermav.northwind5-Aug-08 2:57 
GeneralRe: GraphicsPath Pin
cocoonwls5-Aug-08 18:17
Membercocoonwls5-Aug-08 18:17 
QuestionBitshifting question Pin
threemp34-Aug-08 16:10
Memberthreemp34-Aug-08 16:10 
AnswerRe: Bitshifting question Pin
Mark Salsbery4-Aug-08 16:26
MemberMark Salsbery4-Aug-08 16:26 
GeneralI need to explain to our VP why trying to do everything in Stored Procedures is a bad idea [modified] Pin
Togakangaroo4-Aug-08 14:55
MemberTogakangaroo4-Aug-08 14:55 
GeneralRe: I need to explain to our VP why trying to do everything in Stored Procedures is a bad idea Pin
PIEBALDconsult4-Aug-08 18:15
professionalPIEBALDconsult4-Aug-08 18:15 
I'm glad I'm not the only one here who dislikes stored procedures (or at least the blind overuse of them).

Let me preface:
Before .net I did most of my database application programming with SQL embedded in C, either with Oracle (PRO*C) or RDB.
But at one point I had to work on a project that used SQL Server (6, as I recall) and ESQL was not yet available so we had to use a horrible (consultant-written) library of ODBC routines. The result was that the only way to have transactions was to use stored procedures.
No one was happy about it and thought very nasty thoughts about the sales monkeys who said we'd do it.


While I realize that newer versions of SQL Server offer better security and reliability, I continue to distrust stored procedures:

A) While installing some of my stuff at a client's site, the client asked if I could make a slight change; I did, it was just a small change to a stored procedure... a change to production code outside of source control. I did remember to check in the updated code when I returned to the office, but I realized that any (properly authorized) person could change any of the procedures, perhaps maliciously. Such changes are much more difficult with compiled code.

B) Several times a stored procedure just disappeared! I have no idea how. This either doesn't happen with compiled code, or at least the program won't run at all if it does.


Performance:

A) I can neither confirm nor deny whether or not procedures are faster than queries in strings. I suspect they are, though at best it's only due to preparation time.
Unless you have a procedure that gets called a whole lot (which may well be the case with a Web page), I doubt it matters.
If a procedure gets called less frequently then you're not benefiting from any performance improvement.
If the preparing of a particular SQL statement proves to be a bottleneck, by all means wrap it in a stored procedure.

B) But also remember that a parameterized ADO.net SqlCommand, once prepared, can be reused many times.
So if you have a Windows Service that needs to execute a particular statement many times during its execution (which should last months) it need only prepare the statement once, just like a stored procedure.
On the other hand, if you repeatedly create and dispose an SqlCommand that executes a stored procedure lose out on much of the benefit of having pre-compiled the code in the stored procedure.

C) One of the benefits of a stored procedure is that you can perform some logic on the data without passing it out of and back into the database, but I wonder what percentage of stored procedure actually involve that.

D) Add to that the recent post that pointed out that if you begin the names of your stored procedures with "sp_", SQL Server wastes time looking for it among the system procedures.

E) I have also worked with people who insisted that "metadata" (stored procedured, functions, etc.) bogged down the database. (Crazy as that seems.)


Other points:

A) If I have an application installed at several client sites I can check the installed versions with directory commands.

B) I can deploy a change to client systems with copy.

C) A database restore won't wipe out SQL code in compiled code.

D) I have no idea why someone would write a stored procedure that merely returns a table of data; use a view for that.
I also use quite a few inline table-valued functions; they combine the best of both views and stored procedures.


Closing:

Don't just use stored procedures unthinkingly:

If the statement is a proven bottleneck, first try improving the statement, only make it a stored procedure as a last resort.
A pre-compiled piece of crap is little better than an ad hoc piece of crap. (And it may be more visible to the client.)

Conceptually, a "procedure" has more than one step, and perhaps control statements; if your code is one simple statement I doubt it should be a stored procedure.


"Use the right tool for the right job." -- Scotty et al
GeneralRe: I need to explain to our VP why trying to do everything in Stored Procedures is a bad idea Pin
N a v a n e e t h4-Aug-08 19:26
MemberN a v a n e e t h4-Aug-08 19:26 
GeneralRe: I need to explain to our VP why trying to do everything in Stored Procedures is a bad idea Pin
Mycroft Holmes4-Aug-08 21:31
professionalMycroft Holmes4-Aug-08 21:31 
GeneralRe: I need to explain to our VP why trying to do everything in Stored Procedures is a bad idea Pin
N a v a n e e t h4-Aug-08 19:28
MemberN a v a n e e t h4-Aug-08 19:28 
GeneralRe: I need to explain to our VP why trying to do everything in Stored Procedures is a bad idea Pin
Togakangaroo5-Aug-08 3:16
MemberTogakangaroo5-Aug-08 3:16 
GeneralRe: I need to explain to our VP why trying to do everything in Stored Procedures is a bad idea Pin
darkelv4-Aug-08 20:22
Memberdarkelv4-Aug-08 20:22 
GeneralRe: I need to explain to our VP why trying to do everything in Stored Procedures is a bad idea Pin
leppie4-Aug-08 21:07
Memberleppie4-Aug-08 21:07 
JokeRe: I need to explain to our VP why trying to do everything in Stored Procedures is a bad idea Pin
Harvey Saayman4-Aug-08 23:58
MemberHarvey Saayman4-Aug-08 23:58 
GeneralRe: I need to explain to our VP why trying to do everything in Stored Procedures is a bad idea Pin
PIEBALDconsult5-Aug-08 4:14
professionalPIEBALDconsult5-Aug-08 4:14 
GeneralRe: I need to explain to our VP why trying to do everything in Stored Procedures is a bad idea Pin
leppie5-Aug-08 4:56
Memberleppie5-Aug-08 4:56 
GeneralRe: I need to explain to our VP why trying to do everything in Stored Procedures is a bad idea Pin
Ashfield4-Aug-08 22:16
MemberAshfield4-Aug-08 22:16 
GeneralRe: I need to explain to our VP why trying to do everything in Stored Procedures is a bad idea Pin
PIEBALDconsult5-Aug-08 4:24
professionalPIEBALDconsult5-Aug-08 4:24 
GeneralRe: I need to explain to our VP why trying to do everything in Stored Procedures is a bad idea Pin
Ashfield5-Aug-08 9:26
MemberAshfield5-Aug-08 9:26 
GeneralRe: I need to explain to our VP why trying to do everything in Stored Procedures is a bad idea Pin
Guffa4-Aug-08 22:42
MemberGuffa4-Aug-08 22:42 
GeneralRe: I need to explain to our VP why trying to do everything in Stored Procedures is a bad idea Pin
PIEBALDconsult5-Aug-08 4:26
professionalPIEBALDconsult5-Aug-08 4:26 
GeneralRe: I need to explain to our VP why trying to do everything in Stored Procedures is a bad idea Pin
nelsonpaixao5-Aug-08 14:32
Membernelsonpaixao5-Aug-08 14:32 
QuestionHow to finalize an object with an internal Thread ? Pin
ofeririko4-Aug-08 12:38
Memberofeririko4-Aug-08 12:38 
AnswerRe: How to finalize an object with an internal Thread ? Pin
led mike4-Aug-08 13:09
Memberled mike4-Aug-08 13:09 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.