Will limit the # of rows being returned speed up query execution?
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 16

Thread: Will limit the # of rows being returned speed up query execution?

  1. #1
    Join Date
    May 2001
    Posts
    285

    Unhappy Will limit the # of rows being returned speed up query execution?

    We have an application that in case the table has tons of rows, it will only return a relatively small set of data (say 1000 rows but it is configurable) for the user to work with.

    I know we can limit the number of rows returned by taking advantage of rownum, but don't know if doing this could speed up the query execution, i.e. if CBO will take advantage of the smaller result set and choose a different execution plan. (I heard SQL Server's optimizer will do, that's why I'd like to check).

    Also, can we set this option globally? (so when you write individual query, you don't have to always specify where rownum < 1000 etc)

    Thanks!

  2. #2
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    NO
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  3. #3
    Join Date
    Apr 2002
    Location
    Germany.Laudenbach
    Posts
    448
    Hi,
    If there are no sorts it could be;
    Orca

  4. #4
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    My! that's confusing! There were TWO questions:

    a) Will it be faster? - Orca answered this. My reply would also be "probably" - but remember that the rows will be a unpredictable sample of the total (well, effectively unpredictable).

    b) Is there a "global" setting - abhaysk answered this.
    "The power of instruction is seldom of much efficacy except in those happy dispositions where it is almost superfluous" - Gibbon, quoted by R.P.Feynman

  5. #5
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Originally posted by DaPi
    My! that's confusing! There were TWO questions:

    a) Will it be faster? - Orca answered this. My reply would also be "probably" - but remember that the rows will be a unpredictable sample of the total (well, effectively unpredictable).
    It wud be no as well.....query execution time will remain same....may it be SQL Server as well........(elaine3839:I heard SQL Server's optimizer will do, that's why I'd like to check)
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  6. #6
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Originally posted by abhaysk
    It wud be no as well.....query execution time will remain same....
    Nope. IT could be either YES or NO. It entirely depend on the query itself.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  7. #7
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    Originally posted by abhaysk
    It wud be no as well.....query execution time will remain same
    Not in my experience.

    I'm assuming we are in the realm of Full Table Scans. Simple cases I've just tried DO run faster - there is a "COUNT (STOPKEY)" in the Explain Plan. In complicated cases, but no sorts (i.e. joins over several tables) I would say I had a 3-out-of-4 chance of it being faster. If results are required to be sorted, then usually you won't win.

    If everything can be done the "fast" way (e.g. through indexes) then the improvement may be too small to be observable.
    "The power of instruction is seldom of much efficacy except in those happy dispositions where it is almost superfluous" - Gibbon, quoted by R.P.Feynman

  8. #8
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    Originally posted by jmodic
    It entirely depend on the query itself.
    Of course . . . .
    "The power of instruction is seldom of much efficacy except in those happy dispositions where it is almost superfluous" - Gibbon, quoted by R.P.Feynman

  9. #9
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    I didnt mean same executing time for diff queries.....

    it wud suerly be diff with sorts and with out....and ofcourse joins and so.......

    wat i meant was...."with rownum and with out it...if query wud give faster result was Q".....the ans wud be NO as far as execution time for the query is concerneced and not return of the o/p at client.

    Abhay.
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  10. #10
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    Originally posted by abhaysk
    wat i meant was...."with rownum and with out it...if query wud give faster result was Q
    Exactly what I meant too. As far as I can see, sometimes (often?) an FTS can be started, but stop when enough rows have been found - so it isn't really a "Full" Table Scan - it may have to read all the table to find 1000 rows satisfying the conditions, it might get them in first few blocks.
    "The power of instruction is seldom of much efficacy except in those happy dispositions where it is almost superfluous" - Gibbon, quoted by R.P.Feynman

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