avoiding multiple calls to SQL while being modular

Question

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?

Answer 1

In order:

1. Is there something which allows you to assert that the application is slow? Is there a non-functional requirement related to performance that the application doesn't fulfill any longer? Does it feel slow (in which case your first concern should be to write down a non-functional requirement which will explicitly tell what are you expecting)?

   If the application is fast enough, you're doing premature optimization. Please, don't do that.

2. What are the results when you profile the application? Have you profiled it and identified that the fact that you're doing three queries instead of one is the bottleneck which affects the performance in a very noticeable way?

   If not, do this first. Run profiling, identify the bottlenecks. Very probably, you'll find that the number of queries (3 vs. 1) is your least concern in terms of performance.

   Note that connection pooling (if you're using Microsoft SQL Server) reduces considerably the cost of closing and reopening a connection several times. Make sure you close your connections properly, i.e. you dispose the resources. You should have using () { } around all SqlConnections, SqlTransactions, SqlCommands, etc. The static checking tool called Code Analysis helps you finding the stuff not properly disposed.

   Remember to profile your views as well. Execution plans are particularly useful in this case. Watch for the indexes (not necessarily the lack of them, but also which ones are there and why). Look at the data you load and the data you actually need (sometimes, developers load too much data from a database, and only then filter it at application level; this is unfortunate, since databases do the filtering job very well themselves).

   If you're using an ORM, watch the queries it builds (you may use SQL Server Profiler for that; most ORMs also allow to log the queries they send to the database). Sometimes, ORMs need help for figuring out the best way to build an optimized query.

3. Do you use caching?

   If not, you may want to focus on that first, before dealing with multiple queries vs. one query. Since you're talking about views, I imagine that you're not modifying data, but only reading it (although, you can modify data through views in Microsoft SQL Server). This means that this data can very probably be cached on the server, which means that instead of 3 or 1 queries, you'll have 0.

Only then, i.e. only if you know that the application doesn't respond to a non-functional requirement of performance, that it is slow because it does three queries instead of one and that caching won't help should you be concerned by optimization consisting of replacing three queries by one.

Remember that you're not necessarily forced to do it through a stored procedure. In your particular case, since all you do is to access three views, creating a fourth view can be the solution.

In all cases, I'm surprised that you have found that (or that you believe that) performance is affected on I/O level by three queries instead of one:

• What could have been the issue is to close and reopen connections two times. As I previously said, connection pooling solves that.

• In general, the time spent sending a query to an SQL server is nearly zero compared to the time spent reading the data back, unless you're querying a scalar value.

• In general, the time spent sending a query to an SQL server is not as important as the time spent by the SQL server loading the data (given that most SQL servers do a great job of caching data in memory, which makes most queries outstandingly fast).