SQL procedure performance question

Question

Which of these have a better effective performance when used from ASP.NET

SP1:

Returns 100 params

SP2:

Selects 100 params from SP1 into a temp table by doing a EXEC SP1 Selects 20 params from temp table

Answer 1

It sounds like you are paging data from a result-set. It is always better to chunk the data in the database so that you don't incur the overhead of transferring the data to the webserver and use up memory on the server for data you will never display.

Answer 2

In all performance issues, you will need to test to see which is actually faster. I would suspect that the second would take longer to run on the SQl Server but it will pass through the network faster as less data is being returned. Whether the difference is noticable or if the improved network speed can offset the longer proc performance is really something that only you can see with your current datbase design and equipment.

Of course the fastest would be to write a new proc that only processes the records or columns you want to see.

Answer 3

If you have a stored procedure with 100 "out" parameters then you have a fundamental problem in your design. I truly don't mean to be rude but I simply cannot imagine a SQL context in which this makes sense. My advice to you is to rethink your entire design.

In the worst case (where the design doesn't really change), you could craft and return a temporary table within your SP1 and then have a SqlDataReader pull each value in turn. But 100 out parameters? No, I would never do that.

Of course, I have read this question several times and I still don't know exactly what you are asking so I could be off. For example, what do you mean when you say "Selects 100 params from SP1 into a temp table by doing a EXEC SP1 Selects 20 params from temp table"? Thus, I can't really give you any perspective on "SP1" versus "SP2".

Answer 4

I would say 'overall' sp2 is faster because there you are using db to filter 80 columns(!) instead of all that data traveling through to your app server to be discarded.

However , you can give a thought to maybe modifying sp1 to accept an additional argument and depending on that have a result set 100 or 20 columns wide