Alternative to SELECT COUNT(*) for a View

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Alternative to SELECT COUNT(*) for a View

Author

Topic

JeffK627
Yak Posting Veteran

50 Posts

Posted - 2010-07-29 : 12:51:40

I need to determine whether the record count for a View is > 0.

SELECT COUNT(*)

takes forever and

SELECT rows FROM sysindexes WHERE id = OBJECT_ID('View Name')

doesn't work for Views. Does anyone know a fast way to find out if a view is empty or not? I don't even need the actual count, just whether there is at least 1 row.

X002548
Not Just a Number

15586 Posts

Posted - 2010-07-29 : 12:54:34

SELECT TOP 1 * FROM View

?????

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

JeffK627
Yak Posting Veteran

50 Posts

Posted - 2010-07-29 : 12:58:00

Tried that - it takes as long as COUNT(*).

X002548
Not Just a Number

15586 Posts

Posted - 2010-07-29 : 13:01:53

really?

post the view ddl

How long is long

What if you do

IF EXISTS (SELECT * FROM VIEW)
PRINT 'DATA EXISTS'

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

JeffK627
Yak Posting Veteran

50 Posts

Posted - 2010-07-29 : 15:00:13

Long as in I went to lunch and it was still running when I came back.

I found an alternative though - turns out I can just check for 0 records in one of the tables the view pulls from.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-07-29 : 15:39:55

that view needs to be optimized

Kristen
Test

22859 Posts

Posted - 2010-07-30 : 05:56:13

... or rebuild indexes / update statistics on the underlying tables (which may be the same thing as Russell said)

X002548
Not Just a Number

15586 Posts

Posted - 2010-07-30 : 08:51:41

you sure it's not blockingh?

execute

sp_who2 active

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

JeffK627
Yak Posting Veteran

50 Posts

Posted - 2010-07-30 : 09:56:58

Unfortunately the tables themselves are not mine to index or otherwise change. But I did find a workaround, so it's OK for now. I'll ask the owner of the tables if he would be willing to rebuild the indexes.

Kristen
Test

22859 Posts

Posted - 2010-07-30 : 17:07:12

"Unfortunately the tables themselves are not mine to index or otherwise change"

My suggestion was not to change them, but to check that the Housekeeping is running regularly - if not then all/most queries will be/become slow.

X002548
Not Just a Number

15586 Posts

Posted - 2010-08-02 : 14:16:26

quote:
Originally posted by JeffK627
But I did find a workaround, so it's OK for now.

And WHAT pray tell...was that????

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

konark
Yak Posting Veteran

60 Posts

Posted - 2010-08-02 : 20:35:46

Use count(1) instead of count( *)

Chandragupta Mourya

Takhyashila

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-08-03 : 05:44:14

quote:
Originally posted by konark

Use count(1) instead of count( *)

Chandragupta Mourya

Takhyashila

I dont think that will be faster

Madhivanan

Failing to plan is Planning to fail

X002548
Not Just a Number

15586 Posts

Posted - 2010-08-03 : 09:40:42

quote:
Originally posted by konark

Use count(1) instead of count( *)

Chandragupta Mourya

Takhyashila

That "Trick" was no longer need back in 7 when the SQL Server "developmwent" team realized they should optimize SELECT COUNT(*)

Most the trick was meant for sub queries that used SELECT *...SELECT 1 in 6.5 gave an advatage, because the "Development" team at the time return all the rows to the sub query, when they could NEVER be used

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

JeffK627
Yak Posting Veteran

50 Posts

Posted - 2010-08-03 : 10:29:08

I discovered that I didn't have to check whether the view itself was empty, I only need to check one of the tables the view uses. That was more than fast enough for my needs.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources