Embrace Stored Procedures
-
Yes, but, in a data related task, what is the heaviest thing ?
Accessing data or doing business logic ?
Why not to have a database containing data and another containing all the rest (stored procedures, user defined functions, views).
The first one will be always the bottleneck, the second can be (if REALLY necessary) scaled out.
-
My experience has been that, for both data warehouses and transactional databases, disk I/O is the biggest bottleneck. If you're maxing out CPU, then look for recompiles or cursors.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
-
Jeff Moden (4/20/2015)
Yet Another DBA (4/20/2015)
Jeff Moden (4/18/2015)
I read Rob's article when it first came out. What a refreshing change it was from the dogma that I've been reading about for the previous several years before that.Likewise
To coin a phrase, "If you don't think the database server is the center of the world, turn it off and see what happens." 😛
"So what the CEOs, Sales and Project managers email are not in your database then the world will keep spinning!" The old argument between Exchange Admins and DBAs
Heh... if the database server is down, they'll certainly have a whole lot to talk about. 😀
I'm not sure if you are serious or just provocative for fun.
Lets take your agrument a bit further, if you are the guy that turns of the db I'll recreate it else where and since I have backup redundancies it wont take much of an effort.
The db in most systems is next to useless without it's logical components and user interfaces. In today's world, in the companies I've seen in Sweden, only old school people who likes to focus only on one area (db) puts logic into the database. All thou to be fair, data processing and calculations could also be named logic, but you dont want the logical components for validation and busies rules, temporary campaigns etc in the database now do you? That would be clumsy compared to the simple alternatives.
The database is important, and I like working with them, but way too many people get too attached I'd say and thus does not look up to see the horizon and can not put together the big picture.
Do you sometimes forget that the db is not onlly meant to be an effective master piece but meant to be there to severe the interests of a company / organization?
Now how's that for provocative form you're point of view? 😉
-
IceDread (4/20/2015)
Jeff Moden (4/20/2015)
Yet Another DBA (4/20/2015)
Jeff Moden (4/18/2015)
I read Rob's article when it first came out. What a refreshing change it was from the dogma that I've been reading about for the previous several years before that.Likewise
To coin a phrase, "If you don't think the database server is the center of the world, turn it off and see what happens." 😛
"So what the CEOs, Sales and Project managers email are not in your database then the world will keep spinning!" The old argument between Exchange Admins and DBAs
Heh... if the database server is down, they'll certainly have a whole lot to talk about. 😀
I'm not sure if you are serious or just provocative for fun.
Probably both!!!
Lets take your agrument a bit further, if you are the guy that turns of the db I'll recreate it else where and since I have backup redundancies it wont take much of an effort.
The db in most systems is next to useless without it's logical components and user interfaces. In today's world, in the companies I've seen in Sweden, only old school people who likes to focus only on one area (db) puts logic into the database. All thou to be fair, data processing and calculations could also be named logic, but you dont want the logical components for validation and busies rules, temporary campaigns etc in the database now do you? That would be clumsy compared to the simple alternatives.
The classic chicken and egg argument.
The logical components and user interfaces would be useless without the database.
The database is important, and I like working with them, but way too many people get too attached I'd say and thus does not look up to see the horizon and can not put together the big picture.
Do you sometimes forget that the db is not onlly meant to be an effective master piece but meant to be there to severe the interests of a company / organization?
Now how's that for provocative form you're point of view? 😉
The function of a relational database is to enforce the business rules. This is an obtuse concept to most folks. This is a lost concept to many developers and DBA's.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/ -
The function of a relational database is to enforce the business rules
No, it's not. The function of the database is to store information in a manner from which it can be efficeintly maintained and retrieved. I'm sure more can be said, but one if them is *not* enfore the business rules.
-
"Best Practices" is so overused. And most of the time when I hear someone use the phrase, it has been someone who has no idea about what they are talking about. Mostly driveling sales people. Dilbert has it down:
The more you are prepared, the less you need it.
-
"Best Practices" is so overused
This is a response I've seen from designers who don't think primary and foreign keys are important. And that's just the start. And it has often created a mess that I (and others who have commented on this) have had to clean up. I agree that sometimes a careful look at best practices can result in them not holding up, or not being worth the effort. But too often people seem to me to want to not bother to learn and think about their craft.
-
Yes, it is.
It's badly stated , but business rules and relational database design are/should be (quite) the two faces of the same coin.
Me, I always stated that, given a database correctly designed (using some good methodology, IDEF1X, for example), I could understand how the company works (a lot of its business rules).
More: a database designed by the developers, designed solely for the developers needs it's usually a mess (I always menace the developers to cut their hands if they put them on the database).
Unfortunately things go in the opposite way.
I'm full of examples of queries written in a desperate way (full of DISTINCT, GROUP BY, loops in the JOIN) only because of that.
-
And to quote Adam Machanic:
"In my opinion, all external database access should occur through stored procedures."
Reference:
The more you are prepared, the less you need it.
-
"In my opinion, all external database access should occur through stored procedures."
I don't disagree with this out of hand, although if dogmatically adhered to rules out using batch updates. The discussion is not about whether or not to use stored procedures. When data retrieval is needed, it should be through a stored procedure. But when business rules need to be applied, it should be done in the middle tier. The results can either be passed to a stored procedure or result in an updated batch. Most of the time, it is done through a stored procedure. But the stored procedures only retrieve and pass and then receive and store the information. They do not do the heavy lifting. That we leave to the app servers.
-
RonKyle (4/20/2015)
The function of a relational database is to enforce the business rules
No, it's not. The function of the database is to store information in a manner from which it can be efficeintly maintained and retrieved. I'm sure more can be said, but one if them is *not* enfore the business rules.
Yes, it is.
Certainly a database can have whatever data stored in any number of ways that can be efficiently retrieved. But if there is no enforcement of what the data MEANS to the business, then the design is a mess. If the rules of a business are not considered in the design, then what's the point?
Does the term "verb phrase" mean anything?
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/ -
RonKyle (4/20/2015)
"Best Practices" is so overused
This is a response I've seen from designers who don't think primary and foreign keys are important. And that's just the start. And it has often created a mess that I (and others who have commented on this) have had to clean up. I agree that sometimes a careful look at best practices can result in them not holding up, or not being worth the effort. But too often people seem to me to want to not bother to learn and think about their craft.
+ 1000
My soap box.
Put a disk in, run the install. Ta-Da!!! I am a developer!
Put a disk in, run the install. Ta-Da!!! I have a database! So must be a DBA!
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/ -
"Business rules" has a meaning that is generally understood within the development community. It doesn't refer to the relationship between tables. At some level, that is a business rule. A department has employees. If an employee can only be a member of one department, the table structure be different from one where an employee can be a member of multiple departments. And I would never rely on programming code to enforce the proper relationship. This is correctly done in the data layer.
If we have the following business rule, however: If an employee has one unexcused absense within 60 days, x results; two, then y results. Those rules could be done in stored procedures. They are properly done in the middle tier. The database should not enforce these rules.
This is hardly a radical concept. Not only is there a good deal of documentation advocating this and the reason for it, I've seen first hand the improvements that come from moving business logic to the middle tier. This is especially true of looping and row by row application is required.
-
RonKyle (4/20/2015)
"Best Practices" is so overused
This is a response I've seen from designers who don't think primary and foreign keys are important. And that's just the start. And it has often created a mess that I (and others who have commented on this) have had to clean up. I agree that sometimes a careful look at best practices can result in them not holding up, or not being worth the effort. But too often people seem to me to want to not bother to learn and think about their craft.
Perhaps I was not clear. We should always strive to implement sound practices.
But, the phrase "Best Practices" is so overused.
And just what are the best practices for stored procedures? With even such a simple question we have far different opinions. So even with this we cannot come to any unified agreement as to what "Best Practices" should be with regards to stored procedures.
The more you are prepared, the less you need it.
-
The problem the original author of the articles is addressing is the cargo-cult education and obvious ignorance of a large subset of developers. It's frustrating dealing with Ruby/ORM/NoSQL/Cloud rockstars that run screaming from SQL or decades of experience.
Store procedures are the tool for many a business problem. I solved a vital business rule issue for my largest client by converting a utility program that took 28 minutes into an stored proc that took 12 seconds.
Viewing 15 posts - 31 through 45 (of 77 total)
You must be logged in to reply to this topic. Login to reply