August 21, 2009 at 8:43 am
My question here might get a bit vague without actually seeing the whole system however I will try to provide as much of detail as possible and would certainly provide any additional information that would be needed in advising me further:
My company is a mid size company where we use extensive use of SQL Server reporting services. We make use of SQL Server as the database. Now we are migrating to the new SQL Server 2008/ SSRS 2008 environment. We have been making extensive use of stored procedures for creating reports. However there is a huge log stored procedures because we have been using a few base stored procedures and other branched stored procedures.
Now with migration we would also like to bring about new ways to organize stored procedures or use alternatives to stored procedures in order to reduce the high number of stored procedure which get complicated when everyone starts creating innumerable branches. We started exploring other options like using UDF to reduce the use of stored procedures. However I learnt from my research that UDF's are not very scalable.
1) Are there any alternatives to using stored procedures?
2) Is there anyway we can UDF's and can make them scalable?
3) Are there any other ways to organize stored procedures rather than branching them innumerably.
Any advice would be appreciated!
Thx.
August 21, 2009 at 9:15 am
I would stay with the SP you could try to eliminate the branching....
do this by reusing the base SP for each new SP. Just a copy and paste....I think I understand what you are trying to do though.
I have the same problems. you end up with a SP that calls many SP with in it, if I am understanding you correctly... it almost like having triggers...gets a little messy to follow.
It seems like the idea it right you just reusing code as in OOP. \Not sure of any other way around it
good luck
August 21, 2009 at 9:24 am
I think you pretty much got it right. Yea and you invariably end up calling one base stored proc from quite a lot of stored procs and it gets messy.
August 21, 2009 at 9:26 am
Stick with procs. It can get messy, but anything else gets even worse.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 23, 2009 at 6:04 pm
I think that in the described scenario storedprocs are just the fever... the illness might be...
MaverickMan (8/21/2009)
... get complicated when everyone starts creating innumerable branches.
I would stay with storedprocs and try to better organize development effort.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.August 23, 2009 at 7:34 pm
it's really not possible to give a good answer to this question without looking at some examples oif what you are seeing as "the problem". In particular, wht do the "branched" procs look like and why are your developers doing it this way?
There are at least a half-a dozen different ways to do this, but which approach (or combination or approaches) has a lot to do with how and why you are ending up where you are.
Here are just some of the ways that I know of and have used successfully in the past, and that might be right for you. Though all of these can avoid the scaling & performance problems of normal UDF's, which is right, again, depends.
( ** - despite what you may have heard, dynamic execution can be done very safely and performance, but it reuires structure and discipline ti insure this).
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
August 24, 2009 at 6:16 pm
I think Barry lays out most of what you need:
structure and discipline
Structure: Develop a clear,concise, well planned naming convention for your sprocs
Discipline: Stick to it
-MarkO
"You do not really understand something until you can explain it to your grandmother" - Albert Einstein
August 24, 2009 at 6:54 pm
molson (8/24/2009)
I think Barry lays out most of what you need:structure and discipline
Structure: Develop a clear,concise, well planned naming convention for your sprocs
Discipline: Stick to it
Heh, agreed Molson. Always te two best tools in any development effort.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
August 25, 2009 at 9:12 am
RBarryYoung (8/24/2009)
molson (8/24/2009)
I think Barry lays out most of what you need:structure and discipline
Structure: Develop a clear,concise, well planned naming convention for your sprocs
Discipline: Stick to it
Heh, agreed Molson. Always te two best tools in any development effort.
Both of you have my vote guys 😉
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.August 25, 2009 at 9:17 am
Thanks very much all of you guys. I certainly think these thoughts would help us better organize going forward. I appreciate all of you taking time out for posting your thoughts.
August 25, 2009 at 9:39 am
Well, I was really hoping for an answer to my question. Plus, I would be very interested in what you have decided to do and what the thinking was at your place. We like for this to be a two-way street after all, we can learn from you too.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply