Stored Procedure

Most companies like to code their logic within stored procedure. Reason Being

1) To reduce method call between app servers and client (especially web client)
2) Easier to manage (Is It True)

From what I have learned for the past  4 years,  3-Tier/ N-Tier are hardly practised by most companies. 


Any Comments....

for small projects, indeed n-tier will be an overkill. as you move up the value chain of software projects, however, you will see fewer and fewer such quick and dirty ways of getting things done. the drawbacks of encoding business logic in data tier instead of the business tier (or even using business rule engines in especially complex projects) will emerge. for one, scalability is hard to achieve when the execution of complex logic are managed by an ill-fitted dbms.

I tend to be generally against (though cannot say 100%) the idea of implementing application and domain logic into stored procedures, and thus the database layer. I perceive the database as primarily that - a physical repository to persist the data application(s) make use of.

Note that I placed a plurality marker there to imply more than one application can make use of the same set of data in different ways. Although suffice to say those type of highly extensible scenarios are rare, and difficult to design.

Even if it is not two different applications, differing business/domain logic within a same application would benefit greatly from the more flexible constructs of modern programming languages and platforms - they truly offer a greater degree and extent of freedom in logical expression and application design than any SQL implementation can ever facilitate. Remember than SQL is designed to work on set-based data, which is fine for working with flat data, but hardly an intuitive way to model how the real world works. (How should I implement different discount rates for Christmas sale versus stock clearance sale versus bundled offers? And possibly many more other rules?)

General rule of thumb for me is to apply data-integrity logic in stored procedures, or basic querying rules. Everything else that is the essence of what the application is about lives mostly in the domain layer.

3-Tier/ N-Tier are hardly practised by most companies

A tragically sad fact for development teams who have not graduated out of their "vertical pillar" mentality - end-to-end functional delivery to immediately satisfy the user. This development model is still ok for small applications, but applications always grow into something larger, much larger. In fact most of the time, larger than these vertical veins of functionality can cope to keep the code from mutating into one whole monolithic complicated mess, unmaintainable and incomprehensible.

Most teams just develop without designing. Plain asking for scores of problems and bugs to come crashing in over and over like ocean waves. Teams need to learn how to identify and split their application design by technological and service layers, instead of business/user requirements.

feelite, what do you mean by an "ill-fited" DBMS? Would Server 2005 considered "ill-fitted"?

I did complete a "small" application that loads legacy time punches (in flat files with comma as delimiter) into SQL 2005, process them according to their business logic/rules and export them out for payroll processing. They have around 5K active employees and I wrote only SQL queries and no programming code at all. It took less than 7 seconds to complete (from loading of 200+ files, process them and eventually, export them into Excel files)  wheres the original dbase programme took 20-30mins to complete everything. 

I do believe it's considered "simple, easy and small" application. If you're talking about internet banking, e-commerce sites where thousands or millions of users with many many small little functions, I do not think the stored procedures approach would work.

Why not a hybrid? Business rules that's fixed and required less maintenance but having complex calculations leave it to the DBMS wheres the rest use the 3-Tier / N-tier approach?

Sometimes I do envy the older ways of doing things.....when Cobol/C was my job, performance is still No1 priority. Code messy never mind, as long as it's well documented, it is not that difficult to maintain them. Sometimes I am wondering, which is the right way to go?

What he means is a DBMS is ill-fitted to conduct complex business/domain logic and decision branching. Depending on the level of complexity, you may be able to "get away" with stored procedures, as you have with is essentially an ETL process. But, systems/businesses usually require alot more than just that. Security ACLs, OO and inheritance models (unless one likes thousands of If-Else), workflow, distributed computing, loop (per-entity) processing, hardware communication, file I/O, external systems, etc.

SQL is designed around managing data stored in the database and applying set-based operations on them. In fact trying to pull everything into application memory and update a field in a loop is definitely silly when a single UPDATE statement would do. Trying to however use it to accomplish the above examples are an incredible pain to accomplish, if even possible.  The usual type of business logic that goes well with SQL are the ones that ensure the integrity of the data. Then again, it depends very much on how complex that logic is - a simple cascading update, or some complicated re-calculation and decision making?

Sometimes I do envy the older ways of doing things.....when Cobol/C was my job, performance is still No1 priority. Code messy never mind, as long as it's well documented, it is not that difficult to maintain them. Sometimes I am wondering, which is the right way to go?

If you do not find it difficult to read and understand the what is often interwoven logic of other people's thinking, then it is hats off to you. But for most others, it is more than half the day trying to figure out just what the other developer has written - who has documented all the 203 different use cases that apply to all that code in a single WinForms class sizing 16000 lines?

And more often than not, the code is not performant either. Nor reliable. Trying to fix things or make it run faster is like replacing a bottom card in a castle.

Which is the right way to go?

I know some of the MNC companies in Malaysia write the parameterized sql inside the code behind. They claimed to be better ... and said that developers nowadays don't know how to write stored procedures, bad naming yada yada yada ...

But in my company, we only write basic select, update, delete statements in stored procedure (and at best do filtering data, order by). But business logics are all done on Business layer.

Let say we have a TransactHeader table and a TransactDetail table. What if we want to duplicate a transaction (together with details), and yet obtain some current values for calculation for that transaction? Do we use business logic or stored procedure for the duplication?

bronkman:

Let say we have a TransactHeader table and a TransactDetail table. What if we want to duplicate a transaction (together with details), and yet obtain some current values for calculation for that transaction? Do we use business logic or stored procedure for the duplication?

Perhaps you'd like to explain the scenario and context for what this "transaction" truely refers to. What kind of a transaction are we looking at that needs to be duplicated and than recalculated? Is that still a duplicate?

Thought that the scenario does not matter in this case, but anyway... Let's see... If we have an Order for a number of items that has already been processed, shipped and billed. So the same customer now wants a new Order identical to the previous one to be processed again for the same number of items. Assuming that the customer transacts in foreign currency and so recalculation is required to use the latest foreign exchange rate.

To the database, they are different. To the system, they are similar not the same. Query out the order structure into program memory, create a new order based on that structure, then save back to database.

hmmm let me share what i would do

1. i guess you've some function like replace order or something like that

2. when user clicks on that link or functionality etc, (s)he'll be shown list of his/her previous orders from which he can select 1 or more depending on your requirement. assume only one as of now can be replaced

3. from UI i'll pass only the order_no to Business Logic Layer in turn to Database layer, i'll write a stored procedure to do the job

i would be something like //i'm assuming order_no is identity column for sake of simplicity

insert order_header(customer_id,order_date.....)

select customer_id, getdate(),.....from order_header where order_number = '@passed_order_number' //from UI

sorry dont know if that sounds complicated and i really dont know exactly what your application looks like.

regards,

satish.

hi chuawenching ,

one reason i would like to use procedure instead of inline queries is lets say you've one query that repeats in many pages, and for some reason you need to change something outcome you'll keep modifying all places. still worst will be say you forgot one place to modify and you deployed the modified app. what will happen , user will curse us .

 

Hi satish, there is no actual application, just to share a case study I thought of. Your solution would represent part of the solution to duplicate the order header, which is great if it is only the header information that needs to be duplicated. Part of the case is to duplicate the order details as well. So the question is to:

1. strictly separate Business Logic and Database Layer. Like icelava proposed, to create a data structure and insert each order detail; or
2. integrate Business Logic directly into the Stored Procedure (Database Layer) calling insert..select and update (for latest information update)

Perhaps it depends on the scale, like feelite mentioned, or the Solution Architecture adopted by the organisation, or the performance of each option as opposed to the number of times the duplicate function is utilised, etc.

Perhaps that's why we developers need to shape our skills towards knowing the inner working of the applications/systems that we're dealing with, then only we can decide what to do in what situation. A good chef may know the recipe, the cooking steps and tips and tricks. But a better chef knows how to cook the similar food with replacement ingredients, producing a very similar result.

 http://www.sql-server-performance.com/best_sql_server_performance_tips.asp