Web Development

General discussion


SQL/ASP paging of relational data

By craig ·
I'm trying to page through a large amount of data (thousands of records) obtained from SQL server in an ASP application. The problem is that the data involves a one-to-many relationship. Let me try to explain: I have table1 and for each record intable1 there are varying numbers of child records in table2, I want to page through 10 records at a time from table1 while also displaying all of the records in table2 for each of the 10 records (in reality the query is much more complex and involves about 10 tables). So when the stored procedure returns the joined result set (which can be sorted & filtered based on several supplied parameters to the sp and involving values from both table1 & table2), contains multiple records for each record in table1. I've come up with a few solutions, none of which I feel are adequate:

1) Use the MS data shaping provider to shape the data (basically remove the redundant records and attach a sub record set with the table2 values) and then use ADO topage it. Unfortunately this requires that all of the data must be transferred to the web server.

2) Do you query and only returns the distinct ID's from table1, page the results with ADO and do another query (or 10 separate queries) to obtain the full records for that particular page. Again, all of the ID's must be transferred to the web server, not a huge problem, but the query must be run twice and as I said it's somewhat complex.

Does anyone know of a better solution?


This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Comments

Related Discussions

Related Forums