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

performancesybase-ase

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

I know of two ways of doing this:

SET ROWCOUNT

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

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

Best Answer

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.

Related Solutions

SQL Server – How to Optimize a Slow Query with Nested Loops Inner Join

The problem appears to be in this part of the code:

JOIN category_link l on l.sku_id IN (SELECT value FROM #Ids) AND
(
    l.category_id = c4.category_id OR
    l.category_id = c5.category_id
)

or in join conditions is always suspicious. One suggestion is to split this into two joins:

JOIN category_link l1 on l1.sku_id in (SELECT value FROM #Ids) and l1.category_id = cr.category_id
left outer join
category_link l1 on l2.sku_id in (SELECT value FROM #Ids) and l2.category_id = cr.category_id

You then have to modify the rest of the query to handle this . . . coalesce(l1.sku_id, l2.sku_id) for instance in the select clause.

Sql-server – difference in performance between @date and getdate()

The answer is - you have to test it to find out.
I did a test of my own on a table which has ~8,000,000 rows

DECLARE @date DATETIME
SET @date = GETDATE()
;
SELECT T.DateCol, DATEADD(dd,-100,@date)
FROM dbo.TableName AS T
WHERE T.DateCol > DATEADD(dd,-100,@date)
;
SELECT T.DateCol, DATEADD(dd,-100,GETDATE())
FROM dbo.TableName AS T
WHERE T.DateCol > DATEADD(dd,-100,GETDATE())
;

In my case the use of the variable caused SQL Server to estimate an expensive plan:

enter image description here
the reason is that SQL Server builds the query plan during compilation,
at that time the value of the variable is not yet known and SQL Server can not use the statistics.
I suggest you read about parameterization

There are other cases where the use of the variable will cost less then calling a function many times,
so here it is again - TEST TEST, and TEST :)

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

Best Answer

Related Solutions

SQL Server – How to Optimize a Slow Query with Nested Loops Inner Join

Sql-server – difference in performance between @date and getdate()

Related Question