Skip to main content
2 of 4
added 1300 characters in body
Arseni Mourzenko
  • 139.2k
  • 32
  • 359
  • 544

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

  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. Remember that connection polling (if you're using Microsoft SQL Server) reduces considerably the cost of closing and reopening a connection several times.

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

  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.

Arseni Mourzenko
  • 139.2k
  • 32
  • 359
  • 544