Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
russray
Starting Member
27 Posts |
 Posted - 2006-05-16 : 08:13:41
|
| I am hoping others from this forum can help me. I am looking for examples or information that explains the concept of a resultset that has a resultset as one of its columns. Let me explain. A Customer may have many Accounts. An Account may have many Account Activities. An AcctActivity may have many Documents for each activity. What we are currently doing is pulling back an initial Resultset for the Customer information from the CallableStatement. Also returned are cursors in the Callablestatement for the different parts for the Customer---Accounts, AcctActivites, and Documents. What were are doing is assembling the relationship between Customer, Accounts, Account Activities, and Documents. As you can surmise this is very expensive for customers with many accounts, account activities, and Documents. The performance shows.What we want to do is change to the way the results are returned. For example, we want to pull back a resultset for a Customer and within the Customer resultset is a column for Accounts. Within the resultset for Account is a column for Activities. Within the resultset with AcctActivity is a column for Documents involved in such activities. This way we are starting with a resultset where the data is closely coupled and will reduce the amount of time necessary for assembling the relationship. Our goal here is reducing the all the looping for assembling the data and not increasing the number of trips to the database.Thank you for taking the time to real my post and for any suggestions you may provide.Russ |
|
|
Kristen
Test
22859 Posts |
Posted - 2006-05-16 : 08:36:47
|
| We tend to do this by returning 2 resultsets - first one is Customers, Second is Accounts [for Customers in the first resultset, ORDERed by the same ordering as Result Set 1] and so on.Then, client side, we "walk" all three Result Sets together, moving to the next row in Result Set 1 when there are no more corresponding rows in Result Set 2 ... and so on.Pretty efficient in terms of there being only one round-trip from Application-to-SQL-ServerKristen |
 |
|
|
Srinika
Master Smack Fu Yak Hacker
1378 Posts |
Posted - 2006-05-16 : 08:42:06
|
| U can use JOINs and with 1 statement u can return all data u need at once and do the processing in the front end.Eg.Select c.AccountID, c.[Name], a.Balance ..... fromCustomer cinner Join Account a on c.AcountID = a.AcountID inner Join AcctActivity aa on .....Srinika |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2006-05-16 : 09:00:26
|
| ADO (not .Net) supports nested hierarchical resultsets using the MSDataShape provider, this article has an example:http://www.4guysfromrolla.com/webtech/060301-1.shtml |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-05-16 : 09:05:15
|
| "u can return all data u need at once"Although if the width of the columns in Customer are large you will be using significant amounts of bandwidth to get the data, which is repeated on adjacent rows.Kristen |
|
|
|
russray
Starting Member
27 Posts |
Posted - 2006-05-16 : 10:19:51
|
| Thank you All! What you did was draw my attention to the creation of SQL Reference Types and how we can go about doing this. |
|
|
|
|
|
|
|
|
Resultset within a Resultset