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

[elaine3839]

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!

[abhaysk]

NO

[Orca777]

Hi,
If there are no sorts it could be;
Orca

[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).

b) Is there a "global" setting - abhaysk answered this.

[abhaysk]

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)

[jmodic]

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.

[DaPi]

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.

[DaPi]

Originally posted by jmodic
It entirely depend on the query itself.

Of course . . . .

[abhaysk]

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.

[DaPi]

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.