CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Results 1 to 10 of 10
  1. #1
    Join Date
    Apr 2000
    Location
    Belgium (Europe)
    Posts
    4,626

    continuing a previous SELECT TOP X query

    Suppose I have a query with a "complex" filtering condition
    SELECT
    <fields>
    FROM
    <tables>
    WHERE
    <complex condition on non-indexed fields>
    ORDER BY
    <indexed fields>

    Now further suppose the database is very large (as in hundredthousands of records)

    And this query is part of a webservice (which is what I'm writing) used from a third party website (which I'm not).


    In order to limit the time the DB is busy processing the request, and because I don't want to return potentially thousands of records at once, I want to retrieve a set number of records at a time which would be presented on the website one page at a time with previous/next buttons.


    I know I can restrict the sql query with say "TOP 10", but what would be a good way to do the next page (11 to 20) and the page after (21 to 30) etc. in such a way that the DB doesn't have to restart the entire query over from the start. or in other words, what's the "optimal way" to continue a previous (set of) TOP X

  2. #2
    Arjay's Avatar
    Arjay is offline Moderator / EX MS MVP Power Poster
    Join Date
    Aug 2004
    Posts
    13,490

    Re: continuing a previous SELECT TOP X query

    Are you using SQL 2012? If so, can you use the new fetch/offset order by parameters?

    http://sql-articles.com/articles/gen...l-server-2012/

  3. #3
    Join Date
    Apr 2000
    Location
    Belgium (Europe)
    Posts
    4,626

    Re: continuing a previous SELECT TOP X query

    We haven't settled on a DB manager yet, ideally we would want to be supporting several. I'm not sure we can "require" MSSQL 2012, some of our smaller customers might have budget issues with that. A more generic solution would be prefered.


    I'm not sure I fully understand the Offset / Fetch next approach. Or rather, how the DB manages to optimize this efficiently. The issue here is the "complex condition".

    if I do an offset X, fetch next Y. How does this prevent the DB from starting the query all over again, and rerunning the query to find the first X records. It is very possible that it may need to process thousands and thousands of records to obtain the first top X.

    If what the DB does is "fetch top X+Y, and don't return first X in the resultset" then this probably won't work efficiently enough.

  4. #4
    Arjay's Avatar
    Arjay is offline Moderator / EX MS MVP Power Poster
    Join Date
    Aug 2004
    Posts
    13,490

    Re: continuing a previous SELECT TOP X query

    Quote Originally Posted by OReubens View Post
    if I do an offset X, fetch next Y. How does this prevent the DB from starting the query all over again, and rerunning the query to find the first X records. It is very possible that it may need to process thousands and thousands of records to obtain the first top X.

    If what the DB does is "fetch top X+Y, and don't return first X in the resultset" then this probably won't work efficiently enough.
    I don't know how it works internally. Could you take some performance metrics and see if the results return consistently? If the implementation internally is correct, I would expect the timings of the last set of records to be similar to the first set.

  5. #5
    Join Date
    Apr 2000
    Location
    Belgium (Europe)
    Posts
    4,626

    Re: continuing a previous SELECT TOP X query

    I don't have MSSQL 2012 installed, so can't do any tests atm, nor can I easily convert the current database to MSSQL. This whole thing is currently ISAM based, and I'm looking at doing the same functionality with the SQL/ODBC layer, so we can transitionaway from ISAM and into relational. With ISAM, this whole thing is easy enough, we simply return the record position for each record, and for a next/previous, we call the webservice with either the record position of the first record in the current "page" (for a previous) or the last record (for a next).

    Ideally we want this to work with any db that has a sufficiently compatible SQL syntax/features. We have customers on MSSQL already, but for the smaller customers, MSSQL really isn't an option because of the pricing.

  6. #6
    VictorN's Avatar
    VictorN is offline Super Moderator Power Poster
    Join Date
    Jan 2003
    Location
    Hanover Germany
    Posts
    20,396

    Re: continuing a previous SELECT TOP X query

    How about Microsoft® SQL Server® 2012 Express? It is free...
    Victor Nijegorodov

  7. #7
    Arjay's Avatar
    Arjay is offline Moderator / EX MS MVP Power Poster
    Join Date
    Aug 2004
    Posts
    13,490

    Re: continuing a previous SELECT TOP X query

    Quote Originally Posted by VictorN View Post
    How about Microsoft® SQL Server® 2012 Express? It is free...
    Yep. MS now has several low cost SQL options.

  8. #8
    Join Date
    Apr 2000
    Location
    Belgium (Europe)
    Posts
    4,626

    Re: continuing a previous SELECT TOP X query

    not really useful if it needs to be multiuser now is it
    of did MS change the express to be simultaneously multiuser now ?

  9. #9
    VictorN's Avatar
    VictorN is offline Super Moderator Power Poster
    Join Date
    Jan 2003
    Location
    Hanover Germany
    Posts
    20,396

    Re: continuing a previous SELECT TOP X query

    I use SQL server beginning with the 2008 Express version. I never heard about any multiuser problem with it.
    FYI:
    http://social.msdn.microsoft.com/For...5-785831d319ec
    http://www.sqlmag.com/article/databa...capable--49736
    Victor Nijegorodov

  10. #10
    Arjay's Avatar
    Arjay is offline Moderator / EX MS MVP Power Poster
    Join Date
    Aug 2004
    Posts
    13,490

    Re: continuing a previous SELECT TOP X query

    Quote Originally Posted by VictorN View Post
    I use SQL server beginning with the 2008 Express version. I never heard about any multiuser problem with it.
    FYI:
    http://social.msdn.microsoft.com/For...5-785831d319ec
    http://www.sqlmag.com/article/databa...capable--49736
    From
    http://www.microsoft.com/en-us/sqlse...s/express.aspx
    SQL Server Express is a free edition of SQL Server ideal for developing and powering desktop, web and small server applications

Posting Permissions

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





Click Here to Expand Forum to Full Width

Featured