SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Display Complex Hierarchical Data with Server-Side Pagination


Display Complex Hierarchical Data with Server-Side Pagination

Author
Message
Peichung Shih
Peichung Shih
SSC Rookie
SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)

Group: General Forum Members
Points: 35 Visits: 123
Comments posted to this topic are about the item Display Complex Hierarchical Data with Server-Side Pagination
Paul White
Paul White
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15568 Visits: 11355
Very nice work, thank you.



Paul White
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
SQLRNNR
SQLRNNR
SSC-Dedicated
SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)

Group: General Forum Members
Points: 32028 Visits: 18551
Nice work thanks. I added this to my briefcase for future reference.



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw

george 86905
george 86905
SSC Rookie
SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)

Group: General Forum Members
Points: 31 Visits: 52
Yes, a great post: very useful to know how the paging works. This is great for the single column recursive hierachial data for which it was intended.

On the other hand, on a related topic, is there any similiar thinking concerning how to deal with the more general case of non-recursive hierchial data which is found in multiple columns, for example, Country, State, and City, with visibility toggles for State and City.

Unlike the article EmployeeID example, in my case, I don't see a way to predetermine and then set page numbers for the numerous State and City rows and my reports with large recordset do indeed suffer the repaging performance hit described in the article. Any advice?
Peichung Shih
Peichung Shih
SSC Rookie
SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)

Group: General Forum Members
Points: 35 Visits: 123
Thanks for your comment. :-)

SQL Kiwi (12/22/2011)
Very nice work, thank you.

Peichung Shih
Peichung Shih
SSC Rookie
SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)

Group: General Forum Members
Points: 35 Visits: 123
Thanks for the feedback. I hope this article can be helpful someday.




SQLRNNR (12/22/2011)
Nice work thanks. I added this to my briefcase for future reference.

Peichung Shih
Peichung Shih
SSC Rookie
SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)

Group: General Forum Members
Points: 35 Visits: 123
I'd suggest to maintain data at the leaf-levels of the HIERARCHYID column and create artificial nodes for the roll ups. Let's say the HIERARCHYID column is called LocationId, and all the sales records have their LocationId set to cities. If state roll ups are needed, you can insert 50 artificial nodes into the hierarchy and point sales data's parent to the newly created state nodes; similarly, if country roll ups are needed, add country nodes to the hierarchy and then point state nodes to country nodes. Essentially, you maintain real data at the leaves and build the tree by inserting roll up nodes. I hope this helps. Please let me know if you have any questions. Thanks,

george 86905 (12/23/2011)
Yes, a great post: very useful to know how the paging works. This is great for the single column recursive hierachial data for which it was intended.

On the other hand, on a related topic, is there any similiar thinking concerning how to deal with the more general case of non-recursive hierchial data which is found in multiple columns, for example, Country, State, and City, with visibility toggles for State and City.

Unlike the article EmployeeID example, in my case, I don't see a way to predetermine and then set page numbers for the numerous State and City rows and my reports with large recordset do indeed suffer the repaging performance hit described in the article. Any advice?

george 86905
george 86905
SSC Rookie
SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)

Group: General Forum Members
Points: 31 Visits: 52
Thanks a lot for the advice. I'll have to digest your idea before I can comment on it, but at first glance, I can say that I will need a flexible solution that allows for an ever-changing groups of Countries and Cities.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search