Hacker News Reader

Comment by philliphaydon | Hacker News Reader

SQL Server supports returning multiple result sets, i.e

select * from Product select * from Category

Then in C# you can pull out 2 different result sets and get two different collections. With 1 call to the database. So no round trips.

PostgreSQL supports multiple result sets if they contain the same columns. But the drivers don't support them. :(

View on HN

Seems like you ought to be doing a JOIN here, otherwise your're returning completely un-related result sets and crunching them in your app logic. It's usually best to let the DB do the data crunching and just return out what you need.

It was a trivial example. Are you saying that you never write code that needs multiple un-related, un-joinable sets of data out of the database?

Even with this example - I need a list of all products and I need a list of all categories... Joining them means I have to write client code to split them up again for display.

In any case, I use this tactic a lot for CRUD apps and it makes everything quicker when I can call one stored procedure and get back 7 lists of stuff that I need to display on my page.

> Are you saying that you never write code that needs multiple un-related, un-joinable sets of data out of the database?

Well, I''m not going to say never, but I will say very rarely. The entire point of a RDBMS is to have related data.

> I need a list of all products and I need a list of all categories

Sure maybe, but in our ecommerce platform products have a field which is an id of a category and is linked via FOREIGN KEY. Each category has another id which is it's parent category... so you traverse upwards until you build the entire category path.

I can see wanting to eliminate a round-trip, but one could also just do two separate queries and then cache the results...

I can see why this feature might be a nice-to-have, but I don't think that single case is enough to justify using that DB exclusively (if it were that much af a demanded feature, I'd wager other DB's would have implemented it by now, especially heavy-weights like Oracle).

Let the search results speak for themselves. Try doing a search for: <database-type> multiple result-sets and you will see a lot of requests on how to do it.

For instance - https://www.google.com/search?q=oracle+return+multiple+resul...

Also, have you considered that you don't really know how useful this technique can be since this features doesn't exist in the databases that you use? In any case, availability of features ultimately dictates style. (And I bet money that if you looked in your code, you'd find a lot of places with multiple trips to the database.)

When you look at the search results, you'll find that there are some kludgy ways for people to work around this limitation in Oracle, PostgreSQL and MySQL. (Actually MySQL might have this feature now.) So I'm sure plenty of people are settling for the kludge and moving on instead of complaining.

Beyond retrieving the contents of several unrelated enum-like tables in one roundtrip, multiple result sets are also super-useful for grabbing a deep relationship tree of joined tables in one roundtrip.

Writing a single projected SELECT with a deep list of JOINs causes the single result set wire size to explode as you include more and more to-many relations. However, breaking up the joins into separate SELECT roundtrips to reduce wire size will increase latency.

With multiple result sets, you can take all the separate selects, stuff them up in a stored procedure, connect them with insert joins through temp tables, then return multiple result sets from the temp table contents. This gives the best of both worlds: low latency from one roundtrip and a small wire footprint.

Reply on news.ycombinator.com