I have a BusinessLayer.dll which calls DataAccessLayer.dll which ultimately makes the tcp connection to the SQL server. Each business layer method has a corresponding method in the data access layer which then calls its respective view in the database.
Now the question is what if I have a requirement where I need to get data from three views at one go for one business layer method? These views will be required individually in other areas of the application as well but in one or two areas the data will need to be fetched from all three views at once.
Option 1: Should I call those individual methods in the business layer in a fourth method by making three different hits to the database via their respective business and data access layers and then group the data together in my business layer or
Option 2: Should I just have a stored procedure that calls these three views and return the data to back to the DataAccessLayer and then back to the business layer?
In the above image Option 1 is denoted by the green boxes and the fourth business layer method is denoted by the black box that initiates the three hits to the SQL box to get the data.
Option 2 is denoted by the orange boxes. The benefit of the approach using option 2 is that you dont incur multiple IO hits over the network, but then the code in the Data layer is probably duplicating the code written in the green boxes.
The client can be a web or a windows based application. In a windows based application, the business layer will be wrapped by a service which the windows client will call. How does one acheive a balance of modularity and performance here?