August 1, 2008 at 6:50 am
Jezemine has a good point - put SQL in code and there is one more place that hast to be scanned whenever tables changes are made
IMHO I think it is a lot easier to enforce the "All SQL in stored procedures" rule than it is in the "all data access in data access project" rule
I have only worked in small shops and most of the application under development was the work of one programmer
he / she may or may not still be around
the boss wants the fixes fast and could give a rat's *** about niceties like best practices - he / she just wants to sell more widgets
August 1, 2008 at 8:27 am
I have read through most of the comments here today and found that some like sprocs and some don't but this is not a black and white answer. For those with applications that use DAL and BLL it's cool. Simple queries can be done through that but you're not gonna be able to write an insert procedure in a DAL for complicated accounting transactions for instance. I am running a database where our system runs the company's accounting. Automatically you have to create records that debit this account and credit another and find an income account from the general ledger and make sure another account exists. Very big (wordy) procedures that, if you want to do a good job, you can only do in a stored procedure.
What is everybody's problem with cursors! How many articles do I still have to read where people talk about how bad cursors are! What is the difference if I read some data in a temp table (# or ## or @) and then use a while loop to read the data and do whatever is needed to be done. In our database, out of +- 500 sprocs about 10 has cursors so I don't use them for everything I do but sometimes they come in quite handy. Performance is the main thing we have to look after and as long as we make sure of that then the rest is unimportant.
Database Administrator. You make it sound like DBA's is a superior race and that developers are the lower class! Well, I have news for you. I am a developer (training to be a DBA, still developing!) and proud of it and I am going to write in for Exceptional DBA next year and then we'll see!
:D:D:D:D:D:D:D:D:D:D:D:D:D:D:D:D:D:D:D:D
:-PManie Verster
Developer
Johannesburg
South Africa
I can do all things through Christ who strengthens me. - Holy Bible
I am a man of fixed and unbending principles, the first of which is to be flexible at all times. - Everett Mckinley Dirkson (Well, I am trying. - Manie Verster)
August 1, 2008 at 8:43 am
Manie: I could not have put better, do what is necessary but also make it suffcient.
Once its in the accounts ledgers you can't remove it, only jounal it.:P
Control is everything for accounting. 😀
Hiding under a desk from SSIS Implemenation Work :crazy:
August 1, 2008 at 8:48 am
Mannie, you were doing OK 'til you got to the cursors bit... still, as you say you are learning.
Silos are bad. People should talk to each other and try and learn from each other. Developers are often DBA's 'users', so you get the same attitudes that developers have to THEIR users. And users to developers. It helps noon to have an antagonistic attitude, but equally you need to respect others professional abilities. So developers need to listen when dba's say 'don't use cursors' and dba's need to listen when developers say 'We need to do X'. But in exactly the same way as developers need their users to communicate with them - don't tell us what to do. Tell us what you NEED. We then work out how to do it.
Cursors are bad. They don't scale, and there is always a better set based solution, that will perform better. Effectivly, you have this massivly optomised parrallel processing engine, and you are forcing it to work in a single-threaded mode. It may not cause a problem with what you have at the moment, but it's a bad habit to get into. I'm not surprised you like them, as coming from a development background, they are familier control structures - as you learn more in a dba role, you will learn how to think like a dba, as well as developer.
oh - and data integity is the most important thing - perfomance is a lot less important than this.
(thanks Shaun)
August 1, 2008 at 9:00 am
Oh oh double post TimJL
- edit button 😀
Hiding under a desk from SSIS Implemenation Work :crazy:
August 1, 2008 at 9:03 am
Developers, DBAs, architects, to say nothing of systems analysts, business analysts, testers (even users !) : We're all a team and we all need to work together. Silo's are sooooo 1990s. We may be stronger in some areas than others but efficient systems are balanced systems and need to be designed and built by people with a broad understanding across the spectrum. The current mantra is Agile - and that requires multi-skilling and teamwork. How can we come up with optimal designs if we confine ourselves to thinking about only one part of the whole system or seeing our colleagues as enemies who are trying to sabotage our personal fiefdom?
August 1, 2008 at 9:06 am
Yes marketing is the enemy not the other team members 😀
Hiding under a desk from SSIS Implemenation Work :crazy:
August 1, 2008 at 5:26 pm
Manie Verster (8/1/2008)
What is everybody's problem with cursors! How many articles do I still have to read where people talk about how bad cursors are! What is the difference if I read some data in a temp table (# or ## or @) and then use a while loop to read the data and do whatever is needed to be done. In our database, out of +- 500 sprocs about 10 has cursors so I don't use them for everything I do but sometimes they come in quite handy. Performance is the main thing we have to look after and as long as we make sure of that then the rest is unimportant.
I absolutely agree... replacing cursors with a Temp table and While Loop is futile and mostly a waste of time. If you use a "Firehose" cursor, you have the equivalent of a Temp table and While Loop except that the cursor code will bump the counter "auto-magically".
BUT... for the very performance reasons you stated, that's as far as I agree. 🙂 The only time you need a cursor and it's inherenent slothfulness is maybe when you need to do something across databases like some good ol' fashioned maintenance using "USE"... in other words, something that will control multiple set-based dips on the database and not RBAR. Most people agree that cursors are a bad thing because they can be a little tough on resources and they're slower than set based code. Being proper Developers or DBA's, they'll cough up the ol' "it depends" furball and say that you should only use a cursor when nothing else will work even if the cursor does become RBAR. The problem with that is...
... they give up too bloody easily! 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
August 1, 2008 at 5:46 pm
Jeff Moden (8/1/2008)
Manie Verster (8/1/2008)
What is everybody's problem with cursors! How many articles do I still have to read where people talk about how bad cursors are! What is the difference if I read some data in a temp table (# or ## or @) and then use a while loop to read the data and do whatever is needed to be done. In our database, out of +- 500 sprocs about 10 has cursors so I don't use them for everything I do but sometimes they come in quite handy. Performance is the main thing we have to look after and as long as we make sure of that then the rest is unimportant.I absolutely agree... replacing cursors with a While Loop is futile. If you use a "Firehose" cursor, you have the equivalent of a Temp table and While Loop except that the cursor code will bump the counter "auto-magically".
BUT... for the very performance reasons you stated, that's as far as I agree. 🙂 The only time you need a cursor and it's inherenent slothfulness is maybe when you need to do something across databases like some good ol' fashioned maintenance using "USE"... in other words, something that will control multiple set-based dips on the database and not RBAR. Most people agree that cursors are a bad thing because they can be a little tough on resources and they're slower than set based code. Being proper Developers or DBA's, they'll cough up the ol' "it depends" furball and say that you should only use a cursor when nothing else will work even if the cursor does become RBAR. The problem with that is...
... they give up too bloody easily! 😉
As long as you're not hung up on performance, then cursors must be fine, since they help with readability...hmm no, not really, since they add more useless code and divert from the real purpose...
But they're good for blocking...increasing blocking that is....
But at least now - you can fully control what is happening to each individual row (actually - no, you're usually not, you're simply coding by exception, instead of finding the errors BEFORE they happen).
But - the execution plan tells me that the cursor is more efficient than the set based code. (actually - no, it's telling you that ONE ITERATION might be more performant than the code that will do ALL of the updates. The exec plan has no clue how many iterations the cursor might need, so it just doesn't even consider that as a factor in the cost calc.)
So remind me - WHAT is good about cursors again?
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
August 1, 2008 at 7:51 pm
Matt Miller (8/1/2008)
As long as you're not hung up on performance, then cursors must be fine, since they help with readability...hmm no, not really, since they add more useless code and divert from the real purpose...But they're good for blocking...increasing blocking that is....
But at least now - you can fully control what is happening to each individual row (actually - no, you're usually not, you're simply coding by exception, instead of finding the errors BEFORE they happen).
But - the execution plan tells me that the cursor is more efficient than the set based code. (actually - no, it's telling you that ONE ITERATION might be more performant than the code that will do ALL of the updates. The exec plan has no clue how many iterations the cursor might need, so it just doesn't even consider that as a factor in the cost calc.)
So remind me - WHAT is good about cursors again?
BWAA-HAA! Don't need cursors when you have CLR's.... (wait for it...) :):D:P:w00t::hehe:
--Jeff Moden
Change is inevitable... Change for the better is not.
August 1, 2008 at 9:25 pm
BWAA-HAA! Don't need cursors when you have CLR's.
Didn't want you to wait too long Jeff 😎
--Paul Hunter
August 4, 2008 at 3:00 am
Good morning people,
Having just been sent this link I thought you may want to hear the argument from a Web Developers point of view.
I have recently developed a complex website application which relies on a smooth running RDBMS. As the project specification was created it became clear that there were 2 tiers of basic architecture, Website & Dbase. As it turns out myself and the Database Admin worked on this together.
Now we both knew the limitations of our expertise despite both being able to complete the task individually. However by splitting the task into the two tiers and using Stored Procedures as the interface our development time in comparison to similar work I have done was reduced by over 50%.
It is simply a matter of separation of concerns. I built a robust web interface, while the DB Admin built a solid database and by following an agreed naming convention everything came together in a matter of hours when we came to testing.
Several things concerned me about the posted topic:
Security: The use of SProcs does increase security... why would anyone argue that this is a bad thing in a world where governments can lose millions of pieces of sensitive information in seconds. DVLA & Child Benefit Office to name 2 recent ones.
Writing 3-4 SProcs per table: Simply not true. The only stored procedures created were specifically written to perform a task required. As I presume you all enjoy normalising tables I'm sure you understand there simply would be no point writing SProcs for many of them.
Code Reuse doesn't work: Of course it does. Arguing that you would have to rewrite large sections of code because you don't understand it is just laughable. If this was actually the case I would seriously consider if I wanted to continue employing that person.
In conclusion:
My opinion is that Stored Procedures have saved a massive amount of development time, improved security and allowed a separation of concerns between two often conflicting areas of Web Development.
Have a lovely day with whatever you are doing!
August 4, 2008 at 5:25 am
Dex (8/4/2008)
As it turns out myself and the Database Admin worked on this together.
Now we're talking... great post.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 4, 2008 at 6:03 am
"... arguing that code reuse doesn't work.." -- I'm not sure whether that refers to stored procs useful for only one application in a database that supports more than one application -- or whether the reference is to (object-oriented) application code that isn't easy to modify when the application must be changed.
Working as a consultant, I have had engagements at several companies using applications developed by "consultants employed two years ago by the west coast office" etc. The downside of scalability is that entire divisions of conglomerates are using applications whose developers never had a conversation with the people now supporting the application. And NO documentation. What documentation did exist was written by and for **the development team** and nobody supporting the application has it now. That is, no (effective) planning for the future by the management that ordered the application.
Stored procs are right there in the database, easy to document after the fact, easy to change when a new development team goes to work.
_________________
"Look, those sheep have been shorn."
data analyst replies, "On the sides that we can see.."
August 4, 2008 at 9:21 am
Dex (8/4/2008)
Code Reuse doesn't work: Of course it does. Arguing that you would have to rewrite large sections of code because you don't understand it is just laughable. If this was actually the case I would seriously consider if I wanted to continue employing that person.
Having to rewrite code because you can't read what it does? What are your writing in? PERL? :w00t: That's about the only one-way language I know of. You get over Joe Celko's nine things limit in a hurry.
ATBCharles Kincaid
Viewing 15 posts - 121 through 135 (of 160 total)
You must be logged in to reply to this topic. Login to reply