Stored Procedure Hell
-
Does the stored procedure do the job it was intended to do? Does it make business sense to decouple it and break it down into smaller pieces? If it works, and doesn't need to be decoupled, then where is the issue?
-
Don't you know that stored procedures are the fastest possible way to do something on the database? Surely it doesn't matter how it is written. it still has to be faster than to do it in a more maintainable way :rolleyes:
Put that BACK!
Nicholas Marty wrote:
What?
The pish you just took! :laugh: :laugh: :laugh:
speramus in juniperus
-
I agree with your argument of being the fastest way to interact with the database data, but it also means the business logic stays in the database and is higly coupled with the db system. In an evolutive world as the one we live in, this is not recommended, although this is probably a system from a big corp that was built on older paradigms. At this point, they probably have no other choice. :)
I'd be hard pushed to describe any of this as logic!
"If you don't fail at least 90 percent of the time, you're not aiming high enough." Alan Kay.
-
Does the stored procedure do the job it was intended to do? Does it make business sense to decouple it and break it down into smaller pieces? If it works, and doesn't need to be decoupled, then where is the issue?
At present it kind of works. Our system deals with a number of types of object, if a new type is added, we will need to maintain it. Nobody has been brave enough to tackle the thing, and no one really understands how it works. But apart from that, all is hunky dory.
"If you don't fail at least 90 percent of the time, you're not aiming high enough." Alan Kay.
-
I agree with your argument of being the fastest way to interact with the database data, but it also means the business logic stays in the database and is higly coupled with the db system. In an evolutive world as the one we live in, this is not recommended, although this is probably a system from a big corp that was built on older paradigms. At this point, they probably have no other choice. :)
Actually, the little bit I looked at today had rafts of queries of the form..
INSERT INTO TEMPTABLE (A, B, C, D, 'OBJ TYPE 1')
SELECT A, B, C, D
FROM MASTERTABLE
WHERE OBJTYPE = 1 ' OBJ TYPE 1INSERT INTO TEMPTABLE (A, B, C, D, 'OBJ TYPE 2')
SELECT A, B, C, D
FROM MASTERTABLE
WHERE OBJTYPE = 2 ' OBJ TYPE 2(without the comment. Theres absolutely zero comments) Rinse and repeat for a further 13 object types, then apply a set of 15 update queries to the result based on other selections based on the same types. If I ever have to get my hands dirty, I'll begin by refactoring those out to other procedures, and use a parameter to specify the object type - that's if I can't do the same thing more efficiently by GROUP BY in some way. Hopefully after a while some wood will begin to emerge from the trees.
"If you don't fail at least 90 percent of the time, you're not aiming high enough." Alan Kay.
-
Put that BACK!
Nicholas Marty wrote:
What?
The pish you just took! :laugh: :laugh: :laugh:
speramus in juniperus
I was further amazed to discover the guy who wrote it (used to work for a third party who used to do the development) now works for Microsoft! I did joke that maybe his job is to push their query optimisation team to the limits. ;)
"If you don't fail at least 90 percent of the time, you're not aiming high enough." Alan Kay.
-
Apparently, about 18 months ago it took a couple of hours. At this rate, we soon won't be able to run it overnight, as our database doesn't tend to get smaller over time!
"If you don't fail at least 90 percent of the time, you're not aiming high enough." Alan Kay.
-
Apparently, about 18 months ago it took a couple of hours. At this rate, we soon won't be able to run it overnight, as our database doesn't tend to get smaller over time!
"If you don't fail at least 90 percent of the time, you're not aiming high enough." Alan Kay.
Is the growth in execution time linear or exponential?
Politicians are always realistically manoeuvering for the next election. They are obsolete as fundamental problem-solvers. Buckminster Fuller
-
At present it kind of works. Our system deals with a number of types of object, if a new type is added, we will need to maintain it. Nobody has been brave enough to tackle the thing, and no one really understands how it works. But apart from that, all is hunky dory.
"If you don't fail at least 90 percent of the time, you're not aiming high enough." Alan Kay.
Just call it magic and back off slowly. :laugh: And don't turn your back on it until you're out of sight!! :D
-
Is the growth in execution time linear or exponential?
Politicians are always realistically manoeuvering for the next election. They are obsolete as fundamental problem-solvers. Buckminster Fuller
I don't know yet, I only have 2 data points. (and one of them is fairly rough - I only know exactly for the latest few runs)
"If you don't fail at least 90 percent of the time, you're not aiming high enough." Alan Kay.
-
I was further amazed to discover the guy who wrote it (used to work for a third party who used to do the development) now works for Microsoft! I did joke that maybe his job is to push their query optimisation team to the limits. ;)
"If you don't fail at least 90 percent of the time, you're not aiming high enough." Alan Kay.
Rob Grainger wrote:
now works for Microsoft
How about nominating him here? http://www.nextmicrosoftceo.com/[^]
Vasudevan Deepak Kumar Personal Homepage You can not step into the same river twice.