-
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!
-
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"
-
Hi,
If there are no sorts it could be;
Orca
-
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
-
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"
-
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?
-
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
-
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
-
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"
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|