Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Joe Celko on SP & Triggers

Author  Topic 

whitefang
Enterprise-Level Plonker Who's Not Wrong

272 Posts

Posted - 2009-05-12 : 12:09:28
Source: Celko, J., & Celko, J. Stored procedures: threat or menace?

quote:
Stored procedures are trouble in the making. Usually written in a procedural programming language (a third-generation language, or 3GL), stored procedures enable programmers to cast off the bonds of SQL and handle data sets as if they were data in sequential files. Not only do they violate the theoretical rules of databases, they can be inefficient and make portability difficult.


quote:

True, stored procedures allow you to put a sequence of SQL statements into a single block of code and pass parameters to those statements. But for this type of use, you could instead replace a stored procedure with a simple batch file. Such a batch file might even offer some advantages, since it could also access other programs from its position at the OS level.


quote:

Rule zero (yes, there is a rule zero, Dr. Codd's rules) says that ''for a system to qualify as a relational database management system, that system must use its relational facilities (exclusively) to manage the database.'' That excludes a second (procedural) access method to the database from the start.


quote:

In addition, database-optimizing compilers are complex and sophisticated, while procedural languages have simple compilers. T-SQL is a classic example: It's a one-pass compiler that can't handle a forward reference and needs a @ in front of local variables. There's not much code optimization in a one-pass compiler; about all you can do is rearrange the expressions a little and hope for the best.


quote:

Also, many databases automatically recompile stored procedures whenever they are used--even several times in the same session. The system itself might cause this by paging a stored procedure out of main storage and not retaining the executable. A worse situation is when stored procedures call each other and push each other out of main storage, guaranteeing maximum compiling times.


quote:

Whoa, Trigger!

Another disadvantage is that triggers and stored procedures do not communicate anything to the query optimizer. The user calls the stored procedure, so the database has no idea when it will execute, or even what code it contains. If you maintain referential integrity with the PRIMARY KEY, FOREIGN KEY...REFERENCES, and CHECK() clauses on the tables instead of in triggers, then the optimizer can pick up the predicates in the CHECK() clauses and use them in its execution plan.



But then again, you guys are more knowledgeable on SQL than Joe Celko himself.

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2009-05-12 : 12:21:15
Still at it then Access Enterprise Guru?
Go to Top of Page

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2009-05-12 : 12:49:05
First let's note that Celko is a true guru but he's often preaching a purely theoretical stuff that usually doesn't work in real world scenarios.

quote:
Stored procedures are trouble in the making. Usually written in a procedural programming language (a third-generation language, or 3GL), stored procedures enable programmers to cast off the bonds of SQL and handle data sets as if they were data in sequential files. Not only do they violate the theoretical rules of databases, they can be inefficient and make portability difficult.

Portability is way overrated. There are very few really high performance systems that ever port to a completely different database vendor.

quote:

True, stored procedures allow you to put a sequence of SQL statements into a single block of code and pass parameters to those statements. But for this type of use, you could instead replace a stored procedure with a simple batch file. Such a batch file might even offer some advantages, since it could also access other programs from its position at the OS level.

also true, but irrelevant. this means that you'd have to move the batch file along with your database which makes no sense for any kind of app. admin tasks a a different story.

quote:

Rule zero (yes, there is a rule zero, Dr. Codd's rules) says that ''for a system to qualify as a relational database management system, that system must use its relational facilities (exclusively) to manage the database.'' That excludes a second (procedural) access method to the database from the start.

don't even go there. NO db today is purely relational, so this argument is also irrelevant.

quote:

In addition, database-optimizing compilers are complex and sophisticated, while procedural languages have simple compilers. T-SQL is a classic example: It's a one-pass compiler that can't handle a forward reference and needs a @ in front of local variables. There's not much code optimization in a one-pass compiler; about all you can do is rearrange the expressions a little and hope for the best.

This is also irrelevant since it talks about the sproc code not it's execution.
i'll be the first to say that contrieved sprocs with a hundred if's etc are a bad thing.

quote:

Also, many databases automatically recompile stored procedures whenever they are used--even several times in the same session. The system itself might cause this by paging a stored procedure out of main storage and not retaining the executable. A worse situation is when stored procedures call each other and push each other out of main storage, guaranteeing maximum compiling times.

this might be true in the old days, but today this argument is irrelevant. Plan reuse is a well known feature that works great!
to be fair this also works great for non sproc SQL.

quote:

Whoa, Trigger!

Another disadvantage is that triggers and stored procedures do not communicate anything to the query optimizer. The user calls the stored procedure, so the database has no idea when it will execute, or even what code it contains. If you maintain referential integrity with the PRIMARY KEY, FOREIGN KEY...REFERENCES, and CHECK() clauses on the tables instead of in triggers, then the optimizer can pick up the predicates in the CHECK() clauses and use them in its execution plan.


this is totally true and still holds. Referential integrity should never be ebforced with triggers. who does this is an idiot or has a very good reason.

___________________________________________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.5 out!
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-05-12 : 13:01:06
Also, the scenarios above is about "procedural approach" in stored procedures; aka cursor approach.
You are allowed and it's approved to write set-based code in stored procedures.
http://sqlblog.com/blogs/paul_nielsen/archive/2009/05/09/why-use-stored-procedures.aspx



E 12°55'05.63"
N 56°04'39.26"
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-05-12 : 13:04:18
Also worth mentioning is that article referenced by WhiteFang above was published in Byte Magazine in March 1998.
11 year old article (http://www.celko.com/articles.htm), how cool is that!?


E 12°55'05.63"
N 56°04'39.26"
Go to Top of Page

robvolk
Most Valuable Yak

15732 Posts

Posted - 2009-05-12 : 13:16:16
Nice troll, but not up to your previous standards. You usually made your own point rather than go with "some other guy says it's bad".

Maybe Joe will drop in and provide some opinion, he posts on SQL Team from time to time.
Go to Top of Page

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-05-12 : 13:24:15
Q: How do you enforce temporal relations in a relational database and enforce that you don't have any temporal gaps or overlaps without triggers then?
A: You don't

Everything has a time and a place. Just like a lot of SQL people say to NEVER use temp tables or denormalization is ALWAYS wrong. Depends, depends, depends..
Go to Top of Page

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-05-12 : 13:42:44
I can just see Whitefang up late night in bed with a flashlight on under the covers so his mom doesn't catch him, pouring thru books looking for quotes to take out of context just to show you guys who's boss. "They'll be sorry they ever messed with me!" He thought as he reached for another Kleenex.

Jim
Go to Top of Page

whitefang
Enterprise-Level Plonker Who's Not Wrong

272 Posts

Posted - 2009-05-12 : 14:03:20
quote:
Originally posted by spirit1
this might be true in the old days, but today this argument is irrelevant. Plan reuse is a well known feature that works great!
to be fair this also works great for non sproc SQL.



I guess you forgot to read the SQL Server 2008 documentation
http://msdn.microsoft.com/en-us/library/ms181055.aspx

A SP is always recompiled if one of the following conditions is met:
- Large numbers of changes to keys (generated by INSERT or DELETE statements from other users that modify a table referenced by the query).
- For tables with triggers, if the number of rows in the inserted or deleted tables grows significantly.

Sort of like in an enterprise system. Also, stored procedures form an API by itself where any modifications can potentially break the API. So you end up adding another interface stored procedure and eventually end up with 1000 or more stored procedures, some obsolete. Good luck debugging a dinosaur API with pascal coding and no intellisense, reflection, or real documentation.

When you keep all the business logic in an SP, the application layer must have some business logic too (in most cases, it's the same logic in the DB). So now, you have the same business logic in two places.

Go to Top of Page

robvolk
Most Valuable Yak

15732 Posts

Posted - 2009-05-12 : 14:05:33
quote:
Good luck debugging a dinosaur API with pascal coding and no intellisense, reflection, or real documentation.
Are you hiring? What's the rate?

Real programmers use Notepad.
Go to Top of Page

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2009-05-12 : 14:33:29
quote:
Originally posted by whitefang

[quote]
I guess you forgot to read the SQL Server 2008 documentation
http://msdn.microsoft.com/en-us/library/ms181055.aspx

A SP is always recompiled if one of the following conditions is met:
- Large numbers of changes to keys (generated by INSERT or DELETE statements from other users that modify a table referenced by the query).



Large numbers being 20% of the table (+500 rows), which is the threshold for a statistics update that invalidates cached plans based on that table. That's a lot of rows, especially on large tables. Even given that, on a well-tuned database server, plan reuse is usually well over 95% (95% of queries that run can reuse the cached plan)

Note that that applies to stored procedures and parameterised ad-hoc SQL identically. There are minimal differences in the later versions of SQL between plan caching for stored procedures and plan caching for parameterised ad-hoc SQL. Any ad-hoc SQL that you submit from your app also has to be parsed, bound, and either optimised or matched with an existing cached plan, and it too will have to be recompiled if the statistics get updated.

In SQL 2000 and below, procs were cached far better than ad-hoc SQL and the use of ad-hoc SQL could result in a large overhead due to query optimisation.

Even in SQL 2008, the use of adhoc SQL can have a detrimental effect because plan matching for any ad-hoc SQL is based on a compare of query texts. Even an extra space will mean that the query doesn't match to one in cache. Stored procs, triggers and functions match based on the object_id.

--
Gail Shaw
SQL Server MVP
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-05-12 : 14:51:19
quote:
Originally posted by whitefang

Good luck debugging a dinosaur API with pascal coding and no intellisense, reflection, or real documentation.
And again I fail to see the importance, or even relevance, of Intellisense and business logic...



E 12°55'05.63"
N 56°04'39.26"
Go to Top of Page

robvolk
Most Valuable Yak

15732 Posts

Posted - 2009-05-12 : 14:56:22
Well, you can't do enterprise level programming without Intellisense.

One wonders how the Space Shuttle can fly, or we landed on the moon, using all that non object-oriented non n-tier non-enterprise software.
Go to Top of Page

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2009-05-12 : 14:58:58
I fail to see what reflection has to do with debugging. Reflection is the ability to get info on an object at runtime (I assume similar to SQL's metadata queries). How that makes debugging easier I cannot see.

As for documentation, my .net stuff has far worse documentation than my SQL code does. Documentation is a matter of programmer discipline and has nothing to do with language, dev environment or anything else.

--
Gail Shaw
SQL Server MVP
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2009-05-12 : 15:14:56
Celko has a real knack for taking his personal opinion and making pronouncements that make them sound like rules of nature, never to be questioned. In any case, virtually everything that you quoted is very debatable or obsolete.

As for taking Celko’s word over us because he is well known:
http://en.wikipedia.org/wiki/Informal_logic
“Argument from authority or appeal to authority is a logical fallacy, where it is argued that a statement is correct because the statement is made by a person or source that is commonly regarded as authoritative.…”

If you still want to play, you can use this as a reference for constructing more invalid arguments:
http://en.wikipedia.org/wiki/List_of_fallacies





CODO ERGO SUM
Go to Top of Page

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2009-05-12 : 16:15:12
plus i think we've already established that sproc should not have business logic in them
data logic yes, but business logic no.

___________________________________________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.5 out!
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-19 : 02:36:28
Another user (?) asking Celko for help
http://www.dbtalk.net/microsoft-public-sqlserver-programming/db-architecture-questions-joe-celko-639710.html



N 56°04'39.26"
E 12°55'05.63"
Go to Top of Page

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2009-07-19 : 17:31:23
?? Q: How do you enforce temporal relations in a relational database and enforce that you don't have any temporal gaps or overlaps without triggers then?
A: You don't <<

Wrong. Let me use ANSI Standard SQL for a history table of price changes. The fact is that a price had duration. This is the nature of time and other continuums. So a basic history table looks like this in SQL/PSM

CREATE TABLE PriceHistory
(upc CHAR(13) NOT NULL -- industry standard
REFERENCES Inventory(upc),
price_prev_date DATE NOT NULL,
price_start_date DATE DEFAULT CURRENT_DATE NOT NULL,
price_end_date DATE, -- null means current price
CHECK(price_start_date < price_end_date),
CHECK (price_start_date = price_prev_date + INTERVAL 1 DAY), -- prevents gaps
PRIMARY KEY (upc, price_start_date),
item_price DECIMAL (12,4) NOT NULL
CHECK (item_price > 0.0000),
etc.);

You use a BETWEEN predicate to get the appropriate price.

SELECT ..
FROM PriceHistory AS H, Orders AS O
WHERE O.sales_date BETWEEN H.price_start_date
AND COALESCE (price_end_date, CURRENT_DATE);

It is also a good idea to have a VIEW with the current data:

CREATE VIEW CurrentPrices (..)
AS
SELECT ..
FROM PriceHistory
WHERE price_end_date IS NULL;

Now your only problem is to write a stored procedure that will update the table and insert a new row. You can do this with a single MERGE statement, or with a short block of SQL/PSM code:

CREATE PROCEDURE UpdateItemPrice
(IN in_upc CHAR(13), IN new_item_price DECIMAL (12,4))
LANGUAGE SQL
BEGIN ATOMIC
UPDATE PriceHistory
SET price_end_date = CURRENT_DATE
WHERE upc = in_upc;
INSERT INTO PriceHistory (upc, price_prev_date, price_start_date, price_end_date, item_price)
VALUES (in_upc, CURRENT_DATE, CURRENT_DATE + INTERVAL '1' DAY, NULL, new_item_price);
END;

This will make the price change go into effect tomorrow.


--CELKO--
Joe Celko, SQL Guru
Go to Top of Page

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2009-07-19 : 18:13:54
quote:
Originally posted by robvolk

Well, you can't do enterprise level programming without Intellisense.

One wonders how the Space Shuttle can fly, or we landed on the moon, using all that non object-oriented non n-tier non-enterprise software.



You are kidding, right?

--Jeff Moden
"Your lack of planning DOES constitute an emergency on my part... SO PLAN BETTER! "
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row"

Go to Top of Page

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2009-07-19 : 18:14:45
quote:
Originally posted by spirit1

plus i think we've already established that sproc should not have business logic in them
data logic yes, but business logic no.

___________________________________________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.5 out!



Heh... who the hell made THAT rule? Like everything else, it depends on the situation. If you don't thing so, don't ever use another constraint on a table... ever. ;-)

--Jeff Moden
"Your lack of planning DOES constitute an emergency on my part... SO PLAN BETTER! "
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row"

Go to Top of Page

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2009-07-19 : 18:16:44
Guess that's another book/article that I won't take to heart.

--Jeff Moden
"Your lack of planning DOES constitute an emergency on my part... SO PLAN BETTER! "
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row"

Go to Top of Page
    Next Page

- Advertisement -