SQL Server Performance Forum – Threads Archive

Single tables vs. tables with nested stored proc

I have a table of data. It is large and pulls from many tables to build the data set. I was able to build the table in RS using grouping levels to achieve the desired result visually. Another co-worker built a similar table structure but they used nested tables/stored procs to achieve the same results. I tried to illustrate how this is drawn out visually here…. + Group 1
+ Item A
+ Object A1
– Item B
– Item C
+ Object C1
+ Object C2
+ Group 2
+ Item D
+ Object D1 I use one stored proc to build this table and then use RS to split it up with the grouping levels. The second method is to use one stored proc to get the group level, then another to build the item level and another to build the object level. Each SP has values passed to it through the subreport. My question after all that is which one is a better method? When does RS actually execute the nested subreports? If they are all executed at the same time, then wouldn’t the first method be superior because it could be optimized? Or would the second method be better because each SP could be finely tuned? Any answers to these ramblings would be appreciated. mike

---

]]>