Professional Web Applications Themes

Sql Server Paging/Limit of Resultsets - Microsoft SQL / MS SQL Server

Hi, I have a solution that I've been developing that allows me to apply paging to just about any allowed query. It appears to be just as efficient as other methods I have discussed, however I have a need to know the record count, which tends to slow things down. I use it as follows: DECLARE RowCount int exec usp_ExecSqlWithPaging N'SELECT * FROM pubs.dbo.Authors',1,10,RowCount out Limitations: -It uses the odbc cursor stored procs -It uses a select into to create the output table. -While it can handle most queries (although I have not thoroughly tested it), you must have unique ...

  1. #1

    Default Sql Server Paging/Limit of Resultsets

    Hi,

    I have a solution that I've been developing that allows me to apply
    paging to just about any allowed query. It appears to be just as
    efficient as other methods I have discussed, however I have a need to
    know the record count, which tends to slow things down.

    I use it as follows:
    DECLARE RowCount int
    exec usp_ExecSqlWithPaging N'SELECT * FROM
    pubs.dbo.Authors',1,10,RowCount out

    Limitations:
    -It uses the odbc cursor stored procs
    -It uses a select into to create the output table.
    -While it can handle most queries (although I have not thoroughly
    tested it), you must have unique column names in the select list, so
    if you have two tables in a join that have the same column name, and
    you want both, you will need to alias one. Seems to perform just as
    well as other methods I have produced, with less confusion.

    Other possible changes:
    -I was considering trying to remove the select into by querying the
    cursor for its columns.
    -I was considering using standard SQL cursor statements to produce the
    results, however I can't use INSERT INTO #tmp on a FETCH statement, so
    I would need to generate the parameter list to be inserted into first.

    I'm always looking for better ways to do this, so please post
    improvements if you have any.

    CREATE PROCEDURE [dbo].[usp_ExecSqlWithPaging]
    SqlIn nvarchar(4000),
    Page int,
    PageSize int,
    rowcount int out
    AS
    SET NOCOUNT ON

    --DECLARE SqlIn nvarchar(4000)
    --declare page int
    --declare pagesize int
    --SET SqlIn = 'SELECT DISTINCT CommunityName FROM cmd.dbo.community c
    inner join cmd.dbo.subscriber s on (c.communityid=s.communityid) WHERE
    c.CommunityName LIKE ''A%'' ORDER BY CommunityName'
    --set page = 15
    --set pagesize=20

    DECLARE SelectPos int
    DECLARE FromPos int
    DECLARE WherePos int
    DECLARE OrderByPos int
    DECLARE EndOfFromPos int
    DECLARE EndOfWherePos int
    DECLARE GroupByPos int
    DECLARE HavingPos int

    declare select_list nvarchar(1000)
    declare table_source nvarchar(1000)
    declare filter nvarchar(1000)
    declare sortby nvarchar(1000)

    SET SqlIn = LTRIM(SqlIn)
    SET SelectPos = CHARINDEX('SELECT ',SqlIn)
    if (SelectPos<1) begin rollback return end
    SET FromPos = CHARINDEX(' FROM ',SqlIn)
    if (FromPos<1) begin rollback return end
    SET WherePos = CHARINDEX(' WHERE ',SqlIn,FromPos+6)
    SET GroupByPos = CHARINDEX(' GROUP BY ',SqlIn,FromPos+6)
    SET HavingPos = CHARINDEX(' HAVING ',SqlIn,FromPos+6)
    SET OrderByPos = CHARINDEX(' ORDER BY ',SqlIn,FromPos+6)
    if (WherePos<0) SET WherePos=0
    if (GroupByPos<0) SET GroupByPos=0
    if (HavingPos<0) SET HavingPos=0
    if (OrderByPos<0) SET OrderByPos=0
    SET EndOfFromPos = coalesce(NULLIF(WherePos,0),NULLIF(GroupByPos,0) ,NULLIF(HavingPos,0),NULLIF(OrderByPos,0),Len(S qlIn)+1)

    if (WherePos>0)
    begin
    SET EndOfWherePos =
    coalesce(NULLIF(GroupByPos,0),NULLIF(HavingPos,0 ),NULLIF(OrderByPos,0),Len(SqlIn)+1)
    SET filter = substring(SqlIn,WherePos+7,EndOfWherePos-WherePos-7)
    end else SET filter=''

    if (OrderByPos>0)
    begin
    SET sortby=substring(SqlIn,orderbypos+10,Len(SqlIn )-orderbypos-9)
    end else SET sortby=''

    SET select_list = substring(SqlIn,SelectPos+7,FromPos-SelectPos-7)
    SET table_source = substring(SqlIn,FromPos+6,EndOfFromPos-FromPos-6)

    declare startrow int
    set startrow=Pagesize*(Page-1)+1

    DECLARE execsql nvarchar(4000)
    SET execsql = 'SELECT TOP 0 ' + select_list + ' INTO #tmp FROM ' +
    table_source + ' WHERE 1=0
    declare cursor int
    insert into #tmp
    exec sp_cursoropen cursor out, Sql
    SET rowcount=CURSOR_ROWS
    insert into #tmp
    exec sp_cursorfetch cursor,16,startrow,pagesize
    exec sp_cursorclose cursor
    SELECT * FROM #tmp
    '
    exec sp_executesql execsql,N'Sql nvarchar(4000),startrow
    int,pagesize int,rowcount int
    out',SqlIn,StartRow,PageSize,rowcount out

    SET NOCOUNT OFF
    GO
    Tony Guest

  2. #2

    Default Re: Sql Server Paging/Limit of Resultsets

    Using undoc xp's in production is never recommended. Why not do something
    like this...

    CREATE PROCEDURE sp_PagedItems2 (
    Page int,
    RecsPerPage int
    )
    AS
    -- We don't want to return the # of rows inserted
    SET NOCOUNT ON

    --Create a temporary table
    CREATE TABLE #TempItems (ID int IDENTITY,
    FirstName nvarchar(100))

    -- Insert the rows from tblItems into the temp. table
    INSERT INTO #TempItems (FirstName)
    SELECT FirstName From Agents

    -- Find out the first and last record we want
    DECLARE FirstRec int, LastRec int
    SELECT FirstRec = (Page - 1) * RecsPerPage
    SELECT LastRec = (Page * RecsPerPage + 1)

    -- Now, return the set of paged records, plus, an indiciation of we
    -- have more records or not!
    SELECT *, MoreRecords = (SELECT COUNT(*) FROM #TempItems TI WHERE TI.ID 
    FROM #TempItems
    WHERE ID > FirstRec AND ID < LastRec



    --
    -oj
    RAC v2.2 & QALite!
    http://www.rac4sql.net



    "Tony Wright" <com> wrote in message
    news:google.com... 
    coalesce(NULLIF(WherePos,0),NULLIF(GroupByPos,0) ,NULLIF(HavingPos,0),NULL
    IF(OrderByPos,0),Len(SqlIn)+1) 
    coalesce(NULLIF(GroupByPos,0),NULLIF(HavingPos,0 ),NULLIF(OrderByPos,0),Le
    n(SqlIn)+1) 


    oj Guest

  3. #3

    Default Re: Sql Server Paging/Limit of Resultsets

    Hi Oj,

    Microsoft does not provide me with an efficient way to do record
    paging, so I will do what I can to emulate this functionality which is
    available in MANY Other Databases. In fact, Microsoft actually
    recognises a need for this functionality as they implement a kind of
    record paging in Commerce Server.

    It actually doesn't bother me that the stored procs used are
    undoented - firstly, they are the stored procs that ODBC uses,
    which I hardly think Microsoft is ready to discard, and secondly, the
    routine will end up on a web site, which will not need to be upgraded
    any time soon.

    I have never like the method you propose because it requires a lot of
    set up just to return a page from a query. You are effectively dumping
    the entire contents of your table into the temp table just to return
    one page. Are you aware of how expensive the IDENTITY statement is on
    a large table?

    On the other hand, I am dumping the page only into the temp table
    before selecting from it.

    Also, I can put just about any query I like in the select string
    (provided the select list contains only unique fields), making any
    routine that I want to add paging to, significantly easier to read and
    debug. Don't get me wrong, your method will work, but this method
    gives us an option that we haven't really had in the SQL Server
    before.

    Give me the following any day, as it is significantly more flexible:
    DECLARE RowCount int
    DECLARE Page int
    DECLARE PageSize int
    SET Page=2
    SET PageSize=10
    exec usp_ExecSqlWithPaging N'SELECT * FROM
    pubs.dbo.Authors',Page,PageSize,RowCount out

    Microsoft, are you listening?

    Regards,
    Tony



    I prefer the following method of paging for large tables (you will
    need to substitute PageSize and (Page * PageSize) with real numbers or
    it doesn't work otherwise):

    SELECT TOP PageSize * FROM (
    SELECT TOP PageSize * FROM (
    SELECT TOP Page * PageSize FROM Table_Source ORDER BY PKID
    ) as T1 ORDER BY PKID DESC
    ) as T2 ORDER BY PKID



    "oj" <com> wrote in message news:<phx.gbl>... 
    > FROM #TempItems
    > WHERE ID > FirstRec AND ID < LastRec
    >
    >
    >
    > --
    > -oj
    > RAC v2.2 & QALite!
    > http://www.rac4sql.net
    >
    >
    >
    > "Tony Wright" <com> wrote in message
    > news:google.com... 
    > coalesce(NULLIF(WherePos,0),NULLIF(GroupByPos,0) ,NULLIF(HavingPos,0),NULL
    > IF(OrderByPos,0),Len(SqlIn)+1) 
    > coalesce(NULLIF(GroupByPos,0),NULLIF(HavingPos,0 ),NULLIF(OrderByPos,0),Le
    > n(SqlIn)+1) [/ref]
    Tony Guest

  4. #4

    Default Re: Sql Server Paging/Limit of Resultsets

    I checked the previous thread, and I am wondering about some possible
    improvements.

    Would changing the cursor to a LOCAL or VARIABLE cursor make any
    difference to performance, and would changing the cursor type from
    DYNAMIC to SCROLL also make a difference.

    Thanks,
    Tony

    "Vlad Vissoultchev" <myrealbox.com> wrote in message news:<phx.gbl>... 
    Tony Guest

Similar Threads

  1. MS SQL Recordset paging (on DB server)
    By nedflanders in forum Coldfusion Database Access
    Replies: 4
    Last Post: October 31st, 03:31 PM
  2. turning Resultsets Using Sql Server Stored Procedures...
    By Amber in forum ASP.NET Data Grid Control
    Replies: 0
    Last Post: October 7th, 03:05 AM
  3. Replies: 2
    Last Post: July 30th, 07:29 AM
  4. Paging resultsets in SQL Server - another solution
    By Tony Wright in forum Microsoft SQL / MS SQL Server
    Replies: 0
    Last Post: July 3rd, 11:30 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139