1

Not that the current setup of a single stored procedure that does 3 thing takes too long. But perhaps in the interest of speeding up when the user can see some amount of information would be useful.

I have a single stored procedure that is being run with the async/await pattern and returns 3 separate sets of data. None of them require any of the other 2, so order and who completes first is a non-issue. Thus, the user will not see anything on the web page until the entire SP has run and is returned.

Aside from more code, which is no preferable, would it make more sense to break that SP into 3 separate SPs and have 3 separate async calls to those SPs?

On a side note, would having still a single SP but with a new @Which parameter make sense? Meaning when @Which = 1 query this data, when @Which = 2 query that data and so on. Or would there be some sort of lag attempting to run the same SP multiple times via 3 separate async calls?

I have done some research - Why should I prefer single 'await Task.WhenAll' over multiple awaits? was good information, but not really for my question.

Community
  • 1
  • 1
Grandizer
  • 2,819
  • 4
  • 46
  • 75

2 Answers2

2

As always, there really isn't a right or wrong answer to this. However, there are development patterns that specifically address this.

I do not believe that having a stored procedure return multiple result sets is a bad thing, however you mentioned that the result sets are independent of each other. My understanding of that statement is that the SP is performing 3 different units of work and can, should and will have a result set that does not depend on the results of another part of the operation. I believe that this voids the Single Responsibility principle. https://en.wikipedia.org/wiki/Single_responsibility_principle

If you ever had to update one portion of the stored procedure you now have to touch the entire stored procedure and potentially alter something unrelated to the change requested.

If you ever have to fetch just a portion of the Stored procedure results you still have to fetch all of the result sets in the procedure, perhaps without reason.

Now I might argue that if all 3 result sets depended on the same working set of data this would be an acceptable approach. A working set that required a guarantee they be similar across all result sets. Say for instance a detail result set and a few different aggregated versions, or something with some specific join conditions. Ideally, there are still other ways of handling some of that.

From a database maintainability perspective, I suggest making this 3 separate calls/stored procedures.

Brad D
  • 752
  • 4
  • 8
1

Yes, assuming the three SPs do not compete with each other, you ought to be able to either split them into 3 or have the same stored proc branch internally on a paramter, without any problems. Which of those two options you choose is personal preference, but I would opt for 3 sprocs if they are doing 3 logicall separate things.

jlew
  • 10,491
  • 1
  • 35
  • 58
  • Good point, these three sets of records are all just looking at the same data from 3 different ways, Date, Supplier and Amount. So if I have 3 separate SPs trying to hit the same records, that could? slow things down. – Grandizer Oct 28 '15 at 12:46