Is there performance difference between “SET ROWCOUNT” and “TOP”?

Question

I need to select batches of N rows from a large table.

I know of two ways of doing this:

1. SET ROWCOUNT

   SET ROWCOUNT ####
   SELECT something FROM table WHERE clauses
   SET ROWCOUNT 0 -- rowcount is only needed for the one query above.
   

2. TOP

    SELECT TOP ### something FROM table WHERE clauses
   

Is there any performance difference (or any other reason to pick one over another) between the two solutions?

Notes:

• If it matters, Sybase ASE is version 15.
• The query is a single SELECT statement. The limit will not apply to any other statements (so usual reasons to pick rowcount over top don't apply).
• The order of the rows does not matter

Research done:

• This SO Q&A has accepted answer which claims no difference without any supporting references; and another answer that shows a possible un-measured difference for MS SQL server (which I'm not sure applies to Sybase as the reference is from Microsoft).

Answer 1

SET ROWCOUNT ####
SELECT something FROM table WHERE clauses
SET ROWCOUNT 0 -- 

In this query ,optimizer do not include "SET ROWCOUNT ####" part in query plan therefore no cost.

It is rarely use and there is not much reason to use because we are not able to specify any criteria.

SELECT TOP ### something FROM table WHERE clauses

Generally TOP is accompanied by order clause though not necessary.

TOP is evaluated query plan hence it has cost.

so SET rowcount will perform little better.

Main Difference

TOP work only on the query it is use.

SET ROWCOUNT work for current SCOPE.There for all query are affected by SET ROWCOUNT in that scope including trigger.

Suppose ,

SET RowCount 2

and actually (by logic) 4 rows should be affected in trigger .but only 2 rows will be affected because of SET RowCount 2

So it is not safe to use SET ROWCOUNT.

I heard SET ROWCOUNT will be depreciated in next version.

Answer 2

Generally speaking ... performance should be the same.

Technically speaking ... it depends on the query; for example, if the TOP is used within the confines of a derived table then you may see different performance depending on how/if the optimizer flattens the query.

To answer the question for a given query(s), run some tests with the following options enabled:

set showplan on
set statistics io,time on
go
-- set rowcount XXX
select [top] ...
-- set rowcount 0
go

You'll want to run the query a couple times to make sure data has been pulled from disk so that you can compare in-memory data queries to each other.

Obviously you'll then want to compare the 2x query plans as well as the IOs and timings (keeping in mind that times can vary greatly based on duration of blocking and/or time spent on the wait queue waiting to run on a dataserver engine). IOs should be comparable on repeat runs, but for timings you'll likely want to run each query a few times and take the average.