Advice for best practice when consolidating code 2

[HoustonGuy]

I have a very, very wide resultset that I create for our processes. Once the first iteration of the table is created, I conditionally update a lot of the fields using additional statements. I have about 50 statements that update the table based on various conditions. Some of these statements use temp tables and lookup tables also.

I have kept this SQL code linear during testing, to ensure all is well. Now I need to make this code manageable, so my thoughts are that many, if not all of these statements should be put in their own prospective stored procedures - for many reasons. Mainly usability, and manageability, etc.

Does this sound like the correct plan of action?

Thank you in advance!

 

[gmmastros]

Excellent question!

You are likely to get several different, sometimes opposing responses. I can only answer from my perspective.

When I am working on code, I generally follow these steps....

1. Make it work.
2. Make it fast.
3. Make it maintainable.

As a database guy, it is your number 1 responsibility to make sure your data is correct. You simply cannot tolerate bad data because people will be basing decisions on the data and bad data leads to bad decisions.

Making it fast is a bit subjective, but it is always a secondary priority of mine. Performance is directly related to the user experience. Poor performing queries leads to a poor performing app, which leads to disgruntled users. You also need to consider scalability. If a query performs within a reasonable time, you may think you've done your job. As more users begin using the software, you may find yourself in a position where they are waiting for queries. Each query could be reasonably fast when it is the only query running, but could be prohibitively slow with concurrent users.

Please don't misunderstand me. I am not suggesting that you spend hundreds of hours optimizing all of your queries, because you could effectively be wasting your time on some of them. However, as a developer gets more experience, they get a feel for what will perform well and what won't.

In my opinion, you should never sacrifice data quality or performance in an attempt to make your code more maintainable.

If you can separate your steps in to multiple procedures without sacrificing data quality or performance, then I say, "go for it".

Please understand that this is my advice. Do what you want, but at least consider it.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[HoustonGuy]

Once again - excellent post George.

Those three steps are exactly my plan of action.
I'm correcting readbility/formatting, then I'm moving on to reviewing speed. Then lastly, I'd like it to be maintainable - which is why I posted.

I think keeping the code linear may be fastest, but anyone coming after me would not consider it modular/maintainable.

All advice and opinions are welcome.

 

[TysonLPrice]

gmmastros is always worth listening to and heeding his advice

I work in an insurance related field and there are certain calculations like standard premium, tabular reserve, etc. When I started supporting this existing system the calculations were often done over and over again in many stored procedures. I would have to search for everywhere they were used and change them when the calculation changed for some reason (say industry mandates). As I modify the system during maintenance I do my best to consolidate them into stored procedure calls, views, functions, etc. My point being if a function like those is used repeatedly I like to isolate them so the can be changed in one place.

 

[HoustonGuy]

Thanks Tyson.

Very good points!
I need to check redundant actions to see if they can be placed in a UDF.

Unfortunately there will be many Stored Procedures that I will call from the Main procedure, but with a proper naming convention and commenting it should be manageable.

Most of my lookup tables are perm tables that I truncate for each run. I don't think I see the need for any views, but I have the need to retain some local temp tables.

Very good points, and I thank you! I appreciate any and all help to stay fresh on best practices!