SQL Server 2005 - Are there any major negative implications to returning multiple tables w/ stored procedures?

Question

I've run across multiple situations in the last few months where legacy sql SP's are returning a single table made up mostly of redundant information.

Example:

Select CustomerID, CustomerEmail, CustomerAddress, InventoryLineItem, ShipQty from ...

Returns:

55 [email protected] 723 StreetName InvLineItem#1 45
55 [email protected] 723 StreetName InvLineItem#2 42
55 [email protected] 723 StreetName InvLineItem#3 1
55 [email protected] 723 StreetName InvLineItem#4 5
55 [email protected] 723 StreetName InvLineItem#5 200
55 [email protected] 723 StreetName InvLineItem#6 7045

(first 3 fields never change)

Since I'm the only developer on the project, I've started breaking this into two select statements. (I work on the receiving .NET code as well)

select CustomerID, CustomerEmail, CustomerAddress from ....

Returns: 55 [email protected] 723 Streetname  <-- 1 record

select InventoryLineItem, ShipQty from .... Returns:

LineItem#1 45
LineItem#2 42
LineItem#3 1

etc.

Obviously the resulting Dataset in .NET is smaller, but it really bugs me to have sometimes 10 fields in a select statement that are always under all circumstances exactly the same. Some of the queries may even return 10's of thousands of records.

I feel like I'm doing the right thing here, but then again I'm no SQL pro.

Are there any reasons for me to avoid this practice?

Thanks in advance for your time.

Answer 1

Well, my big answer here is that if you're feeding the output to a reporting framework such as SSRS, multiple tables are going to make your life difficult -- reporting frameworks are pretty good at taking denormalized results and processing them into pretty reports.

As far as performance goes, no, you don't have a reason to avoid multiple resultsets coming off the sproc. As long as you're using a DataReader and .NextResult() on the client end, IMO you're golden.

Answer 2

I think you are right in your thoughts regarding redundant data.
It is extra data flowing through network.

It is possible that you might be calling a stored procedure with one of the same parameter that you are SELECTing.

e.g. GetCustomerOrders 'ALFKI' - which could be returning

ALFKI | ALFA Customer | OID001 ......
ALFKI | ALFA Customer | OID002 ......
ALFKI | ALFA Customer | OID003 ......

So, you can avoid the SELECTion of the field which you are passing as parameter to the stored proc.

EDIT: On a second thought, there could be another stored procedure - which will return Customer related information. The invoice related procedure will return Invoice Data for a given Customer ID.

Answer 3

I find that consistency in the stuff that a stored procedure returns can be very important, but you certainly see system stored procedures that don't have consistency (consider sp_help).

So it really depends on what you're after, and whether your client code will be able to handle it. Any time you want early-binding, or you have a client that will ask the stored procedure what it's going to be returning (such as any reporting client, or ORM system such as LINQ), then you may have trouble. If you're using .Net code, and you're expecting to see a different resultset structure every time, then it's probably not such a big deal.

At the end of the day, if you have a ton of columns that may be empty in some circumstances, that might give you more flexibility in your client, but it all depends on where you want to put the work in.

My personal preference would be to have a single resultset that's always the same shape. But then again, I would also be considering putting that in a table-valued function, so that I could handle the results in a more flexible way (such as grouping, etc). I find that stored procedures are great for when a client needs to call something, but if I'm wanting to handle stuff differently, I'd rather have a view or a table-valued function to produce it (much like the move from system stored procedures to DMVs that Microsoft went down a while back).

Rob

Answer 4

I use multiple result sets all the time.

In your case, I would have 2, one for the header and one for the detail. Often, the client has to extract the header anyway: why should I flatten it (eg bloat it) when the client can't use it like that?

The other area I use it is web pages. Each call to the database is enough data for one action or pop-up on the screen (assume no caching for now).

Say, one header row, 5, details rows + lookup rows for dropdown for the detail rows.

The other reason is performance. SQL queries can be tuned, C# code can be tweaked, but round-trips (eg web <-> database) can not be.

Answer 5

If your client consuming the data coming back from the stored procedure can properly handle multiple result sets, then no, there's absolutely no reason not to use them.

They have several advantages, as you've already pointed out yourself:
- reduce the amount of data transferred - keep the normalization, no unnecessary duplication of data - save you a roundtrip to the server compared to have to call two separate stored procs (one for header, one for details data)

So all in all - go for it! :-) Seems like an excellent idea to me.

Marc

Answer 6

My biggest worry with doing this is that the data between the two queries may not relate correctly if your database is highly transactional. In other words the second query might contain records that were not in the first query (as they were added just as the first query finished) and so you don't know the related data from them. This of course can also work in reverse where records from the first query were deleted before the second query ran.

Answer 7

Sounds like this doesn't apply to your situation, but if the stored procedure is called by another stored procedure, any data sets returned after the first one will be "invisible" -- that is, not accessible -- by the calling procedure. Makes it hard to do INSERT... EXECUTE... statements. (These sets will be returned to the calling application, of course.)