Post reply

return a subset from a query

  • November 21, 2003 at 6:42 am

    #106761

    I have query which returns 25,000 rows which are displayed in the application by paging. each time it shows 50 records in the application. user can view other records by navigating using pages.

    this query returns data from 5 tables(all of them have more than 1,00,000 records). i am using temporary table in this query for paging. is there any way i can retrive subset of records from the query say from 100th record to 150 record with out using temp table.

    thanks in advance

    Pratap

  • November 21, 2003 at 7:17 am

    #483081

    Well, apart from the fact that there isn't such a thing as the 150th row, you might take a look at http://qa.sqlservercentral.com/forum/link.asp?TOPIC_ID=17248 or http://www.planet-source-code.com/vb/default.asp?lngCId=633&lngWId=5 or http://qa.sqlservercentral.com/forum/link.asp?TOPIC_ID=13087

    Frank

    http://www.insidesql.de

    http://www.familienzirkus.de

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • November 21, 2003 at 8:05 am

    #483112

    I do something similar, but my largest recordset is about 15000 rows. I don't know how long your query takes, but what I do is return the TOP (pagesize*pagenumber) records and then go to the pagesize*(pagenumber -1) record and display to the end of the record set.

    It sounds whacky, but it works and is quite fast in our situation.

    So, for the 8th page of a recordset with a pagesize of 50, I would

    Select Top 400 Table.*

    then I use a moveto function to goto the 351st record and display from there to the end of the result set. This is an ASP application.

  • November 24, 2003 at 12:41 pm

    #483442

    I've always used the > clause and a unique index to page through my data. You store the ID on the page and pass that ID to a stored proc which grabs top n records > ID. If you're going to previous, you grab top n records < id sorted descending. Granted, this doesn't give you page count, goto, etc., but you don't always want that. One advantage to this method is you're only getting 50 records, so your server isn't taxed and you're not using much bandwidth. If you're using a clustered index, you'll gain performance for nexts, but resorts for previous will be slower.

  • November 25, 2003 at 5:48 am

    #483522

    If you are using an ADO recordset it has paging abilities.

    Far away is close at hand in the images of elsewhere.
    Anon.

  • December 3, 2003 at 11:21 pm

    #484558

    Check out this article "Using Inline UDFs for Precise Paging" at http://www.15seconds.com/issue/030113.htm

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply