OO programming is evil with related to DB

I would like to ask you for advice for web site programming OO.
Every one uses OO nowadays, even I have been using it for the last few years. I find it exciting at first, but now I find it is the most ineffective way of programming in WEB SITE related to database. I still use OO for NON related database programming, but for web site programming using a db, I won't choose it anymore. It has a large drawback in db.

I give some simple example:
Suppose you are building a web site which is an order system. In the database, you have customer table, order table and order details table.

Now, you build a module to handle Customer, the customer module uses CRUD methods.

Now you have a page to display all customers,so you use the R method in the customer module (SELECT * FROM Customers)

Now you have another page to display all customers, but this time, you need to display their total number of orders as well, so you call the R method in Orders table to get the total numbers for each customer, so the R method in Orders table will look like (Select NumberOfOrders, Customers from Orders). so this page you need to call the R method in Customers module and R method in Orders module. You are calling 2 sp to display a list this time.

There are many problems in this design.
Why do you need to call 2 sp calls, that is waste of performance.

What if you have 100000 records, the performance drawback is too bad.How do you implement paging, do u create a sp just for paging

Because we attemp to allow resuability in all the modules. A single method is used in many pages. We will retrive too many unnecessary fields. Imagine a page you have to retrieve data from 10+ modules. The performance penalty is too heavy. I have a page to display the customers list where we need to get data from 10+ modules. Because of modulization, we are not supposed to write a signle sql to do the job.

The matter is even worse if you want to do sorting and paging

You have 10 pages to display customer information, but each customer page display difference kind of information, some page may only display the name, some pages only display their age, but not their name. If all of these pages are using the same sp, you retrieve so many rubbish, and if you do paging and sorting, you have to write another sp just for that purpose

The worst part is on testing, we introduce independany on the system, if you have a system with hundred of pages. a little change on the module will need to test hundres of pages. AND we normally do not document the dependencies. That change can be small such as I want that page to display one more field from another table (eg total amount of money they have ordered in the past month). You modify the sp to include that, because of that change, you delay the performance of other pages. That is BAD !!! It is not good for multiple developers to pick up your code, he would not remember your dependencies in your code

I am now using a old school method to program large systems
Each SP IS NOT resuable by other pages, there is maximum one sp call whenever you referesh the page. So the sp name will be end up like sp_PageName_OperationName.

It is very fast, and I like that it is indepedant of other pages, if I change the sp on that page, i don't have to modify and test the other pages. Other developers only need to worry the pages they are working with.

Of coze, I don't mean you don't use OO at all, stuff such as getting Connection string from the configuration, you still build a module to handle that.

I am talking about OO in DB programming is very bad

We are still using Relational Db at the moment, unless we got a OO DB, the performance drawaback is still there

Forgive me if I summarise this.

You don't want to make too many SPROC calls. So on each ASP.NET page (a class, in OO speak), you write a page-specific function (aka a method on that class) to get its data. In other words, you're following classic OO programming where you identify an object, you write a class to represent it and you implement its functionality in methods. That sounds to me like OO rocks.

Now imagine trying to write that with procedural code, without having some nightmare world of conditional statements, massive code duplication or objectivation via void * "handles" (shudder).

Of course, by not using dynamically generated SQL (which would realistically speaking cost you nothing in terms of perf), you're going to end up with a ton of sprocs to maintain. And that's going to hurt at some point. And by not creating properly factored data access and business logic classes (hey, just because there's a table called Customers, Orders, etc. doesn't mean that you have to architect SPROCs one table at a time with data access methods that work on only one type at a time), you might be making life much harder on yourself than it really is.

I have been using classic OO programming for years,, it works for small web sites.

When the web site has more than a thousnad of pages, the module is extremely hard to maintain.

OO programming retrieves the fields that you don't use on the page. That is very very bad. Now in my project that has hundreds of pages, the penalty comes.

1. I have to write so many "page specific sp" such as paging and sorting. Now the sprocs become very hard to manage

2. On testing, a small change on the module has to test the other pages dependant on it, and sometimes because of that small change, testing is very hard to control

My method is name the business class and dataaccess class as "PageNameBusinessClass.cs" and "PageNameDataAccessClass". That GURANTEES maximum performance and testing will not affect the other pages. I cut down the unnecessary dependancies between the classes.

However, I still have some general functions (non db related) that have dependancies with the business and data access class

Benoityip

Please explain how and why you think that OO programming is harder than non-OO programming when there are many thousands of pages.

There is absolutely no need for OO programming to retrieve fields that you don't use on a page. In fact, only poorly architected and designed systems will do that.

There is also no relationship between OO programming and SPROCS: they way that you organise how your data access is accomplished can be either efficient and easily maintainable, or not. It appears that you seem to be unable to

Let me give you an example in .NET. Using attributed programming, it is trivial to write one class that would handle every single page's sorting and paging code for a database. One class for thousands of pages. Now that's maintainable code. Without OO, you'd end up writing the same (or nearly the same) code over and over again. And there is absolutely no need to have so many page-specific sprocs. Too many developers think that SPROCs improve security and performance (and there are arguments that favour that idea, I grant you, but there are just as many that counter it), when in fact dynamically generated SQL queries would offer identical performance and reduce the maintenance overhead to just about zero.

I fear that you are obviously not using OO techniques, although you might be using classes and an OO programming language, in your design. Consequently, I fear that the problems you're having with OO programming, and thus the reason that you think that it is evil, are down to a lack of planning, design and effective refactoring, not down to OO at all.

By definition, OO is no more evil than procedural programming: in the worst case scenario, where OO has been totally misunderstood, it reduces to being merely badly organised procedural code. And it's true that many developers end up writing what you've done: a page has a business layer class has a data access layer class. Where such a one to one to one mapping exists, one has to question the design (there's no benefit to having three classes in this case, other than when there is a need to separate layers across physical tiers). A more holistic approach, that uses configuration- or attribute-based data to generate queries reduces that code requirement to virtually nil. And that has to be much more maintainable.

Page