DBAsupport.com Forums - Powered by vBulletin
Page 2 of 2 FirstFirst 12
Results 11 to 17 of 17

Thread: No of rows fetched ...

  1. #11
    Join Date
    Apr 2001
    Posts
    118

    Smile Your explanation was quite clear

    Thanks for taking the time to straighten me out. :)

  2. #12
    Join Date
    Mar 2001
    Posts
    19
    I modified the below query posted by shiva in the following way, is this query correct??. I tested as well and it is working fine. I cannot understand why shiva has included another inline query in his subquery, can anyone xplain for me please please.

    thanks
    Seenu

    My query
    ------------------

    select messageID
    from
    (select messageID, rownum as newcnt
    from messages
    where rownum <= 3)
    where newcnt > 0


  3. #13
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    Please re-read my explanations in this thread and the other referenced threads to fully understand the solution.

    Your solution won't work if the result set needs to be ordered, and windowing generally only makes sense with an ordered result set. However, if ordering is not necessary, then your solution will work. I must admit I find it a little humorous that your complaint has to do with what you consider an extraneous layer to the original query while your example truly has one - checking for rownum>0 is definitely extraneous . Don't worry, I know where you were going, just found it funny.

    Also, Shiva never actually posted a query, so I'm not actually sure which query you were referring to, not that it really matters though.

    - Chris

  4. #14
    Join Date
    May 2001
    Posts
    14
    To further extend the discussion
    I need 500 to 600 of 100000 records selected

    here 100000 is the total number of records
    of which I am displaying 500 - 600
    ordered on some column ,.


    What is the optimal solution here?
    We use scrollable result set in Java . But I hear that in the above case all 100000 records have to be transfered across the wire .

    SOmoene suggested that inline views are very slow and degrades performance. Is this true ?

  5. #15
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    Originally posted by ssinha
    SOmoene suggested that inline views are very slow and degrades performance. Is this true ?
    Uh, no.

    As for the optimal solution, I would still propose the ROWNUM solution. However, it would depend upon certain variables.

    The reason the ROWNUM solution works so well is that the optimizer utilizes a STOPKEY method, which simply means that it will stop sorting once it reaches the upper limit (in your case, 600). So, for a single page, a lot of sorting has been avoided. However, if your user is going to end up paging through *the entire* resultset, then we will have actually performed more querying and sorting than if we only pulled the entire resultset once. In other words, if you simply selected all 100,000 rows into a local buffer and scrolled the user through that buffer, you would only have run the query once and sorted the resultset once. However, it is generally true that in *most* searches, where windowing is usually used, very few users will page through more than 5 pages. Most will only page once, maybe twice. Therefore, only sorting and returning the bare minimum number of rows is a better solution. If the user will always use a majority of the resultset, then a single mass query is preferred.

    However, one must ask oneself how much use 100,000 rows of information will be to *anyone* on-line. Such massive queries are best left to nightly batch reporting, not on-line queries that the user scrolls through (windowing).

    Food for thought,

    - Chris

  6. #16
    Join Date
    May 2001
    Posts
    14
    thanks Chris
    I have more questions hopfully someone can help

    Yes most users dont scroll through all 100000 records. But still the requirement is to show for example rows 100 -200 and also display the total count at the bottom off the page which is 100000.

    TO get the total count I have to do a count(*) query


    I also would like note that the above query has atleast 10 tables and many of which is outer join and hierarchial.,
    Does outerjoin cause full table scans or is there anyway to tune all the above combined ?

  7. #17
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    The first thing to try is to get the requirement removed. It is costly to get that count. However, if you definitely need it, then there are a few options:

    - Optimize the query that gets the count. Most queries contain joins to lookup tables and the like to translate codes to user-readable text, etc. Such joins might be able to be eliminated it the COUNT(*) query

    - Only run the count(*) query on the first page - don't run it for every page

    - If the COUNT query takes a long time anyway, it might be worth re-visiting the '1 big query' approach. Run the entire query and cache all the results. I wouldn't do this blindly - test the various options and know how many pages are usually viewed by the user.

    HTH,

    - Chris

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