Hi
I find my self in a situation where I have to choose between either creating a new sp in database and create the middle layer code. so loose some precious development time. also the procedure is likely to contain some joins.
Or use two already existing sp(s), the problem of this approach is that I am doing two round trips to database. which can be poor performance especially if I have database in another server.
Which approach you will go?, and why?
thanks
You've answered the question your self. Two round trips is going to be far less efficient, and should be avoided.
If you don't have much time, you might be able to create a new SP that calls the other two SPs.
If it's really slow to create code for calling a new SP, and people are avoiding it, you might want to question your architecture as a whole.
You are really not providing enough information here.
In general, I would go for the single - SP approach, unless it is in fact two very different things that you are retrieving from the database. I think the stored procedures, should follow the Single Responsibility principle, just as your classes should.
Two round trips to the database server won't normally be a problem; but of course it depends on how it is being used. For instance, do you need to retrieve the data at every request ? Is performance critical ? Is it being used in a tight loop ? (hopefully not :-)
Keep in mind, that even if you have two separate SPs and you want to join the results together, there is no simple way to do that on the server side in a 3rd SP. You can insert them each into a temporary table and then join the temporary tables.
You can also copy the code from both SPs and combine them to produce the desired result set - potentially resulting in some logic duplication and maintenance issues.
Or, as you say, you can do it client side. If the two SPs don't return significantly more information separately than you want to return together, it's not terrible overhead (and if you can spawn them asynchronously from the client and then handle them together, it can actually be fairly efficient)
So in any case, you are already going to have to do some coding and testing of at least minor significance.
Because of that, I would strongly recommend you look into changing the two original SPs into table-valued functions (inline if possible). Then the two original SPs can pull from the UDFs and the new SP can join the two UDFs just like you would join tables or views. It's the simplest SP-joining mechanism from a code-duplication perspective and from a re-use and semantics perspective - the UDF code is relatively easy to read and use and re-use - they basically worked like parameterized views. Also, the optimizer tends to handle inline TVF VERY well. There are cases with complex SPs where TVFs simply cannot work, but for a bulk of scenarios, they are very powerful AND efficient. An unusually successful feature which can help significantly in any SQL Server system architecture.