6

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?

enter image description here

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?

  • If I am reading you correctly, "Each business layer method has a corresponding method in the data access layer which then calls its respective view in the database." seems redundant and error prone. – msw Oct 12 '14 at 16:43
  • Thanks MSW for the comment. But could you expand a bit on why it would be reduntant? Each of those are doing different things. – user20358 Oct 12 '14 at 18:15
7

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).

  • Very well written answer, I just want to add that you also might want to mention the correctness aspect of the question. If one calls multiple non-DB operations, I am not sure how one can use the DB's built in transaction support. – InformedA Oct 12 '14 at 17:33
  • the application which I described above is not yet written. At this point in time the possibility of individual views being required is not yet 100% But they are being written just because eventually they will be required as per the requirements we know today, which could also change in the future. what exactly did you mean by writing down a non-functional requirement ? – user20358 Oct 12 '14 at 19:42
  • @randomA: with TransactionScope in .NET Framework 4, it doesn't matter, since you can mix for example several queries to the database with some calls to NTFS with transactional support, all this within a single transaction. – Arseni Mourzenko Oct 12 '14 at 19:45
  • @user20358: then you're at a point of doing premature optimization. As for your question about non-functional requirements, see that answer, point 2 for an example of such requirement. If instead you'are wandering what is the usefulness of writing down requirements before developing a software product, than this may be a separate question to post on Programmers.SE. – Arseni Mourzenko Oct 12 '14 at 19:50
  • I've really not been able to profile the application because we are still in the design phase. I just feel that making three trips to a server when one trip can do the job is taking too many liberties too early. I could be wrong on this thought and Im willing to listen to critcism on this point. – user20358 Oct 12 '14 at 19:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.