Way to get full @@rowcount even when using "top"?

Question

Way to get full @@rowcount even when using "top"?

Viewing 5 posts - 16 through 19 (of 19 total)

You must be logged in to reply to this topic. Login to reply

Vic Kirkpatrick-173212 SSCommitted Points: 1831 More actions · Answer 1

Thanks Calvin, but like I said a few posts ago, I was really looking for a magic bullet setting or something here. I will not be using a temp table/var, or running the query twice.

nlcapit SSC Rookie Points: 41 More actions · Answer 2

I found a tip here a while ago that said there was a faster way to get the record count than using count(*)

SELECT rows FROM sysindexes WHERE id = OBJECT_ID('tblUsers') AND indid < 2

If you do a cross join with this query, you can get the record count and your top 10 -- I tried it with:

select top 10 a.*, [rows]

from authors a cross join sysindexes s

WHERE s.id = OBJECT_ID('authors') AND s.indid < 2

PhilPacha Hall of Fame Points: 3645 More actions · Answer 3

I get an 87% / 13% load... and 85% of the load of the first query goes for the count(*). The strange thing is that when I do top 500 it's more balanced (69%/31% and only 52% for count(*))... can't explain that one though except that it may be considered as a deterministic query and not reran after a few executions.

Remi,

The data from the first SELECT is cached.

The second SELECT reads the cached data, instead of reading from the original source.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 4

thanx, PhilPacha. Looks like I wasn't complettly off target .

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 5

This wouldn't work here because it's 6 tables parametered search report.