Select * return 24 while select count(1) return 3956892

Question

Select * return 24 while select count(1) return 3956892

halifaxdal

SSCoach

Points: 19741
More actions
January 5, 2017 at 9:33 am

#333175

This is really an interesting finding, please refer to my screenshots.
What am I missing here? I cannot understand this finding.

Viewing 7 posts - 1 through 7 (of 7 total)

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

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

What happens when you run them both at the same time?

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

halifaxdal SSCoach Points: 19741 More actions · Answer 2

I did run them at the same time, it's just the count(1) takes longer and the select * takes less than 1 second.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

Run

SET ROWCOUNT 0

and try again.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

halifaxdal SSCoach Points: 19741 More actions · Answer 4

You got it, but can you elaborate this? wouldn't that mean potentially every query must do the rowcount reset?

Thanks.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

halifaxdal (1/5/2017)
You got it, but can you elaborate this? wouldn't that mean potentially every query must do the rowcount reset?
Thanks.

https://msdn.microsoft.com/en-us/library/ms188774.aspx

and

https://msdn.microsoft.com/en-us/library/ms190356.aspx

It only has to be reset once it's been set. I recommend that you track down where it is being used and replace it with TOP().

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

halifaxdal SSCoach Points: 19741 More actions · Answer 6

ChrisM@Work (1/5/2017)
halifaxdal (1/5/2017)
You got it, but can you elaborate this? wouldn't that mean potentially every query must do the rowcount reset?
Thanks.
https://msdn.microsoft.com/en-us/library/ms188774.aspx
and
https://msdn.microsoft.com/en-us/library/ms190356.aspx
It only has to be reset once it's been set. I recommend that you track down where it is being used and replace it with TOP().

Thank you for your help. It's much appreciated.