Paging very large record sets

Results 1 to 2 of 2

Thread: Paging very large record sets

  1. #1
    Join Date
    Dec 1969

    Default Paging very large record sets

    Hello.<BR><BR>Problem Background:<BR><BR>I am working on a bookstore web site that has a product database table of over 100,000 records. This table contains information such as book name, description, table of contents etc. The table is called product_information. There is other product information contained in linked tables, such as publisher name.<BR><BR>These books are divided into categories on our site, this relationship is stored in a product_categories table. The site has approx. 400 categories.<BR><BR>Problem:<BR><BR>Some of our categories contain upwards of 10,000 products. We have to allow paging of these records, and these paged results have to be sortable by three fields - book name, price and publisher.<BR><BR>We are currently doing this paging with an SQL Server stored procedure. This takes all the 10,000 records from our product_information table and then run through them, sorting them according to the users selection. This is slow and as we get more products will get slower.<BR><BR>So my problem is, I need a faster way to return paged results to the user.<BR><BR>Solutions?<BR><BR>Each night, we could run a scheduled stored procedure that returns sorted paging results for each of our categories. We could do this for each of the sort columns and store the results in a database table.<BR><BR>That is, we would create three tables for each category, one for each of the sort methods. <BR><BR>So one of these table might be something like Childrens_Books_Category_Sorted_By_Name, this would contain something like the following fields:<BR><BR>Unique Index<BR>Report_ID<BR>Book_Name<BR>Description<BR> Publisher_Name<BR>Price<BR>etc etc<BR><BR>This would contain the sorted results, so we could simply do Selects from this rather than having to work through and sort our 100,000 record table.<BR><BR>Obvious problem with this solution:<BR><BR>If the details of a book are updated during the day these changes will not show until the following day when paging results.<BR><BR>Another option may be to run a job to save page results in XML format.<BR><BR><BR>I would appreciate anyone’s thoughts on the above and any ideas you can suggest.<BR><BR>Best Regards,<BR><BR>Ross

  2. #2
    Join Date
    Dec 1969

    Default You want a quicky hack?

    Do as you suggested, building the indices at night.<BR><BR>And then when things are added during the day, simply bring them up separately as "***NEW TODAY***". Making it look like you&#039;re giving the user the advantage of seeing the latest stuff, making a feature out of a necessity!<BR><BR>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts