CODEWITHSUNDEEP
sqlsql-servert-sqlquery-optimization
Tudor Carean
Tudor Carean

Reputation: 982

total number of rows of a query

I have a very large query that is supposed to return only the top 10 results:

select top 10 ProductId from .....

The problem is that I also want the total number of results that match the criteria without that 'top 10', but in the same time it's considered unaceptable to return all rows (we are talking of roughly 100 thousand results.

Is there a way to get the total number of rows affected by the previous query, either in it or afterwords without running it again?

PS: please no temp tables of 100 000 rows :))

Upvotes: 1

Views: 5432

Answers (4)

Unreason
Unreason

Reputation: 12704

Generally speaking no - reasoning is as follows:

If(!) the query planner can make use of TOP 10 to return only 10 rows then RDBMS will not even know the exact number of rows that satisfy the full criteria, it just gets the TOP 10.

Therefore, when you want to find out count of all rows satisfying the criteria you are not running it the second time, but the first time.

Having said that proper indexes might make both queries execute pretty fast.

Edit
MySQL has SQL_CALC_FOUND_ROWS which returns the number of rows that query would return if there was no LIMIT applied - googling for an equivalent in MS SQL points to analytical SQL and CTE variant, see this forum (even though not sure that either would qualify as running it only once, but feel free to check - and let us know).

Upvotes: 0

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239646

Assuming that you're using an ORDER BY clause already (to properly define which the "TOP 10" results are), then you could add a call of ROW_NUMBER also, with the opposite sort order, and pick the highest value returned.

E.g., the following:

select top 10 *,ROW_NUMBER() OVER (order by id desc) from sysobjects order by ID

Has a final column with values 2001, 2000, 1999, etc, descending. And the following:

select COUNT(*) from sysobjects

Confirms that there are 2001 rows in sysobjects.

Upvotes: 2

SnatchFrigate
SnatchFrigate

Reputation: 372

I suppose you could hack it with a union select

select top 10 ... from ... where ...
union
select count(*) from ... where ...

For you to get away with this type of hack you will need to add fake columns to the count query so it returns the same amount of columns as the main query. For example:

select top 10 id, first_name from people
union
select count(*), '' as first_name from people

I don't recommend using this solution. Using two separate queries is how it should be done

Upvotes: 0

SQLMenace
SQLMenace

Reputation: 134941

dump the count in a variable and return that

declare @count int
select @count = count(*) from ..... --same where clause as your query

--now you add that to your query..of course it will be the same for every row..
select top 10 ProductId, @count as TotalCount from .....

Upvotes: 4

Related Questions